July 2, 2013 at 6:48 am
I am working on SSRS reports.We have a OLTP database in which we have created stored procedure for each report.These stored procedures are used to create DataSet in BI solution to run the report.
Now we were asked to go through SSIS process ( ETL ) and Data Warehouse concept and all reports will now be running through these two approach.
So my planning is given below:
I'll create a new database in which I'll create new tables for each reports and insert returned result set from stored procedure into new created tables.Finally I'll query these new tables to run the reports as these tables contains per-calculated values so this approach speed up the data retrieval process.
So here what does Data Warehouse term stands for.Does the new database created is called Data Warehouse.
Actually I have downloaded AdventureWorksDW database and tables in this database starts with Dim... and Fact... OK I know this is star schema data modelling approach.
Do I have to create new tables with these prefixes and finally run the report from these tables?
Do I need to design data warehouse first before creating SSIS packages?
July 3, 2013 at 4:48 am
Firstly your initial approach if I'm interpreting you correctly is a very bad idea. What happens when a user needs to see more/different data on a report? Add new column, change the data extract for the "report" table? Going forward that's not really a viable development solution.
If you want to head down the data warehouse route then it would be useful to read widely around the subject. there are two main data warehouse methodologies out there - Kimball and Inmon. There are many books and online resources out there relating to both.
In terms of design and SSIS, yes you would need to design (and build) your data warehouse before embarking on developing an ETL. Without doing that you would have nothing to map to/load into.
Good luck.
July 4, 2013 at 11:55 am
Data Warehouse is a methodology to store your data. You design the data warehouse and then you implement it in SQL Server Database
As mentioned by yayomayn, you need to inform well and study one of the methodologies. I would suggest Kimball. The book "The MicrosoftData Warehouse Toolkit: With SQL Server2005 and the Microsoft Business Intelligence Toolset " will give you pretty much everything you need to start on the right foot.
And just to help you get started on it, a Data Warehouse definetively is not a collection of tables where each table has the results for a particular report. A good design will lead you to build few tables from which most of the reports should be constructed.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply