FACTS and DIMENSIONS IN FM MODEL (STAR SCHEMA)

[gosoccer]

Hi everybody,

I'm used to OLAP (Cube based Reports) and Relational Model but with this new job, I'm stuck with DMR
(I think). 

I suppose to build the FACTS and DIMENSIONS from the supporting TABLES in our database. So, here is
my questions:

1) In order to use FACTS and DIMENSION in Framework Model, is the STAR SCHEMA creation mandatory.

2) While creating the STAR Schema, I need to have a Foreign key for every primary key from Dimensions. Well
when I try dragging my PGM_ID or any other unique column (related to Dimensions (look up tables) to my
Central FACT table I'm getting the following error:

You can not add identifier such as PGM_ID to the fact Query Subject. You may only add facts to the fact query subject.
Do you know by any chance what I need to do to have the PGM_ID dragged to my FACT Query Subject.

Thx so much for your time.


.

[MFGF]

Hi everybody,

I'm used to OLAP (Cube based Reports) and Relational Model but with this new job, I'm stuck with DMR
(I think). 

I suppose to build the FACTS and DIMENSIONS from the supporting TABLES in our database. So, here is
my questions:

1) In order to use FACTS and DIMENSION in Framework Model, is the STAR SCHEMA creation mandatory.

2) While creating the STAR Schema, I need to have a Foreign key for every primary key from Dimensions. Well
when I try dragging my PGM_ID or any other unique column (related to Dimensions (look up tables) to my
Central FACT table I'm getting the following error:

You can not add identifier such as PGM_ID to the fact Query Subject. You may only add facts to the fact query subject.
Do you know by any chance what I need to do to have the PGM_ID dragged to my FACT Query Subject.

Thx so much for your time.


.

Hi,

So, are we talking about DMR Regular Dimensions and Measure Dimensions here, or are we talking about query subjects that represent fact and dimension tables in a data warehouse?

Assuming the latter, the easiest solution for you is to have physical fact and dimension tables in your database to point FM at. If you don't have these, then best practice is to have a multi-layer FM model where the bottom (foundation) layer points to the physical tables you have and describes their relationships etc, and then another layer above this comprising model query subjects that look like fact and dimension tables.

Cheers!

MF.

[cognostechie]

Hi everybody,

I'm used to OLAP (Cube based Reports) and Relational Model but with this new job, I'm stuck with DMR
(I think). 

I suppose to build the FACTS and DIMENSIONS from the supporting TABLES in our database. So, here is
my questions:

1) In order to use FACTS and DIMENSION in Framework Model, is the STAR SCHEMA creation mandatory.

2) While creating the STAR Schema, I need to have a Foreign key for every primary key from Dimensions. Well
when I try dragging my PGM_ID or any other unique column (related to Dimensions (look up tables) to my
Central FACT table I'm getting the following error:

You can not add identifier such as PGM_ID to the fact Query Subject. You may only add facts to the fact query subject.
Do you know by any chance what I need to do to have the PGM_ID dragged to my FACT Query Subject.

Thx so much for your time.


.

In which layer are you trying to add the PGM_ID? The Database Layer (physical layer) or the Business Layer. Assuming that you are trying to add it in the Business Layer Fact , make sure that the Fact is joined to the Dimension containing PGM_ID in the Database Layer.

The answer to question 1 is that it is not mandatory but it is strongly suggested.

[bus_pass_man]

Answer to question 1.

A very potted modeling overview:

One important objective of modeling is the creation of a query structure which will produce consistent, predictable, and correct results.   The primary means for that is through the design of virtual star schemas of fact tables and their associated dimension tables (which can be snowflakes as well).  There would be one and only one possible query path between a fact table and a dimension table.  This would result in only one possible join specification in the SQL statement of the query.  If you don't have that structure then the query engine needs to pick an arbitrary path, which might not be the one that you want.

If you take a Cognos modeling course you will learn about this topic.

This is the case for pure relational as well as DMR modeling.  So the answer is, yes, you need your query layer to have stars.  The source data base(s) don't need that but you need to transform the relationships in the query layer to reflect your query design.

Answer to question 2
It sounds like you're using the model design accelerator.   

The MDA enforces the use of facts only in the fact query subject. 

Relationships between the fact query subject and the dimension query subjects are generated automatically using the pk/fk information in the data base.

If there are no pk/fk information in the data base then you need to manually create the relationship.

When you create your dimension query subjects an indication will show up where the dimension query subjects have pk/fk already and where they don't (see the enclosed picture MDA 1.png).


Just as an aside, the MDA UI is extraordinary in the way that it seems to have been designed by a person with not only absolutely no interest in thinking through the work flow or the subject area of modeling in general but also a passive-aggressive hatred of his users by providing a bare minimum amount of functionality which does not actually actively, intentionally, prevent the user from doing his task but forces him to try to think how to work around the UI to accomplish that end.  Plus a basic absence of any sense of esthetics and any feel for designing user interfaces.

[bdbits]

Wow, good catch bus_pass_man, I was trying to think of where gosoccer was dragging things into fact tables that would generate such a message, as I have almost never used the MDA. I generally find "wizards" like MDA are at best a starting point, and generally have tons of assumptions and are geared to novice users. I understand the challenges in designing said wizards and have sympathy for the developers of them. Unfortunately, the users usually have no idea what the underlying assumptions are, much less what they might need to look at after the wizard generates something. The end result is usually sub-optimal, if it actually works at all.

[cognostechie]

I have never used the MDS either. Actually, when I run the metadata wizard, I never even use the option on the last screen which generates joins
buy detecting keys. I always check that out as I prefer to do it myself. I just have more control that way.

By the way, congratulations on becoming a moderator. I just noticed it.

[bdbits]

I am the same way, cognostechie. I'd rather do it myself than fix what the wizard generated.

And thanks.  8)

[gosoccer]

Thanks so much cognosrtechie, bdbits,  bus_pass_man, and MFGF.

This is great information and since this is just a Subject Query based implementation using
Facts and Dimensions, I'll just go stick with building the Physical Model, Business Layer, and
Presentation Layer in FM, establish the Relationships, and go from there.

Thanks again for all your time.