FRAMEWORK MANAGER - MODELLING PROBLEM

[giovanni.lucarelli]

Hello to everybody,

I have this question for you:

I'm modelling a DRM by cognos8.4 and I have this situation (PLEASE, SEE the attached .bpm) :

4 dimensions (fPRODUCT, CUSTOMER, ORDER DATE and SALES DATE) and 2 fact tables (SALES and ORDERS); PRODUCT is 1---n with SALES and ORDERS, and CUSTOMER too; ORDER DATE is 1--n with ORDERS and SALES DATE is 1--n with SALES.

I cannot use just one fact table (ORDERS + SALES) but end user wants reports in which there are for example:

ORDER YEAR- SALES YEAR- CUTOMER NAME- PRODUCT TYPE- SALES- ORDERS

I saw that Cognos use correctly "coalesce" fuction for this report and it's quite slow... but the worse is that i cannot use ORDER YEAR- SALES YEAR in the report on the same table... I can use just as filter, otherwise query crash; joins are ok, scope relationship too....so...this is my question:

anybody knows how to model better in this situation?

thans very much!!!

best regards

[blom0344]

cannot use ORDER YEAR- SALES YEAR in the report on the same table

Not sure what you mean by that..

There is no shared date dimension, which may be the problem.
Perhaps in this case it would be beneficial to create model query subjects that store both a fact and the dimensional attributes of the date in the same model object.

Not sure whether that is a textbook approach, but I wonder whether it would solve your problem..

[giovanni.lucarelli]

thanks very much to reply....

sorry for my english...I know it's no good  :-\

however:

I cannot create model query subjects that store both fact tables 'cause und user wants different and there is not a relationship in FM between them; then, about time dimension: i cannot create one shared date dimension because they want different name for attributes; until now I have one physical table (TDIM_TIME_TIME) then i reply this to avoid loop join and then two shortcut, rename in this way:

order date                         sales date
order year                         sales year                       
order semester                   sales semester
order quarter                     sales quarter
order month                       sales month
order day                           sales day


what do you think?

[blom0344]

No, my suggestion is to create a model query subject for EACH fact that stores both the fact and the attributes from the date dimension that is associated with the fact.
[The join between fact and date is then stored in the Model definition]
However, I realize this is against best practice, since facts should normally only stores keys and measures. However in your case you have both shared and non-shared dimensional objects, which may obstruct the multi-fact that Cognos tries to create.

[giovanni.lucarelli]

blom0344 , I understood...I think... you are suggesting me to crate a model query subject like this:


order date
order sem
order quarter
order month
order day
order measure1
order measure2
order measure3
....


is it right?

but ... see in the attachment what i have in my billing fact table for example:

more or less 30 dimensions, of which 10 or 15 are shared with other fact tables and the rest are not shared... I risk to have very very huge model query subjetc....

[blom0344]

Your example is quite familiar. I recall building MS Cubes with Analysis Services some years ago.
In the MS 2000 environment only one fact was allowed.
We reached a solution by designing a UNION view on Sales and Orders. In our case the Sales and Order tables closely matched in design. For difference in design we used dummy values (example: a zero for a measure)
With the union we had the benefit of having to use a dimension only once.
I doubt that such a solution would work for a relational solution, but it might be a nice one for a DMR model.
Performance will probably be better as well, since there is no longer any stitching required on the server.
The union can be executed on the database.

No idea if this is a viable option for you. You could build it alongside the existing model though