I have a dimension called Material which I build it from the Fact table
called - FACT_SALES.

But I also have two other Fact tables FACT_INVENTORY and FACT_BILLING.

In one database I have three cubes SALES, INVENTORY and Billing. All
three cubes have one common dimension material. I create material from
the FACT_SALES table as follows:

SELECT DISTINCT MATERIAL FROM FACT_SALES

How can I make material to be derived from the three fact tables.

Thanks
Karen

Hi Karen,

It's generally not a best practice to source dimensions (other than
degenerate ones) from dimension tables; but you may be able to use a
union subquery like this (either in a view in AS 2000, or in a named
query in AS 2005):

from (
select MaterialID
from FACT_SALES

union

select MaterialID
from FACT_INVENTORY

union

select MaterialID
from FACT_BILLING
) as SALES_INVENTORY_BILLING

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Oops - what I meant to say was:

"It's generally not a best practice to source dimensions (other than
degenerate ones) from fact tables..."

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Hi Deepak

Many thanks for clarifying this that is what I currently do. But my
problem is one fact table gets loaded at one time and the other one at
a different time. Considering this what time do we process the
dimension Material.

Also, kindly clarify how I can share a dimension that is common between
two databases (AS Databases)

Thanks
Karen
Deepak Puri wrote:

The safest approach would be to update the dimension after each fact
table is loaded.

Not sure why you would need to share a dimension across 2 AS databases,
rather than using a single database, but each database could have its
own copy of the dimension, while using the same source table/view. Of
course, this would mean that the dimension would have to be processed
separately in each database.


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Many thanks for clarifying this point.

So when do we use different databases for a cube. If we are building
analytic apps in a company invariable at some stage the same dimension
will be needed in another cube if we split them into different
databases we will risk maintaining the same dimension is 2 places.
Problem of building all cubes in one database is archiving the database
can be very time consuming.

Can you please share the dos and donts on when should we use a
different database is it for every data mart or when we expect
dimensions in a new cube is already in a existing database then do we
build the cube in that database.

If we try building a new cube where there are some dimensions in the
existing database but if the existing AS database as a source system
from a SQL database and the new cube is from a different SQL database
or data source how do we manage these kind of scenarios.

Thanks
Karen

Deepak Puri wrote:

Well, dealing with different data sources certainly imposes constraints
when working with AS 2000 - AS 2005 is more flexible in terms of
supporting multiple data sources. With multiple SQL Server source
databases on the same server (as is sometimes the case for us), you can
use views to make data in database visible in another, so that a single
data source can suffice.


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***