Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to use Multiple Fact Tables from different databases?

Posted on 2008-06-24
6
Medium Priority
?
1,147 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

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 this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

688 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