THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

SQLBI is a blog dedicated to building Business Intelligence solutions with SQL Server.
You can follow me on Twitter: @marcorus

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

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.

Published Wednesday, January 11, 2012 2:35 PM by Marco Russo (SQLBI)
Filed under: , , ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Jay Luther said:

Hi Marco,

I am having an issue with my cube when one dimension is dropped on another. This is when analysis services performs a cross join.  

Accordin to profiler this is where the performance hit is happening.

eg.

SET [DEV- Clear View].[{F99BB6D1-88F1-4563-8CBA-E8D0289B5DF6}Pivot60Axis1Set15] AS

'

{ [{F99BB6D1-88F1-4563-8CBA-E8D0289B5DF6}Pivot60Axis1Set14],

FILTER(

CROSSJOIN(

{ [JEE].[JETYPE CODE].&[JE]},

FILTER(

CROSSJOIN(

AddCalculatedMembers([JEE].[JE NUMBER].[JE NUMBER].MEMBERS),

{ [Account].[Name].[All] }

),

NOT ISEMPTY(([Measures].[Debit]))

OR

NOT ISEMPTY(([Measures].[Credit]))

)

),

NOT ISEMPTY(([Measures].[Debit]))

OR

NOT ISEMPTY(([Measures].[Credit]))

)

}

'

I understand that usin NONEMPTYCROSSJOIN() function this would help processing speed. However do you know how to implement this?

I have no calculated memeber but when I slice the cube by JE_number then GL code it uses a crossjoin() but I want it to use NONEMPTYCROSSJOIN().

How can this be acheived?

Thanks

February 12, 2012 6:49 PM
 

Marco Russo (SQLBI) said:

Jay,

I don't understand what is the issue (performance?) and what you're trying to achieve (better performance with Excel PivotTable? Alternative query in DAX?)

If you would be able to create a repro in Excel with PowerPivot explaining what is the issue I might be able to take a look at it, otherwise it might be hard to understand the problem by talking through comments here unless you don't have a clear and easily reproducible issue.

Thanks,

Marco

February 12, 2012 11:49 PM
 

Jay Luther said:

Yes performance is poor. The business would analyze the data through excel as per their requirements. Excel version is not 2010 so no powerpivot.

Having looked at profiler there are seven cross joins occuring when I slice the cube with one dimension after another.

MDX below, not sure how to optimize this as the query is default part of AS.

This is the built in MDX that is run, I have switched query optomizer too. Any pointer?

Thanks

SELECT {[Measures].[Debit],[Measures].[Credit]} DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS , NON EMPTY CrossJoin(CrossJoin(CrossJoin(CrossJoin(CrossJoin(CrossJoin(CrossJoin(Hierarchize({DrilldownLevel({[Office].[Name].[All]})}), Hierarchize({DrilldownLevel({[Dept].[Name].[All]})})), Hierarchize({DrilldownLevel({[Prof Center].[Name].[All]})})), Hierarchize({DrilldownLevel({[JEE].[JE NUMBER].[All]})})), Hierarchize({DrilldownLevel({[JEE].[JE DATE].[All]})})), Hierarchize({DrilldownLevel({[JEE].[Header Narrative].[All]})})), Hierarchize({DrilldownLevel({[Account].[Code].[All]})})), Hierarchize({DrilldownLevel({[Account].[Name].[All]})})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,[JEE].[JE NUMBER].[JE NUMBER].[Book],[JEE].[JE NUMBER].[JE NUMBER].[CURRENCY CODE],[JEE].[JE NUMBER].[JE NUMBER].[Header Narrative],[JEE].[JE NUMBER].[JE NUMBER].[INACTIVE],[JEE].[JE NUMBER].[JE NUMBER].[JE DATE],[JEE].[JE NUMBER].[JE NUMBER].[LAST MODIFIED],[JEE].[JE NUMBER].[JE NUMBER].[Narrative],[JEE].[JE NUMBER].[JE NUMBER].[REVERSE],[JEE].[JE NUMBER].[JE NUMBER].[SOURCE],[JEE].[JE NUMBER].[JE NUMBER].[STATUS] ON ROWS FROM (SELECT ({[Office].[Name].&[Birmingham - Colmore Row]}) ON COLUMNS FROM [DEV- Clear View]) WHERE ([Fiscal Period].[Year---Period].[Key].&[201201]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS

February 18, 2012 4:23 PM
 

Florian said:

Hello Marco,

great article!

I am very interested int he following functionality: "After that you may want to split the budget over these dimensions/attributes according with an allocation algorithm"

Have you any tips or solution approaches for me how I can handle this?

Thanks in advance

Florian

November 12, 2012 5:07 AM

Leave a Comment

(required) 
(required) 
Submit

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog

Syndication

Archives

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement