June 22, 2009 at 1:20 pm
just to clarify - i made the show/visiblity setting on every level for which i knew there could be 'dupes' - so in our case, it was L1 to L6.
Steve.
June 22, 2009 at 4:34 pm
May be the idea of levels is what is confusing me here. Let's assume we are talking about Coutry-State-city ( By the way, I am using SSAS 2008)
I have the data stored like this:
Country-----State---------City
USA CA Los Angeles
USA CA San Francisco
France NULL Paris
UK NULL London
After creating a hierarchy Country-> State-> City browsing the dimension I get this:
USA
---->CA
--------> Los Angeles
--------> San Francisco
France
--------> (NULL or Blank))
----------->Paris
UK
-------->(Null or Blank)
----------->London
Is it possible to skip the null level as:
USA
---->CA
--------> Los Angeles
--------> San Francisco
France
--------->Paris
UK
--------->London
I tried the IsMemberIf with no luck. Is there any example where the feature works?
Please advise!
June 22, 2009 at 9:03 pm
Hi Idris,
Using your example you can achieve what you want using either of David's or my approach.
The primary difference between the approaches David and I outlined are:
Davids:
- You need to 'push' the Account# 'up' the hierarchy levels.
- You need to have a level (say 'sub account') that joins to the fact, but one that you won't display in the dim.
So, as per his example->
Level 1----Level 2----Level 3----Level 4----Level 5----Level 6 (not displayed in dim, but joins to fact)
Value1----Value2----Value6----null----null----Value6
So, the value for 'level 6' is present at both level 3 and level 6, with nulls between them in Level 4 & 5.
You set the visibility as per your original post, so hide if null on levels 2, 3, 4, 5 (not 6, because it's not in the dim).
Using this approach, you drill from Val1 to Val2 to Val6. The lower nulls are not displayed and the 'last' val6 is not an attribute in the dim that is displayed.
My approach is really similar but uses a slightly different property. So, using the same type of example->
Level 1----Level 2----Level 3----Level 4----Level 5----Level 6 (*IS* displayed in dim, and still joins to fact)
Value1----Value2----Value6----Value6----Value6----Value6
Then, set the display property, using 'hide if name is same as parent and only child'. This will make the 'Value6' values at level 4, 5 & 6 not display. So the drill path is as per above, Val1 to Val 2 to Val6.
The only primary difference I can see between the two approaches is that Davids may be difficult to implement if you *need* the lowest level (because you'd have to now set the Level 6 attribute to visible, which could cause a drill path of Val1 -> Val2 -> Val 6 -> Val 6. But he may have a suggestion for dealing with this.
Hope this helps.
Steve.
June 23, 2009 at 9:06 am
I think the basic concept that you are missing is that the way you are populating your relational table needs to change. If you do not place nulls between the level fields then you will not have the problem.
June 23, 2009 at 9:30 am
Not sure what he would use David. In many cases, NULL is appropriate, the usual example is how US/AU/MX use States yet other countries don't. WHat would one populate a 'State' field with if there is no state?
For even more generic issues, i regularly deal with ragged hierarchies where the deepest level is 10 and the shallowest is 2, and there's ~14000 rows of the hierarchy that fall somewhere in between. My prefered option for these are a P-C hierarchy but when that's not possible, i use the approach I outlined above.
Steve.
June 23, 2009 at 9:50 am
Steve,
I agree that a Parent/Child dimension would work the best in this case. I too work with lots of ragged hierarchies, many of which are still in MSAS 2000. P/C dimensions are just horrible in 2000 as you cannot create aggregations on them.
So to remedy that situation I developed scripts that convert P/C tables into flattened hierarchy tables with generic field names.
For example:
DimId <--surrogate key assigned in the orig P/C table
DimCd <-- Business Key or Code for member (FL)
DimDesc <-- Display Value for Member (Florida)
Gen1Cd <-- Business Key or Code for member TOP LEVEL (All)
Gen1Desc <--Display Value for Member TOP LEVEL (All Countries)
Gen2Cd <-- Business Key or Code for member SECOND LEVEL (USA)
Gen2Desc <--Display Value for Member SECOND LEVEL (United States of America)
Gen3Cd <-- Business Key or Code for member THIRD LEVEL (FL)
Gen3Desc <--Display Value for Member THIRD LEVEL (Florida)
...
Just a very basic example, and using your method of pushing the Florida (state) member all the way through all of the levels would work as well, but I just leave them null after the last member is inserted. I always put the leaf level member and ID in the DimID/DimCd/DimDesc fields so that they can easily be queried/joined. But in this case the dimension is built off of the GenX fields from Gen1 to Gen10 or more in many cases. Of course with the setting hide member if null enabled. This way nulls after the leaf level remain hidden.
August 19, 2009 at 9:22 am
Hi all,
First sorry if this has been asked and answered before but I dont seem to understand the solutions above. I have a time dimension which is made up 3 hierarchies in MSAS 2005 (Calendar, Fiscal, and Last 12 Months). Each hierarchy has Year, Qtr, Month, Week in Year as levels. All levels are inherently based upon a dim_time table which has an invoice date pk. I use this date to generate values for year, Qtr, Month, and week in year for ALL BUT the Last 12 Year level. For that I have a Last12Year column on dim_time which only has values for the Last12 Months of InvoiceDate_PK's. The rest of the rows are null. So I have a Last12Year with distinct values of 2008, 2009, null. I dont want the null values to show in level1 of my Last 12 hierarchy so in effect my Last12 hierarchy is only the last 12 months. Any help greatly appreciated.
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply