Solved

How to use Multiple Fact Tables from different databases?

Posted on 2008-06-24
6
1,127 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
ID: 21856702
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
ID: 21857554
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
ID: 21863100
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
ID: 21881934
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
ID: 21882439
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes some very basic things about SQL Server filegroups.
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…

911 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

23 Experts available now in Live!

Get 1:1 Help Now