Parent-child Including Child Members Multiple Places in a Parent-Child Hierarchy
| Including Child Members Multiple Places in a Parent-Child Hierarchy |
| Written by Carl Rabeler |
| Tuesday, 18 March 2008 01:14 |
|
When designing your SQL Server 2005 (or 2008) Analysis Services solution, you may be faced with the design requirement to display multiple hierarchies in a parent-child dimension. A common implementation that we have seen is the use of custom rollups to accomplish this task. However, we have seen performance issues with this implementation when implemented at scale. For example, in one site that we worked with recently, certain MDX queries took almost a full minute to return results when executed against cold cache. Marco Russo describes a different implementation in his Many-to-many revolution article—this implementation uses the many-to-many dimension relationship feature in SQL Server 2005 Analysis Services. Richard Tkachuk describes a third implementation in his Duplicate Members in Analysis Services 2005 blog. We implemented a variation of the many-to-many dimension design at the customer site to determine if its performance was significantly better. We discovered that its performance is dramatically better, particularly when the data requested is neither cached in Analysis Services nor cached in the file system. Note: There are numerous approaches to addressing this problem space. This paper addresses only one particular approach. Other approaches could be superior based on various criteria. Read more... |
Latest Author Articles
- Running Microsoft SQL Server 2008 Analysis Services on Windows Server 2008 vs. Windows Server 2003 and Memory Preallocation: Lessons Learned
- Including Child Members Multiple Places in a Parent-Child Hierarchy
- Scale-Out Querying with Analysis Services Using SAN Snapshots
- Analysis Services Query Performance Top 10 Best Practices
- Resolving Common Connectivity Issues in SQL Server 2005 Analysis Services Connectivity Scenarios (by
Top Rated
- SSAS Implementation Best Practices slides in PDF format
- SSRS Report Against a SSAS Parent Hierarchy
- Handling inter-dimensional members dependency and reducing cube sparsity using reference dimensions in Analysis Services 2005
- Cube structure optimization for MDX query performance in Analysis Services 2005 SP2: Tips for Parent Child Hierarchies usage
- Handling Multiple Calendars with a M2M Scenario
- Passing MDX parameters in Reporting Services reports
- Using UserName to Control Data Access and Default Member in SSAS 2K5 (Carrie Williams)
- SSAS Tutorial: SQL Server 2005 Analysis Services Tutorial by Accelebrate
