DMR Model Creation (Date DIM and FACT)

[gosoccer]

Hello, it's me again! 8) 8)
::) ::)
How is a Time Dimension linked to the actual FACT table? If a unique ID
exists for each "CASE of selling a car" and it's "Selling Date" that the car was
sold, I would need a Relationship in FM Model between the Date Dimension
subject Query and the FACT table (0..n) Before building the Regular Dimensions
and the Measures. Right?

I have a Date Dimension Subject Query (Unique ID, Year, Month, QTR, Date (unique))

I have a FACT Table Subject Query (Car ID, Car Type, Date Sold, Color ID, Amount Sold (MEASURE))
So, Question: Before building the Time Dimension from the Date Dimension, I need to make sure a Relationship
exists between the Date Dimension and the Fact Table, right?

Man, I sure miss having Transformer involved with this project.

Thanks in Advance for your time. :D :D

[bus_pass_man]

Hello

I am saddened to learn are you working on the weekend.

Yes, like any other dimension, the underlying relational tables need to have a relationship defined to the relevant fact tables.  If you don't you'll get cross join errors when you try to do a query.

If you have role-playing you will need to use aliases.  The sample models can provide examples of that.   There's one relationship based on sales date, another based on ship date, and a third based on close date whatever that is.

It isn't strictly necessary for the relationship to be defined prior to creating the dimension but if you do the relationship is leveraged to auto-create the scope relationship, although you'll need to confirm what the actual grain is and set the scope for that.

[gosoccer]

Thank you,

It seems like every job requires new learning and that's when I get stuck. No worries, I still did my 30 minutes run today.
Transformer was so much easier to use.

I really didn't understand role-play and grain you mentioned but I'll dig in.

Ok, I'm going after this now. I got the Physical and Business Layer established (I think).

Thanks so much for your time!!

[gosoccer]

Ok, two more question then I leave you alone: :) :)

For the following type of Dimension, I have the following need to show on a Tree form in the report:
State
       County
                 City
Example:
MD                                   
    Cheersville
                   Cheers
So, if I need these to be generated in none Dimension called location/geo, establish the relatioships between the 3 Subject Queries  for State (CD, DESC), County (CD, DESC), and CITY (CD, DESC) right. and then build one Regulation Dimension but get all the CDs and DESCs into one Dimensional Heirarchy when I build the New Dimension? Of course DESC would be a Caption nd not a business key.

2) In one subject Query, we have 12 columns that some are measures and some are unique id's related to Location, Date Dimensions, etc).
Is it a better practice to have the calcucations (FACT) into a different Dimension as a part of the Measure DIMENSION and the others into a Regular Dimension?

I know these are some basic questions but I'm hitting a very short time frame unfortunately.

Thanks again for your time......

Cheers. :) :)

[bus_pass_man]

Yeah.  Take a look at the products dimension in the go sales sample model for an example of modeling a snowflake.

For 2, no except if you have a degenerate dimension.

Role-playing and fact grains are industry terms.  You will find a discussion of them in Kimball's data warehouse book and others (such as Adamson's star schema).  The Cognos educational materials and courses, which I recommend, also deal with them.

When you set the scope for a measure in Transformer you are specifying the fact grain.  Transformer allows you to project fact values below the grain using allocation. 

The various technologies have trade offs.  There's a table somewhere that acts as a decision guide about what to use.
Found it:
http://www-01.ibm.com/support/docview.wss?uid=swg27036155

[gosoccer]

Ok, Thank you so much! I'm good to go and will review the link.
Have a great rest of the weekend. :) :) :)

[MFGF]

As this is a post about DMR I have moved it from the Dynamic Cubes board to the Framework Manager board.

MF.