SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need help regarding SSRS , SSIS and SSAS concept


Need help regarding SSRS , SSIS and SSAS concept

Author
Message
sukhi.ece
sukhi.ece
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
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?
PB_BI
PB_BI
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4237 Visits: 2732
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
lapras_dewgong
lapras_dewgong
SSC Journeyman
SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)

Group: General Forum Members
Points: 82 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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search