Search results matching tags 'PowerPivot' and 'DAX'

Group Sales by Age of Customers #dax #powerpivot #tabular

sqlbi — Mon, 06 May 2013 12:16:00 GMT

I published an article describing how to implement the grouping of sales transactions by age of customer at the moment of the transaction by using PowerPivot or Analysis Services. The same pattern can be used also for any kind of banding operation, this specific case is useful also to recycle the formula that gets the exact age of the customer for each transaction.

An interesting point is related to performance optimization. The technique is based on adding a calculated column in a table that might contain millions of transactions. This is less expensive than adding a column that contains a foreign key and then a relationship between a table containing group definitions and the transactions table. Every relationship is expensive and generates additional structures (you can see more files in the Analysis Services database, too). Adding one or two columns that have a low number of distinct values (10-15 rows) usually has a lower memory cost than creating a relationship with another table. The article also contains PowerPivot examples for both Excel 2010 and Excel 2013.

If only I could decouple attribute visualization from physical structure, I would put these “degenerate dimensions” in a separate folder, because in this way such attribute will be included in attributes belonging to the fact table, which might not be so clear in presenting data. However, I understand that such a decoupling could make live very hard to DAX clients (but probably for MDX it could be not a big issue).

DAX Studio for Excel 2013 finally available! #dax #excel #powerpivot #ssas #tabular

sqlbi — Sat, 04 May 2013 05:49:00 GMT

I'm so happy that DAX Studio finally supports Excel 2013! As Darren Gosbell described in his blog, this release has a few internal changes that will better support future enhancements. I will port the code to capture the query plan for a query in this new release, but unfortunately it will require some weeks because I'm traveling a lot in these days.

If you write DAX formulas and queries for PowerPivot or Analysis Services Tabular, DAX Studio is a must have tool: do you really want to live without a DAX Editor? There are a lot of possible improvements and I hope other contributors will give their help to this Codeplex project.

LASTNONBLANK and FIRSTNONBLANK functions work with any column #dax #powerpivot #ssas #tabular

sqlbi — Fri, 19 Apr 2013 10:28:50 GMT

During a PowerPivot Workshop course we received an interesting question from a student: “Can I use LASTNONBLANK (and FIRSTNONBLANK) with a column which is not a date column?”

The reason is that we introduce LASTNONBLANK in the Advanced Time Intelligence module, because its typical use case is on a date column. However, you can use these functions on any column, which raises the question about what happens at that point. The sort order used is the one that depends on the data type of the column. If it is a Text column, the alphabetical sort order is the reference order. If it is a number, then the numeric order is the reference.

What happens if a column has the “Sort By Column” property set to another column? This sort order is *not considered* by LASTNONBLANK and FIRSTNONBLANK functions. Even if a PivotTable shows you data sorted according to Sort by Column property, any DAX formula ignores such a sort order. Thus, be careful writing your DAX queries if you have to do some assumptions on the sort order of a column using DAX functions that rely on sort order, such as LASTNONBLANK and FIRSTNONBLANK.

DAX Time Intelligence for 4-4-5 Calendar, ISO Calendar and other custom ones #dax #powerpivot

sqlbi — Tue, 19 Feb 2013 14:21:00 GMT

DAX offers a set of Time Intelligence functions that simplify writing DAX expressions such as YTD, YOY and other time-related calculations. However, these functions only works when some assumptions are valid: your periods should be “natural” months and quarter. Some industries, such as retail and manufacturing, are used to accounting periods that are based on weeks instead of months. One month and one quarter are a set of weeks and a week cannot be split in different months, quarter or years. Making DAX working on these custom calendars requires you to write some DAX expression without using the built-in Time Intelligence functions.

I wrote an article, Week-Based Time Intelligence in DAX, which describes how to write the common DAX calculations required on a custom calendar. I also included two samples, one for Excel 2010 and the other for Excel 2013, so that you can easily work on both versions (as you know, downgrading a workbook from Excel 2013 to Excel 2010 with PowerPivot is not possible).

