Cognos Dynamic Cube Model - Derived Measures?

[CharlesAZ]

Greetings!

Working on my First Dynamic Cognos Cube.  Majority of our Measures in our existing Star Schema require derived measures based one or more fields from my dimensions, example:
Case
When Indicator = Y and Start Date is not null
Then 1
Else
0
end

Point is, some of my measures I need are not in my fact table, they have to be derived.


I am not seeing how to do that with Dynamic Cubes, in transformer I would handle this at the framework level or at he report Studio level that was used as a source for my transformer model

Is the above possiable?  What I am running into is only one table(the fact table) can be associated to the measures.  Can someone please help me confirm this, or if it is possiable let me know how it is done, I appreciate it!

Thanks

Charlie

[bus_pass_man]

It is possible.  You'll have to do it with a virtual cube, though.

Each cube can have only one fact table.  A cube is a star schema query plan.  In order to model for predictable results you need that. 

Multiple facts are modeled with virtual cubes, which allow you to map two cubes or combinations of cubes and other virtual cubes. 

You can use dimensions in any of the cubes in which they are conformed. If you look at the icons for dimensions in cubes you will see the little windows shortcut overlay.   

The sample model has a virtual cube which you might want to look at for reference.   

Keep in mind if the fact grains differ.   

I recommend the IBM Cognos Dynamic Cubes Redbook.  The Redbook is free in PDF or HTML.  You can also order it in hardcopy.
http://www.redbooks.ibm.com/abstracts/sg248064.html

hope that helps.

[CharlesAZ]

Hi, I understand virtual cubes, but I am not sure your understanding my requirement, the measure I need are not from another fact table, they are derived based on atributes within a given dimension that is associated to the one fact table.   Does that make sense?

Thanks

Charlie

[bus_pass_man]

Yep, I understand what you're trying to do.   And, (trying to be gentle here) you have not entirely succeeded in persuading me that your understanding of the material is on as firm a basis as you believe it is.

If the columns which you are trying to operate on to derive facts exist in your fact table then you should be able to do that. Cube Designer will allow you to create measures out of non-fact columns from the fact table. Since all the things which you want to call facts exist in the same data base table, Cube Designer will allow you to proceed.

If the columns which you are trying to operate on to derive facts exist in a dimension table then you are trying to use a second fact table.  This means a separate query has to be generated.

A dimension table can be a fact table if it is being used as the source table for the facts of a query.  There's some cases (such as factless facts) where you want to derive a fact from non-fact data. In other queries, the dimension table would be just a dimension table.  You could have the table used as both the source of the facts and be a dimension in the query.  This case is called a degenerate dimension.

The point is, to model successfully you need to plan the queries.  To plan the queries you need to tell the query engine how to figure out how to generate the SQL. The query engine needs to know what the fact table is. All of the tables in any dimension (if you are using a snowflake) need to have an unambiguous path of relationships between the entities in the dimension and from the dimension to the fact table.

Each cube can only use facts from one fact table.  You'll get an error if you try otherwise.  I think you've encountered that error message.  It's there for a purpose.  And it happens to be relatively clear.  The purpose is so that each cube generates a relational model which is a virtual star schema. 

You would use a virtual cube so that the result sets from the cubes can be merged.


I'd recommend that you brush up on Kimball.  Adamson's star schema is good and newer too.  He's fully Orthodox Kimballite.  Reading the IBM Redbook on Dynamic cubes is useful too.  Reading up on Framework Manager's manual and the best practices or proven practices (I can't remember what they want to call it this week) doc would help too.

[CharlesAZ]

Issue was resolved by adding the required measures to the existing fact table this in turn prevented us from having to use the virtual cube type of solution.
Thank you for your feedback. 

Charlie