Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How to use Multiple Fact Tables from different databases?

Posted on 2008-06-24
6
Medium Priority
?
1,152 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 600 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

926 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