topic Multiple Fact tables in a cube? in SAS Data Management

Multiple Fact tables in a cube?

wrifster — Tue, 12 Jul 2011 21:37:16 GMT

All the documentation I read seems to indicate that a cube can be built from only a single fact table.

How are people dealing with scenarios that need to cross multiple fact tables? e.g. If my actuals are in one fact table and my forecasts are in another fact table. If I build two cubes, how can I report across the two cubes?

It seems like an Information Map can only include a single cube, is this correct?

Arif

Multiple Fact tables in a cube?

nar_sas — Wed, 13 Jul 2011 21:24:42 GMT

Here is an answer from development:

Correct, an information map can currently only wrap a single OLAP cube. As for the multiple fact tables, a user would have to create a single fact table with the summarization of actual and forecast in one table. They can achieve that result using the data step. Then they can build the cube they want, and then use that in the map.

Multiple Fact tables in a cube?

wrifster — Wed, 13 Jul 2011 22:20:51 GMT

I wish the world was such a simple place where you could create a single fact table with everything in one table. Usually this is not desirable because of different granularities between the fact tables. As well it is quite common that the fact tables share some dimensions but not all - in this case how can you combine everything into one fact table.

I hope someone in SAS is aware of how limiting and restrictive this is for real use cases.

Arif

Multiple Fact tables in a cube?

nar_sas — Thu, 14 Jul 2011 12:15:07 GMT

Thanks for the response. Having use cases and details about them really helps us in scoping for future development. We have captured your comments in our requirements management system, and we will investigate extending this feature set for the future.

Multiple Fact tables in a cube?

jplarios — Sat, 23 Jul 2011 23:46:39 GMT

wrifster,

we are in the same boat. 'Drill-across' is a piece that's still missing in SAS BI, you have to work around it and when talking about an OLAP cube, a detail table, or flat file, where you have both facts would be the only way. However, it is true that this causes issues because of the 'mixed' granularities.

Re: Multiple Fact tables in a cube?

jplarios — Sat, 23 Jul 2011 23:50:14 GMT

nar, thanks for having look into this. at least in my opinion, I think that drill-across and drill-down mechanisms are essential technologies for any BI; at least for 80% of reporting needs.