Solved

How to use Multiple Fact Tables from different databases?

Posted on 2008-06-24
6
1,124 Views
Last Modified: 2016-02-13
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.
0
Comment
Question by:netra26
  • 3
  • 2
6 Comments
 
LVL 8

Expert Comment

by:srnar
Comment Utility
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
 

Author Comment

by:netra26
Comment Utility
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
 
LVL 8

Expert Comment

by:srnar
Comment Utility
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
 

Author Comment

by:netra26
Comment Utility
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
 
LVL 8

Accepted Solution

by:
srnar earned 150 total points
Comment Utility
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

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now