An important tip you will find is that creating a column that contains the number of days elapsed in a year (or the running total of days in the year, if you prefer) makes it easy writing the FILTER required to use the right set of days in each calculation. After all, this technique is very similar to the one you would use in SQL to perform the same calculation, for this reason DAX is considered more intuitive than MDX by developers with a SQL background.

LASTDATE dates arguments and upcoming events #dax #tabular #powerpivot

sqlbi — Mon, 01 Oct 2012 17:04:00 GMT

Recently I had to write a DAX formula containing a LASTDATE within the logical condition of a FILTER: I found that its behavior was not the one I expected and I further investigated. At the end, I wrote my findings in this article on SQLBI, which can be applied to any Time Intelligence function with a <dates> argument.

The key point is that when you write

LASTDATE( table[column] )

in reality you obtain something like

LASTDATE( CALCULATETABLE( VALUES( table[column] ) ) )

which converts an existing row context into a filter context.

Thus, if you have something like

FILTER( table, table[column] = LASTDATE( table[column] )

the FILTER will return all the rows of table, whereas you probably want to use

FILTER( table, table[column] = LASTDATE( VALUES( table[column] ) ) )

so that the existing filter context before executing FILTER is used to get the result from VALUES( table[column] ), avoiding the automatic expansion that would include a CALCULATETABLE that would hide the existing filter context.

If after reading the article you want to get more insights, read the Jeffrey Wang's post here.

In these days I'm speaking at SQLRally Nordic 2012 in Copenhagen and I will be in Cologne (Germany) next week for a SSAS Tabular Workshop, whereas Alberto will teach the same workshop in Amsterdam one week later. Both workshops still have seats available and the Amsterdam's one is still in early bird discount until October 3rd!

Then, in November I expect to meet many blog readers at PASS Summit 2012 in Seattle and I hope to find the time to write other article on interesting things on Tabular and PowerPivot. Stay tuned!

Fact Tables with Different Granularities in #PowerPivot and #BISM #Tabular

sqlbi — Wed, 11 Jan 2012 12:35:00 GMT

A few weeks ago I received a question that inspired me in writing this article about how to handle data at different granularities in PowerPivot and BISM Tabular. I think this is a common pattern when you have a budget table that contains data at an aggregated level (like month and product category) and you want to compare it with sales that are stored at product and day level. This scenario can be handled in a BISM Multidimensional model by using relationship with other attributes in a dimension, but this is not possible in PowerPivot and BISM Tabular because of the lack of attribute relationships in the model.

In PowerPivot and BISM Tabular you have two issues: first, you have to create a single PivotTable with both Budget and Sales measures; second, you want to blank the budget value when a dimension or an attribute that is not included in the budget is drilled down. After that you may want to split the budget over these dimensions/attributes according with an allocation algorithm. In the article I covered the first two issues, whereas the third might be the topic for a future post. The following is the final result you will obtain – OrderQuantity and Budget are the two measures obtained from columns of two different tables, Sales and Budget, that have different granularities.

I’d like to get feedback from PowerPivot users that approached the same issue with alternative solutions.

Converting #MDX to #DAX and PowerPivot Workshop online #ppws

sqlbi — Mon, 07 Nov 2011 09:40:00 GMT

I just published the article Converting MDX to DAX – First Steps on the renewed SQLBI web site about converting MDX to DAX. The reason is that with BISM Tabular in Analysis Services 2012 you will be able to write queries in both DAX and MDX. If you already know MDX, you might wonder how to “translate” your MDX knowledge in DAX. I think that this is another way you can improve your knowledge about DAX: it has different concepts behind and this comparison should be helpful in this purpose. This is a first step in this comparison, I will continue with other articles in this series based on feedback I will get.

And don’t forget PowerPivot! This is still the best way to start practicing with DAX now and you can solve many reporting needs by just using it. We did a roadshow in Europe in the last month, and now we announce the first PowerPivot Workshop online edition dedicated to Far East and Australia! It is scheduled for November 24-25, 2012, with this schedule:

Beijing: 8:00am-11:00am / 1:00pm-4:00pm
Hong Kong: 8:00am-11:00am / 1:00pm-4:00pm
-->Jakarta: 7:00am-10:00am / 12:00am-3:00pm
-->Mumbai: 5:30am-8:30am / 10:30am-1:30pm
-->New Delhi: 5:30am-8:30am / 10:30am-1:30pm
Perth: 8:00am-11:00am / 1:00pm-4:00pm
Seoul: 9:00am-12:00am / 2:00pm-5:00pm
-->Shanghai: 8:00am-11:00am / 1:00pm-4:00pm
Singapore: 8:00am-11:00am / 1:00pm-4:00pm
-->Sydney: 11:00am-2:00pm / 4:00pm-7:00pm
Tokyo: 9:00am-12:00am / 2:00pm-5:00pm
GMT: 0:00am-3:00am / 5:00am-8:00am

We are defining partnerships with training centers in these countries, after this online edition I hope you will find other options nearest to you. And if you run a training center and are interested in partnership, hurry up and contact us before this online edition (attending the workshop is a requirement for trainers before delivering the same course).

We know that US and other countries are waiting for the workshop, but we’re working on this, too – more news very soon, I hope!

Median, Moving Average, Quartile and Percentile calculations in #PowerPivot

sqlbi — Tue, 13 Sep 2011 04:18:00 GMT

Javier Guillén wrote several blog post about how to implement certain common calculations in PowerPivot. It is interesting to read his posts not only because of the solutions he propose, but also because he uses several techniques that can be applied in other scenarios.

Here is a list of posts and reasons to read:

Calculating Moving Averages in PowerPivot/DAX: a classical scenario solved by using just a measure and in a very clean way (the average is correctly calculated also when the period available is shorter than the standard period defined for the moving average)
Calculating Median in PowerPivot/DAX: this is the same calculation I solved in another post with a calculated masure, but Javier shows how to implement in with a measure - this will be much useful with BISM Tabular, whenever you cannot change the data model on the client side like you do easily in PowerPivot by adding a calculated column.
Quartile, Percentile and Median in PowerPivot/DAX: use an MDX calculated member in a PivotTable of PowerPivot data, with the help of OLAP PivotTable extensions. Do you know that you can inject a DAX expression in a calculated member using this same technique? Probably not, it is not documented anywhere but it is absolutely supported - more about this in a future post!

#PowerPivot Workshop in Lisbon - Portugal #ppws

sqlbi — Fri, 29 Jul 2011 08:25:00 GMT

Alberto anticipated me announcing our next edition of the PowerPivot Workshop in Lisbon (Portugal) on September 19-20, 2011. The announcement has been published also on the SQLPort website in Portuguese! But remember, the workshop will be entirely in English!

In this edition of the workshop we are integrating in the course the new feature of the next version of PowerPivot "Denali" (now available in CTP3). We are also improving the content thanks to the feedback gathered in the past workshops. We had good feedback,in past editions, but we are never completely satisfied and we always want to improve... visit www.powerpivotworkshop.com for more information.

See you in Lisbon!

Parent-child special calculations in #BISM #Tabular #Denali

sqlbi — Fri, 22 Jul 2011 07:55:00 GMT

Alberto Ferrari published an interesting blog post regarding how to write particular calculations over a parent-child hierarchy in PowerPivot Denali (and in BISM Tabular, of course). One case is the Sum of Leaves (summing only the leaves of a parent-child hierarchy) and the other is the Sum of Children (computes all the nodes that are descendants of the current node, excluding the value of the current node).

It is always more interesting the fact that DAX can perform calculation over hierarchies, without having specific commands to navigate in a hierarchical structure. The results is not so easy to write, but it works and is probably pretty efficient.