Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How do I use Analysis Services?


How do I use Analysis Services?

Author
Message
Tulla_123
Tulla_123
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 20
Hi,
I have an Operational Datastore (ODS) instance consisting of several relational databases that are populated daily from exports from the OLTP databases. I use SSIS 2012 for this.
The databases in the ODS are replicates of the databases in the OLTP.
I also hold web analytics etc in the ODS and I will be combining data from all these sources for MI reporting and also for data analysis and visualization through a BI dashboard.

There won't be much data to start off with, but I want to build the system for future use when there will be more data.

Do I build a datawarehouse from the ODS databases, or do I build datamarts, or do I create logical views on top of the ODS databases that can be used by MI reporting and BI tools?
I think performance at this point in time will be met by simple logical views. Is this wise or do I invest my time in designing a proper datawarehouse from the ODS now?

Also, should I import data from the ODS into the datawarehouse using Analysis Services? Is this the correct use of it?

Thanks for any help offered.
Jim Mackenzie
Jim Mackenzie
Old Hand
Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)

Group: General Forum Members
Points: 350 Visits: 719
Hi Lise_Synnove,

Sounds like you've got a lot of reading to do. I'd start with the Kimball methodology to get more of an idea of how to attack. Have a look at this:

http://www.sqlservercentral.com/blogs/microsoft-business-intelligence-and-data-warehousing/2012/04/08/the-kimball-approach/

Depending on how big/complex/distributed your database is will depend on whether you choose a warehouse or a number of marts, especially if your data is not really related.

If I were you I'd build a Staging database layer on top of your ODS and do most of your ETL there. Then I'd feed from staging in to your data warehouse in a star schema.

Then I'd build an Analysis Services cube/number of cubes from your warehouse. Many people are advocates of using views to enable quick changes to the cube without changing your ETL layer. Basically if you do the lion's share of the work now you will end up with a better solution later.

Cheers,

Jim.

SQL SERVER Central Forum Etiquette
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