Identifying intra-dimensional members relationship and reducing cube sparsity in Analysis Services 2005 SP2 : Cube design tip

In my previous blog, I discussed the approach for "Handling inter-dimensional members dependency and reducing cube sparsity using reference dimensions in Analysis Services 2005 SP2 : Cube design tip". [Watch for the difference, Inter and Intra] The approach discuss the situation where members from one dimension are valid for a few members on other dimension. The typical example would be that for Sales Accounts, normally,only the sales department would be applicable and Sales data wont exists for say, "vehicle expenses department".

But what if there exists a relation between the members on a given "single" dimension? During the cube modeling phase, many a times designers ignore this aspect and later, many of the needed business reports can not be generated by the MDX reporting clients and complex MDX queries need to be hand coded.

Let us explain this with a simple business example of a consulting company. The typical chart of account of our fictitious consulting company is:

image

The data for two years are:

image 

The resultant fact table structure is:

image

The desired report that needs to be generated is:

image

However, what seems like a simple report can not be generated by the client applications since they don't understand the relationship that exists between the dimension members. The resultant MDX query would look something like below:

with member Measures.[USD/HR] as '
IIF(Account.currentmember is [Sales - Product 1],[Sales - Product 1]/[Revenue Hours - Product 1],
    IIF(Account.currentmember is [Sales - Product 2],[Sales - Product 2]/[Revenue Hours - Product 2],
        IIF(Account.currentmember is [Sales - Product 3],[Sales - Product 3]/[Revenue Hours - Product 3],
            IIF(Account.currentmember is [Sales - Product 4],[Sales - Product 1]/[Revenue Hours - Product 4],0))))'
