Below are some proven practices to follow when Specifying Dimensional
Information in Framework Manager
1. Ensure that keys and other identifiers have their usage specified as identifier.
2. When a surrogate key is available for a level use it as the level key and treat
the business key as an attribute.
3. Use the item you would use in reporting as the level and the id (forms one
end of a relationship) as the key for the level (remember to reflect this in the
prompt info for the sake of query efficiency).
4. For each level associate any query items whose granularity is specific to that
level.
Wednesday, September 14, 2011
generated SQL
It is important when specifying your dimensional information to understand the
affect this will have on the SQL that is generated. Dimensional information
affects grouping and aggregation of data, not only from the dimension data
source but also from other data sources that have relationships with that
dimension. Specifying hierarchies for a query subject using query items as
levels, either individual or combinations of query items as keys, and associating
query items to the appropriate levels as attributes provides FM with enough
information to automatically group and aggregate data according to the
hierarchies you have specified. Hierarchies in this case can be thought of as
ways of specifying the order in which data segmented into levels can be
grouped.
affect this will have on the SQL that is generated. Dimensional information
affects grouping and aggregation of data, not only from the dimension data
source but also from other data sources that have relationships with that
dimension. Specifying hierarchies for a query subject using query items as
levels, either individual or combinations of query items as keys, and associating
query items to the appropriate levels as attributes provides FM with enough
information to automatically group and aggregate data according to the
hierarchies you have specified. Hierarchies in this case can be thought of as
ways of specifying the order in which data segmented into levels can be
grouped.
dimensional information
When specifying dimensional information in FM it is important to understand the
following.
• What are the levels in your dimension?
• What is the order and combination of levels that form hierarchies.
• How do you uniquely identify the members of a level, is it unique (one
query item) or does it require multiple query items?
• Association of levels with attributes and measures.
• At what level of granularity is your fact data, do you have more than one
level of granularity? Ie. Is some of your data recorded monthly and some
daily, this would mean that you would have a fact tables relating to your
time dimension table at the month and day levels.
• Are there surrogate keys defined in your data source?
following.
• What are the levels in your dimension?
• What is the order and combination of levels that form hierarchies.
• How do you uniquely identify the members of a level, is it unique (one
query item) or does it require multiple query items?
• Association of levels with attributes and measures.
• At what level of granularity is your fact data, do you have more than one
level of granularity? Ie. Is some of your data recorded monthly and some
daily, this would mean that you would have a fact tables relating to your
time dimension table at the month and day levels.
• Are there surrogate keys defined in your data source?
Saturday, April 16, 2011
Subscribe to:
Posts (Atom)