PHYSICAL LAYER
1.When modelling a star schema data model, create
aliases for all your physical tables (prefixed with either “Dim_”, “Fact_” or
“Fact_Agg_”
2.When possible, configure your connection pools to use
a “native driver” to connect to your physical databases. For example, use OCI
for connecting to an Oracle database rather than ODBC.
3.Make sure the “Max Connections” parameter on your
connection pools are appropriately set (not too low....not too high!). If in
doubt, you can use the following formula which assumes that no more than 4% of
your users will ever be logged on and running a report at any one moment:
Max Connections = Total Users * 0.04 *Max Reports on a
Dashboard
So if you have 1000 users and you have no more than 4
reports on any one dashboard, then your
“Max Connections” should be set to 160.
NOTE: It is easy to exaggerate the number of users who
will be using the dashboards simultaneously
BMM LAYER
4.All Logical Tables should be prefixed with either
“Dim –“, “Fact –“ or “Fact Compound –“.
5.No “physical” column names should ever be seen on the
Business Model layer. All naming conventions should be “business oriented”. For
example use “$ Revenue” rather than “DOLLARS”.
6.Physical Primary Keys or Surrogate Keys should not be
present on the Business Model layer (unless, for example, you have a Primary
Key such as Order Id which will be displayed on reports).
7.Dimension Logical Tables must always have a Logical
Key assigned. The Logical Key should be something “business oriented” such as
“Employee Login” rather than “EMPLOYEE_PK”.
8.Dimension Logical Tables must only contain dimension
attributes, they should never contain any measure columns (which have an
Aggregate rule).
9.Fact Logical Tables should never have a Logical Key
assigned
10.Every Logical Column within a Fact Logical Table
must be a measure column, and therefore have an Aggregation Rule assigned.
11.The Business Model should only consist of logical
star-schemas, there should not be any snow-flaking.
12.Every Dimension Logical Table should have a
corresponding Dimension Hierarchy (with “Total” as a Grand Total level, and
“Detail” at the lowest level.
13. Each level of a Dimension Hierarchy should have its
“Number of Elements” appropriately set (there is a utility that can do this
automatically for you).
14. Every Logical Table Source within every dimension
and fact Logical Table should have its “Content Levels” appropriately set.The
only time the “Content Level” is not set for a particular dimension is when
there is no logical relationship existing.
15.Do not merge all your measures into a single Fact
Logical Table. For example, you should split “Forecast Sales” and “Actual Sales
” measures into two Logical Tables e.g. “Fact–Sales” and “Fact–Forecast”.
16.For your Dimension Hierarchies, only enable the
“Ragged” and “Skip Level” options if your hierarchies genuinely contain Ragged
and/or Skip Levels..
PRESENTATION LAYER
17.When you have multiple Subject Areas, list the
common dimensions in the same order across all the Subject Areas.
18.Presentation Table names within each Subject Area
must not begin with “Dim–“ or “Fact –“ or “Fact Compound –“. So remove these
prefixes if they are present after creating the Subject Area by dragging
Logical Tables directly from the Business Model.
19.The “Time” presentation table should be listed as
the first Presentation Table in each subject area. The Presentation Table
containing your facts should be listed right at the bottom, and the Presentation
Table should be called “Measures”.
20.There should be absolutely no possibility of a user
selecting objects from a Subject Area that have no logical relationship. So, if
there are any objects within the same Subject Area that cannot co-exist in the
same report, then your Subject Area design is incorrect! (Remember, users can
configure an Analysis to source from multiple Subject Areas, so you don’t have
to cram all your objects in one Subject Area).
No comments:
Post a Comment