Best way to relate to arbitrary levels in hierarchy?

Hot Topic

Null ParameterValue
Convert Milliseconds to HH:MM:SS
Programmatically creating and executing data driven subscriptions in SSRS
Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: There is no data for the field at position 18., ;
HELP WITH DATABASE SAVE FOR MINING PROGRAM
No Import/Export Option in Mgmt Studio Express
Measure group related question
Upgrade Advisor error I don't understand
SQL Native Client Configuration or Remote Connections problem
Single sign-on with forms authentication

SQL Server

Slow changing Dimension
Books Online
Exists and Filter don't play together?
Logging in SSIS
How to locate the database folder in sql 2005
The 4000 character limit for expressions
Reporting Services - Variables/Parameters - Is this a bug?
Schema Problem?
best approach for "trigger on commit"?
Concatenate Rows into Columns

Best way to relate to arbitrary levels in hierarchy?

Hi,

I have a geographic dimension (Zip Code <- City <- County <- State), and my underlying OLTP data typically relates to the Zip Code Dimension Key. My problem is that for ~40% of the records in my OLTP system, we only have granularity to the County or State level. Is there any way I can still use my geographic dimension to include those records, albeit at a less granular level

Thanks!


Answer this question

Best way to relate to arbitrary levels in hierarchy?

  • Andy Ho

    Hi Arjun. One potential solution is the use of a parent-child hierarchy. Parent-child hierarchies allow non-leaf members to show values with the MembersWithData property. Set the MembersWithData property to NonLeafDataVisible, and link your fact table foreign key of the geographic dimension to the appopriate primary key in the dimension table for the County or State.

    Hope this helps - Paul Goldy



  • ScorpEagle

    Thanks, I think this makes sense. I'll give it a shot.
  • Best way to relate to arbitrary levels in hierarchy?
Answer this question