Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Need help regarding SSRS , SSIS and SSAS concept Expand / Collapse
Posted Tuesday, July 2, 2013 6:48 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 18, 2013 5:59 AM
Points: 27, Visits: 45
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?
Post #1469458
Posted Wednesday, July 3, 2013 4:48 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 3:41 AM
Points: 1,273, Visits: 2,374
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.

I'm on LinkedIn
Post #1469986
Posted Thursday, July 4, 2013 11:55 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, February 2, 2016 9:42 AM
Points: 81, Visits: 42
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.
Post #1470493
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse