Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2005 Forums  Analysis Server and Reporting Services (2005)  Can the report model handle dimensional modeling?

Author  Topic  J00st Starting Member 2 Posts Posted - 2009-09-30 : 05:11:21 We have a data warehouse which design is based on Kimball's dimensional modeling theory (starschema's). Now we would like to use Reporting Services to create a report model for end users who can create then their own reports. Business Objects has for instance its 'Universe' which allows the developer to handle multiple facttables by designing a context (collection of joins) for each starschema. So if there is for example a production fact table (pk = date, department, product) and there a budget fact table (pk = month, department, product) an end-user would like to show them in 1 report table on a month level. year, month, product, production, budget 2009, 200910, 'A' , 1000, 1100 In Reporting services I can design the report model with all of the fact tables included, however I cannot query more than 1 fact table in the report builder. In report builder I select for instance year (from the time dimension) then I can choose Production from the production table or Budget from the budget table, BUT not both.!!!!! Once I choose budget I cannot choose production anymore :-( The reason, I think, why this is the case, is that there is not a direct relationship between both fact tables (which is correct since there is at least a 1 to many relationship and we wouldn't like measures (facts) being multiplied because of joining a 'many' table). I hope it is just a matter of designing the report model by introducing such as a context like Business Objects have in their universe, which forces the generation of different sql statements in the background when querying different fact tables (in this case 2 queries, one on production and one on budgets). The tool has to be clever enough tot recognize these different levels of granularity and joins the 2 result sets internally on the conformed dimensions (in this case year, month and product) So is there I way in to handle this situation in Reporting services or is this a restriction in reporting services and is the only escape using cubes (which I do not like because extra ETL and metadata layer, so more maintenance etc) or (I hope) did I miss something so far. I hope someone can help with this. Thanks in advance. Joost Joost