SSAS #24 – Implement Attribute Relationship in SSAS 2008

Do you still remember the blue squiggly from my previous blog post, SSAS #23 – Implement dimensional hierarchy in SSAS 2008?

On the Hierarchies tab in the Dimension Designer, the blue squiggly says:

“Attribute relationships do not exist between one or more levels in this hierarchy. This may result in decreased query performance.”

You’ve already know that this is a Best Practice Warning and it is telling us to create attribute relationships.

Fortunately, in SSAS 2008, attribute relationships are no longer a “hidden” feature, as in SSAS 2005 where there is no graphic to show the relationships. Now we have a dedicated tab with graphics. How cool can it get?

After the drag-and-drop of the two attributes, TreatmentType and Treatment, to the Hierarchies tab, the initial graphics on the Attribute relationships shows:

  

This is not exactly correct.

The correct graphics should be:

The trick here is to create the attribute relationships in reverse, that is, lower-order objects (many) then higher-order objects (one), instead of in the order of one-to-many.

There are two ways to get the relationships into the correct order.

One way –drag the lower-order object and drop in onto the higher-order object

Give it a try. Drag Treatment and drop it onto TreatmentType.

Another way – use the Attribute Relationships pane at the lower right corner.

Right click on the relationship. Select Edit Attribute Relationship…

Make sure these:

• The One side of the relationship = Source Attribute
• The Many side of the relationship = Related Attribute
• Relationship type = Rigid (in my example, the relationship will not change over time)

 

The blue squiggly on the Hierarchies tab disappeared

Here is the proof.

Closing Remark

In SSAS #22, 23 and 24 posts, I made the following assumptions:

• you will use the star schema in your data mart design, and
• there are natural one-to-many relationships among dimensional attributes, therefore,
• the dimensional hierarchical structures are stored in one single dimensional table, and
• the dimensional table is a non-parent-child table

There are many discussions about Snowflake schema verses Star schema. If you use a Star schema, the Dimension Wizard will not be able to detect the natural hierarchies that exist.

Dimension Role-Playing in Employee Comp Reporting

The employee hierarchy table is just one physical table, with a date range to keep employee’s employment history. This same employee hierarchy table can be used sometimes to show different business functional teams, for example, LAS VS. Underwriters on the same fact table. 

I can simply create two SQL views to handle this, or just use two sub-queries if I don’t want to create too many SQL views.

This is called dimension role-playing. Role-playing in a data warehouse occurs when a single dimension simultaneously appears several times in the same fact table. The underlying dimension exists as a single physical table, but each of the roles should be presented in a separated labeled view.  

Another common example in the employee productivity reporting, is the date dimension. We will just need one single date dimension table, but very often, we need to have a second date, such as application date, besides the primary decision date, on the same fact table. Again we only need to create two different SQL views to handle this, with only one underlying physical date dimension table.

Introduction to Employee Compensation and Productivity Reporting

I came back to my old job, but am not working on exactly same projects. I am very happy that I have new puzzles to solve now.

Obviously employee compensation and productivity reporting has become a hot topic in the business recently.

I am going to blog on this subject as I get to understand more about the business needs.

After completing several ETL processes and reports so far, I gradually start to put all the puzzles together.

In its simplistic form, I can envision a subset of the data warehouse bus matrix. This matrix will revolve as I understand more.

The dimensions will involve hierarchies. All the facts basically are from a transactional table. But as we put together all the fact tables for reporting, we will get all three types of fact tables, i.e. the transactional fact tables, snapshot fact tables, and also accumulating fact tables. The third type will be the most common.

The natural granularity for our business transactional fact table will be one row for each action a team member does for each account. For the purpose of employee compensation and productivity reporting, the facts associated with the business process typically just include the counts of the actions, such as the counts all “declines”, or the counts of all “approvals”.

In the next blog I’ll talk about whether I need to normalize my fact tables by fact types.  

Define the grain of fact tables with unique key constraints

I have been working with SQL Server, and Integration Services and Reporting Services for years. Data from our data warehouse in DB2 are ETL’d to the SQL server to be ready to be consumed by SSRS report. Until recently, I’ve only been using unique key constrains on the fact tables sparsely. I’ve started to do so on all the fact tables, and dimension tables now.

Out of three basic fact tables, transactional fact table, fact table for periodic snapshots, and for accumulating snapshots, we very often deal with the first type of transactional fact table, which holds data at the most detailed level. Generally speaking, this type of fact table will have the most number of dimensions associated with it. Fortunately, it’s not that many, in the type of fact tables I deal with. The most common grain of the fact tables I deal with involves account number, transaction date, employee login code, type of product, type of actions (this can grow to a few sometimes). If it’s an accumulating fact table, several milestone dates can also be part of the grain.

OK, back to the creation of the unique key constraints. Most of the unique key constraints will just be a composite key that is composed of all the dimensions in a grain. The grain of a fact table represents the most atomic level by which the facts may be uniquely defined. There might be also some dimensions (such as employee’s location) that do not contribute to the uniqueness of the fact records. Do not include those dimensions in the unique composite key.