I am currently working on a project that implements SQL Server 2005 (64-bit), Analysis & Reporting Services plus Excel 2007. The combination of Analysis and Excel offers very good options for analysis, drill down, slice & dice etc.
For the reports, we chose to develop a predefined layout with Visual Studio. The question that now comes up is how to set up the dataset: on a data mart (the cube, where you have defined some business logic) or straight on your data warehouse.
The drawback of Reporting Services on a cube is that RS interprets the cube dataset as a flat dataset, so you lose a lot of flexibility. But if you work with T-SQL, you end up rebuilding business logic that is already in the data mart.
I am curious how happy you are with building reports on a cube.
Frequently asked questions
What is the drawback of Reporting Services on a cube?
Reporting Services interprets the cube dataset as a flat dataset. Because of that you lose a lot of flexibility.
Should I build Reporting Services reports on a cube or on the data warehouse?
Both have a drawback. On the cube you lose flexibility. With T-SQL on the data warehouse you rebuild business logic that is already in the data mart.
What can I do with Analysis Services and Excel 2007?
The combination offers very good options for analysis, drill down and slice & dice.
How do I make reports with a fixed layout on Analysis Services?
Develop the predefined layout with Visual Studio. The open question is whether the dataset goes on the cube or on the data warehouse: the cube costs flexibility, T-SQL means rebuilding business logic.
Which environment does this project use for Analysis and Reporting Services?
SQL Server 2005 (64-bit) with Analysis Services, Reporting Services and Excel 2007.
Comments
Related blogs
Analysis Services Enterprise vs Standard
BIDS Helper
Custom Logging in SSIS