REGULAR AGGREGATE in Framework Manager

[beinownow]

Hi everyone,

We have this new relational model we are building we are finishing our data Layer and just remembered that we need to apply  REGULAR AGGREGATE like SUM on our query items.

We currently have like 2-3 on our fact query subject: [CreditAmnt] for example.

MY QUESTION: Do We  automatically need to "think" to apply REGULAR AGGREGATES (SUM or Count or Average, etc...) whenever there is a fact type query item ?

Thank you !

[Lynn]

Hi everyone,

We have this new relational model we are building we are finishing our data Layer and just remembered that we need to apply  REGULAR AGGREGATE like SUM on our query items.

We currently have like 2-3 on our fact query subject: [CreditAmnt] for example.

MY QUESTION: Do We  automatically need to "think" to apply REGULAR AGGREGATES (SUM or Count or Average, etc...) whenever there is a fact type query item ?

Thank you !

Yes. If you are modeling you should think about EVERY property for every data item, including the aggregates. Things with usage set to fact will default to sum, so the thinking is to decide if that is the appropriate aggregation method.

In my opinion, sloppy modelers don't think enough about things such as default data formats, screen tips, usage, other properties in the model, or the ability to create calculated items that can greatly simplify the report authors task and help achieve consistency.

Part of the documentation I produce for any Framework Modeling project includes a checklist of things like this along with any rules the customer decides are important, such as default data formats for currency vs different types of numbers vs percentages etc. This ensures that anyone working on the model is following the same guidelines and procedures.

[beinownow]

Thanks Lynn,

By the way we have to apply the appropriate regular AGGREGATE directly in the database Layer ?

[Lynn]

Thanks Lynn,

By the way we have to apply the appropriate regular AGGREGATE directly in the database Layer ?

If you do it in the database layer it will carry forward whenever you reference it in model query subjects, so generally I'd say that is the best place to set it.

[beinownow]

OK,

Another quick question: When constructing our model,  what do we do if we encounter date columns not in a date format  ?

I mean we have a column with dates like:
70516
150102
80733

Do we need to ask the DBA to convert it in a real date format BEFORE we the modelers import it in our Framework Manager model ? Or do we need to take it as it is and then apply what ever to_date conversion function to (In the database Layer ?)

[cognostechie]

You might want to check if that column is a DateKey or the Date that you should expose to the users? If it is a Key then that should be used only to join to another table (presumably a fact table) and should not be part of the Business Layer. The Date column should be in the Business layer which should be in Date format and that should be done in the DB itself.

As an example, your Date table in the DB could be like this:

Datekey    Date               Year       Month
---------    -------------     -------    --------
70516      2015-01-01     2015      01   

In this case, you would join the Datekey to the Fact tables but not use this in the Business layer. The 'Date' should be exposed in the Business Layer.

[bdbits]

Is your data source an application database or a separate data warehouse (dwh) populated by ETL?

If it is a dwh, as a rule the best time to do data type conversions is during ETL. Then do as cognostechie laid out for you.

Also, if this is a dwh and you are not already doing so (it was not clear to me), I would very very strongly urge you to convert all of your dates to foreign keys to a date table. The date table/dimension when linked to your dwh facts might be the single most important table in your dwh. You'll thank me later.  :)