This proven design is based on using a data warehouse as the data source. 4 Layers are implemented as namespaces in Framework Manager as follows:
Database Layer:
A replica of the source database. Use the Metadata Import wizard to get tables into this namespace.
Do not meddle with the data source queries created by the import as this will adversely affect query performance for end users.
The other tasks that need to be done in this layer are:
a. Set usage correctly – “identity” for surrogate keys, “fact” for sumable numbers, otherwise set to “attribute”.
b. Create 1:N relationship from each dim to fact. I prefer to do this manually rather than allow the Metadata Import wizard to do it incorrectly. Only inner joins, not outer joins!!!
c. Add Determinants on dimensions only, no determines on facts. Top down approach – group by from the top down to unique for surrogate key at the bottom.
Modelling Layer:
Here we create queries that reference data source queries from the Database layer. At a minimum, create identical query subject in the Modelling layer for each data source query in the Database layer. You can also rename queries and its items to your hearts content. If you’ve got a well designed data warehouse as the data source you should not have to rename all the items. The one thing I do is take out the word “Dim” and “Fact” from the query subject names to make them more user friendly.
Relationships are duplicated/recreated in this layer from the Database layer. If sub dimensions or sub facts queries are created then additional relationships need to be added that do not exist in the Database layer.
Other tasks you can do in this layer
a. business calculations eg gross margin, gross margin %
b. amounts in standard currencies
c. folders inside query subjects to organise query items rather than just seeing a big long list of item
d. hide items eg surrogate keys
e. set default formats for numbers and dates
DMR Layer (Optional):
This is where we create multi-dimensional ROLAP cubes containing dimension hierarchies and measures.
Presentation Layer:
Contains star schema groups (right click on each fact in the Modelling layer and and each measures dimension in the Dimensional layer to create and cut and paste into this layer).
Optionally, create namespaces or folders to separate relational objects from dimensional objects. I like to create two namespaces, “Query” and “Analysis”. Query is for the SQL fact based star schema groups and Analysis is for the Dimensional star schema groups
Database Layer:
A replica of the source database. Use the Metadata Import wizard to get tables into this namespace.
Do not meddle with the data source queries created by the import as this will adversely affect query performance for end users.
The other tasks that need to be done in this layer are:
a. Set usage correctly – “identity” for surrogate keys, “fact” for sumable numbers, otherwise set to “attribute”.
b. Create 1:N relationship from each dim to fact. I prefer to do this manually rather than allow the Metadata Import wizard to do it incorrectly. Only inner joins, not outer joins!!!
c. Add Determinants on dimensions only, no determines on facts. Top down approach – group by from the top down to unique for surrogate key at the bottom.
Modelling Layer:
Here we create queries that reference data source queries from the Database layer. At a minimum, create identical query subject in the Modelling layer for each data source query in the Database layer. You can also rename queries and its items to your hearts content. If you’ve got a well designed data warehouse as the data source you should not have to rename all the items. The one thing I do is take out the word “Dim” and “Fact” from the query subject names to make them more user friendly.
Relationships are duplicated/recreated in this layer from the Database layer. If sub dimensions or sub facts queries are created then additional relationships need to be added that do not exist in the Database layer.
Other tasks you can do in this layer
a. business calculations eg gross margin, gross margin %
b. amounts in standard currencies
c. folders inside query subjects to organise query items rather than just seeing a big long list of item
d. hide items eg surrogate keys
e. set default formats for numbers and dates
DMR Layer (Optional):
This is where we create multi-dimensional ROLAP cubes containing dimension hierarchies and measures.
Presentation Layer:
Contains star schema groups (right click on each fact in the Modelling layer and and each measures dimension in the Dimensional layer to create and cut and paste into this layer).
Optionally, create namespaces or folders to separate relational objects from dimensional objects. I like to create two namespaces, “Query” and “Analysis”. Query is for the SQL fact based star schema groups and Analysis is for the Dimensional star schema groups