select {
[Sales - Product 1],
[Sales - Product 2],
[Sales - Product 3],
[Sales - Product 4],
} on rows,
Crossjoin({[2007],[2008]},{Measures.[Amount],Measures.[USD/HR]} on columns
from cube

Not a very elegant MDX, huh.

However, we know that the following relation exist among the dimension members:

image

The key is to relate these dimension members in the cube so that in the MDX queries, we can take advantage of these relations and make the queries faster as well as the simpler.

In the below paragraph, we would use the following terminology:

Attribute & parent dimension members: The dimension members which provide the details to the parent dimension members to which it is related. E.g., in above example, the "Revenue Hours - Product X" dimension members, Attribute dimension members, provide the number of hours for "Sales - Product X" dimension members, parent dimension members.

There are two possible approaches to define the relation between these dimension members:

Approach 1: Define the attribute dimension member as the "measure" of the parent dimension members

Approach 2 :  Define the attribute dimension member as the "Attribute" of the parent dimension members

Approach 1: Define the attribute dimension member as the "measure" of the parent dimension members

If we know these relations beforehand, then we can modify the dimensional members and load the value of related dimension member as additional "Measures" in the fact table at the time of data load.

We can load the same data as below:

image

The above structure lends itself more for analysis purpose and the end users can now make the desired report using MDX client tools. The representative MDX query would look something like below:

with member Measures.[USD/HR] as 'Measures.[Amount]/Measures.[Hours]'
select {
[Sales - Product 1],
[Sales - Product 2],
[Sales - Product 3],
[Sales - Product 4],
} on rows,
Crossjoin({[2007],[2008]},{Measures.[Amount],Measures.[USD/HR]} on columns
from cube

 

Approach 2 :  Define the attribute dimension member as the "Attribute" of the parent dimension members

This approach can be taken if the earlier approach of "converting attribute dimension members as measures" is not feasible. Approach #2 is much less efficient approach than the Approach #1, but it is still elegant than the approach of lots of nested IIF statements.

In this approach, on account dimension, we create a property called "Hours" and for parent dimension members, we can store the reference of attribute dimension members.

E.g., suppose, in this case, we store the Member Keys as the Hours attribute for "Sales - Product X" dimension members. Now, the MDX query to generate the same report would be something like below:

with member Measures.[USD/HR] as 'Measures.[Amount]/(StrToMember("[Account].&[" + [Account].Currentmember.properties('Hours') + "]"))'
select {
[Sales - Product 1],
[Sales - Product 2],
[Sales - Product 3],
[Sales - Product 4],
} on rows,
Crossjoin({[2007],[2008]},{Measures.[Amount],Measures.[USD/HR]} on columns
from cube

6 comments:

Anonymous said...

Link to this post was added to our website in the [Analysis Services Articles]/[Design] section: http://www.ssas-info.com/analysis-services-articles/62-design/933-identifying-intra-dimensional-members-relationship-and-reducing-cube-sparsity-in-analysis-services-2005-sp2--cube-design-tip

Anonymous said...

[url=http://community.bsu.edu/members/buy+online+Viagra.aspx]online Viagra[/url]
[url=http://eterporno.ru/index.php]wab ru аська знакомства[/url]
[url=http://eterporno.ru/ankety-prostitutok-moskva-gruppovoy-seks-vyezd-starshe-40-let.php]анкеты проституток москва групповой секс выезд старше 40 лет[/url]
[url=http://eterporno.ru/putany-almaty.php]путаны алматы[/url]

[url=http://pc.eterporno.ru/zadat-feferu-seks.php]задать феферу секс[/url]
[url=http://pc.eterporno.ru/sereznye-znakomstva-perm.php]серьезные знакомства пермь[/url]

[url=http://pv.eterporno.ru/intim-novoslobodskaya.php]интим новослободская[/url]
[url=http://pv.eterporno.ru/blyadi-magnitogorsk.php]бляди магнитогорск[/url]

[url=http://px.eterporno.ru/dvorovaya-shluha.php]дворовая шлюха[/url]
[url=http://px.eterporno.ru/g-marks-znakomstva.php]г маркс знакомства[/url]

[url=http://pz.eterporno.ru/dosug-nu.php]досуг ну[/url]
[url=http://pz.eterporno.ru/seks-bolshogo-goroda.php]секс большого города[/url]

Anonymous said...

[color=#336699]Музыкальная группа [url=http://dejavu-group.ru/svadba.php]Dejavu-group[/url] - это коллектив дипломированных вокалистов и музыкантов.
[url=http://dejavu-group.ru/about_us.php]Deja Vu[/url]- один из лидеров в области музыкального сопровождения дней рождения.
В репертуаре группы Dejavu-group около 3 тыс. произведений.
Только живое исполнение. Поп, хиты 70-80-90-х, диско, джаз, ретро, современная музыка, европейские хиты, фоновая музыка, шансон .
ВИА Deja Vu располагает мощной качественной аппаратурой, позволяющей заполнить плотным и приятным уху звуком как маленькое помещение (фуршет), так и огромное помещение (корпоратив до 1000 человек).

Андрей +7 910 483 8294 [/color]

Anonymous said...

Well I assent to but I contemplate the post should secure more info then it has.

Anonymous said...

free sapphic lesbians claire sweeney lesbian indian teen lesbian video dump candice michelle lesbian action bikini blonde lesbian hothouse blogging for lesbians anastasia sabrina lesbians

Anonymous said...

Интересно написано....но многое остается непонятнымb

My Articles

Design

Cube structure optimization for MDX query performance in Analysis Services 2005 SP2: Tips for Parent Child Hierarchies usage

Fact table design for “State Workflow Analysis”: Analysis Services Dimensional modeling

Handling inter-dimensional members dependency and reducing cube sparsity using reference dimensions in Analysis Services 2005 SP2 : Cube design tip

Identifying intra-dimensional members relationship and reducing cube sparsity in Analysis Services 2005 SP2 : Cube design tip

Leaves() : An example to understand it for both regular hierarchies as well as parent child hierarchies

Aggregation design: useful tips

Level based attribute hierarchy: MDX query performance woes in SQL Server 2005 SP2: Is it fixed in post SP2 hotfix?

Parent child hierarchy to level base hierarchy conversion: hiding placeholder dimension members in client application

Trouble / Troubleshooting

Aggregate(), Sum() functions using calculated members does not work in Analysis Services 2005 SP2 (9.00.3042.00 version) but works in Analysis Services 2000 SP4

Analysis Services 2005 migration tool: Custom member formula issues in migrated database

Cube Partitions: Fact table not listing in Business Intelligence Development Studio in partition wizard

Analysis Services 2005: Many-to-Many relationship does not support unary operators with parent-child dimension

MDX

NextAnalytics and MDX : Part 1 - Swap Cells with Row Labels

Selecting dimension's default member based on a member property

Sorting members on member codes / member properties

Time Dimension: How to set Default Member to Current Month

Setting dynamic default member in dimension X based on the current member of dimensions Y

ADOMD.NET

Code : utility code for converting cellset to a data table

Others

Google specialized search for Analysis Services and MDX web resources integrated in my blog

Art of reading MDX articles

MDX Expression Builder : Need for a tool making it easier for functional users to write MDX expressions, queries.

Blogroll