Multiple fact tables in SSAS 2005

Can someone please provide me with a link to an article, which explains the concept of multiple fact tables in SSAS 2005?

Thanks in advance,

Saurav

Are you talking about multiple fact tables as base for partitions or different measure group. In other words, are your fact tables have exactly the same structure? Or the structure is different and every fact table has it's own set of key columns?

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

I do not have a link to an article.

If you are aware of the design in AS2000 you know that you can use one fact table in each cube. This is referred as a physical cube because it has an fact table tied to it.

On the other hand you can join two or more cubes(physical) in virtual cubes.

In AS2005(or SSAS2005) a cube can include one or many fact tables and each fact table becomes a measure group in this cube.

The concept of virtual cubes have disappeared in SSAS2005 but you can say that a cube with many fact tables(more than one) is a virtual cube in the old AS2000 meaning.

Most recent findings and post regarding using more than one fact table / measure group in SSAS2005 say that you should be careful about this.

Regards

Thomas Ivarsson

Multiple Fact Tables

I have two fact tables in a cube...Both fact tables have plenty of measures, some even have the same measures but are name differently (ex. Bad Phone Number, Number of Bad Phones). When I process the cube, only the measures in the most recent fact table (A) show up. The oldest fact table does have some measures to show up in the cube but I was expecting that all the measure in both fact tables would show up. Both fact tables are connected to the same dimensions. Any ideas?

So when you open up the cube in BIDS, can you see all the measures you expect in the list on the top left hand side? If not you can drag the columns you want from the fact tables in the diagram in the centre pane to the measures list. If you cannot even see these columns in the diagram then you must have altered your table structures after building your DSV and you will need to open up your DSV and refresh it before going back into the cube designer to create the measures.

Hope this helps.

|||

Darren, thanks a bunch this worked

Multiple Columns of Fact pointing to same dimension

Hi All,
Here is my scenario.
My fact table has the following
TransactionFiscalWeekId, BilledFiscalWeekId, PaymentFiscalWeekId, Amount
The dimension table has
FiscalYear,FiscalQuarter,FiscalMonth,Fis
calWeek,FiscalWeekId(lowest grain)
Now i need to have dimension built individually on each of the fact table co
lumns (TransactionFiscalWeekId, BilledFiscalWeekId, PaymentFiscalWeekId). Th
e three id(s) will have different values for the same row.
To implement this i need to build three dimensions on the same relational ta
ble. Basically In dimension build editor pick up the same table thrice and b
uild three individual dimensions. The editor will now have one fact table, a
nd the dimension table in t
hree different aliases.
Join as follows
Fact.TransactionFiscalWeekId = dimAlias1.FiscalWeekId
Fact.BilledFiscalWeekId= dimAlias2.BilledFiscalWeekId
Fact.PaymentFiscalWeekId= dimAlias3.PaymentFiscalWeekId
Questions.
1. Is there any way for me to use a single shared dimension FiscalPeriod and
achieve the same functionality?
2. Any better alternative design for the above functionality?
Thanks a lot!!
LalithaYou can use views to reference the same relational table more than once
Ray Higdon MCSE, MCDBA, CCNA
--
"Lalitha" <lalithas512@.hotmail.com> wrote in message
news:08F4F84C-CD32-42FF-9F22-B9A5DB42F2C5@.microsoft.com...
> Hi All,
> Here is my scenario.
> My fact table has the following
> TransactionFiscalWeekId, BilledFiscalWeekId, PaymentFiscalWeekId, Amount
> The dimension table has
> FiscalYear,FiscalQuarter,FiscalMonth,Fis
calWeek,FiscalWeekId(lowest grain)
> Now i need to have dimension built individually on each of the fact table
columns (TransactionFiscalWeekId, BilledFiscalWeekId, PaymentFiscalWeekId).
The three id(s) will have different values for the same row.
> To implement this i need to build three dimensions on the same relational
table. Basically In dimension build editor pick up the same table thrice and
build three individual dimensions. The editor will now have one fact table,
and the dimension table in three different aliases.
> Join as follows
> Fact.TransactionFiscalWeekId = dimAlias1.FiscalWeekId
> Fact.BilledFiscalWeekId= dimAlias2.BilledFiscalWeekId
> Fact.PaymentFiscalWeekId= dimAlias3.PaymentFiscalWeekId
> Questions.
> 1. Is there any way for me to use a single shared dimension FiscalPeriod
and achieve the same functionality?
> 2. Any better alternative design for the above functionality?
> Thanks a lot!!
> Lalitha|||Thanks for the response.
In relational tables part, i am clear. we can just form three different dime
nsions based on same underlying relational table.
But My idea is to use a single shared dimension.
The reason being a change in Fiscal hierarchy will involve change in one pla
ce alone then.
If i have three separate dimensions, then all three has to be changed.
Can i do something like three different private virtual dimensions
using a single shared dimension Fiscal Period?
Thanks
Lalitha

multipass query many fact tables

I am new to DW. I am required to design reports which come off of DW. I found a rpt requirement which is from 4 different fact tables, each of it at a diff grain level.

The results change dramatically. I am not joining fact tables directly, but am joining them through common dimension. I am concerned about the correctness of the recs returned. I have also tried to create a view with a facttable A and its dimension tables, and linked that to the originial facttable B which looks like the report theme(In this approach the # of recs will more or less be = to B's records.

I am not finding any examples apart from multipass query mentioned. I would like to see an example to understand. Could anyone point me in the rt direction?

Thanks

Are you working with Analysis Services or just the SQL Server relational database technology?

If you are working with the relational technology, you may need to aggregate your data to a common granularity before performing your joins. This is significantly easier to handle through SSAS.

B.

|||

I am working with SQL Server Relational db(2005).

Thanks