OLTP to OLAP cube

Question

OLTP to OLAP cube

d-limiter

Valued Member

Points: 69
More actions
April 21, 2009 at 3:00 pm

#384410

Hi I'm new to SSAS 2005. I'm working on a project to take an existing OLTP db and build an OLAP cube. I will also be using ProClarity to create a dashboard from the cube.
I have gone through the AdventureWorks and ProClarity tutorials, but am having some difficulty building my cube.
1. Do I need to use SSIS to build a data mart? New data is being entered into the OLTP db on a daily basis and I need to get this data to populate the cube. Or will the data be automatically populated if I designate the OLTP db as a data source in SSAS?
2. Can a fact table be both a fact and a dimension? Is it true that you should not have more than 12 dimensions in a cube?
I know I need to order some books on this topic. Any recommendations?
Thanks in advance
-d

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Ben Sullins-437405 SSCertifiable Points: 5397 More actions · Answer 1

I would recommend never building a cube, or almost* any reporting solution on top of your live OLTP database. You will need, at the very least, to create an Operational Data Store (ODS) where this data is transferred to for reporting.

From there it is technically possible to build the cube w/o doing a data mart using a data source view, however, this is definitely not recommended. It is very tricky to get this to be stable, and when the cubes become part of the business process and are mission critical, you don't want things to be unstable.