Home > SSAS > SSAS #25 – A MDX question from a reader

SSAS #25 – A MDX question from a reader

Hi,

This is what I gathered from your question.

Need a MDX query get the data in required format.

ON COLUMNS:
DimValidScenario (Hierarchy Level: Scenario) (Members: Base, Flatterner, Value and so on)

ON ROWS:
DimAccount (Hierarchy Level From Level 02 to Level 05 Data)
DimPlan Name( Hierarchy Level 03 Data)
DimStatus( Hierarchy Level 03 Data)

I am making assumption that by “required format” you mean that certain dimensions need to be on columns, and other dimensions need to be on rows, and you do not need all the levels in the hierarchies, and you only need certain levels in certain dimensions.

The following is only a pseudo code to demonstrate what you can do.

  • CROSSJOIN and *: they are basically the same, and similar to CROSS JOIN in SQL
  • You nee to use level 02 to level 05 in a hierarchy in DimAccount. Since we can not use the same hierarchy more than twice in a CROSSJOIN function, you would need to use the attribute instead. I am assuming that you have already created the attribute relationships. 
  • Using the WHERE clause to limit your return dataset for testing purpose.

image

Thanks for your question.

Sherry

Categories: SSAS Tags: ,
  1. BJ
    May 3, 2012 at 8:33 pm

    we have a situation that REQUIRES us to ignore those errors. The source database in not truly normalized and has errors even in the dimension tables – duplicate keys. We do not have access to (a) clean that (b) create a staging database and make our own clean copies of the data. So we have to process the cube directly from the dirty source. Now, one of our developers wants to INNERJOIN fact table with each dimension in order to ensure only data matching valid dimension keys get processed without errors. I want to do a LEFT join, so we can get as much good data as possible, but when there is no match, I want to convert the keys to NULL so that the SSAS built-in ERROR mechanism will put the m in the UNKNOWN pot. That way, at least all data is being reported. What do you think?

    Like

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: