How to use Multiple Fact Tables from different databases?

Hi,
I am new to datawarehousing and was trying my first real cube.
I have two databases on the same server and hence I have created two DS on SSAS 2005 one of the DS has fact table and many dimension tables whereas the other has only fact table. Is it possible to create a cube in such scenario or do I need to have dimension table even from the second DS.
netra26Asked:
Who is Participating?
 
srnarConnect With a Mentor Commented:
Netra26 this is another issue. For best practices follow this links:
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/olapdbpssas2005.mspx

As a general rule of thumb on Olap consider:
* a small cube is better to manage than a big one
* do not use too many attributes - an attribute is in fact a dimension, I met a project with more than 400 attributes in a cube - performance of metadata was a problem
* do not use too many measure group
* do not use too many calculated measures - you have to deal with non_empty_behavior
* do not use cell security - perfomance killer
* study Adventure Works
0
 
srnarCommented:
Of course - you can have more data sources in SSAS 2005. Design of dimensions/facts is put logically into so called Data Source View - from attached picture you can see combo box with data sources in Add/remove table dialog.

I don't like your approach with multiple databases because there cannot be explicitly required referrential integration on SQL server (foreign keys among fact database to dimension database).
multipleDs.jpg
0
 
netra26Author Commented:
yeah I agree that using multiple databases has disadvantages but I dont want to create a seperate DW DB. This is just the begining of my small project so that we can make modification and have a seperate DB for DW. I have created two data sources
say
DS1 for first database which has one fact table and 4 dimension tables
DS2 for another database which has one fact table which is named query on which i have defined the PK-FK relation
but when i process teh cube the data that it shows is same for all the attribute i choose. I hope you understand what I am trying to do. Please give me any suggestion on how to do this.
Thanks
0
 
srnarCommented:
Have you defined relation between dimension and a measure group on the 'Dimension usage tab'?

What is exactly 'data that it shows is same for all the attribute i choose'? Is it happening while browsing the cube? Measures look usually the same when you are browsing/querying data and the dimension is not used in measure group.
0
 
netra26Author Commented:
Thanks for your help. Now i would like to design a seperate databse for data warehouse can you please suggest me some best practises?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.