SQL Server Performance Forum – Threads Archive

Reporting in DW

Hello Friends, We have created a Datawarehouse System, on SQL Server 2000. Our DW Design is very much based on Snowflake Design (Bill Inmon). Apart from Cube Analysis (We use Cognos), we have a Lot of Reports (Use IMR) which need to be churned out. The problem is they are very ad-hoc in nature and cannot be directly taken out from my Dimension Tables as a lot of business rules need to be applied. So what is the best adopted approach, should we create Reporting Tables, Stored procedures, or Views. Reporting Tables – If the number of these Reports Increase, will not this be a problem? SP – I think SP should only be used for any User Parametrized Reports Views – I am not sure how this will help. Kindly help as i am a little confused as to what and how to go about it. Thanks
Deepan

Have you looked at Reporting Services or not? Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided Ã¯Â¿Â½AS ISÃ¯Â¿Â½ with no rights for the sake of knowledge sharing.

No – We are just using SQL Server 2000 to store Data, and are using Cognos for our reporting requirements. So wanted to know, for complex ad-hoc reporting what should be the best option if we are not making use of Cognos Catalogs.
Thanks
Deepa

Hello One of the limitations with Bill Inmons design approach is that the data warehouse is highly normilized (usually 3nf), this makes it very hard for non technical users to browse the data and run ad-hoc reports, in fact this area is considered "Off Limits" to users. Based on your posting I recommend you gather the business requirements and denormilize the warehouse into star schemas, I believe that this is what you meant by "reporting tables" this new layer of the data warehouse is where you want your users querying from. Depending on the size of your warehouse you can also build aggregations to enhance performance even further. Now to answer your question: "Reporting Tables – If the number of these Reports Increase, will not this be a problem?" Provided you have adequate HW and a good design this should not be a problem. As far as Views in SPs you can find plenty of information on this site regarding performance tunning. Look up information on Indexing which is how you will gain best performance for queries. Graphical Reporting tools like Cognos or Analysis Services etc. are nice, but they are not the root or fix for performance problems, start with your design [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />]. Raulie

]]>