Similarly to the fact sheet data model, the BSC data model combines facts from various perspectives. In accordance with the strategic measures that we defined in the previous section, the BSC data model combines information from our sales, marketing, and human resources perspectives. To be specific, we store the following events in our data model's fact table:
The last event is related to the personal behavior analysis that we performed in the human resources perspective in Chapter 7, Human Resources. However, instead of using a data log from RescueTime
, we will use QlikView's own session and audit logs to evaluate how employees' use QlikView's applications.
It is also common to add events from the financial, working capital, and operations perspectives. In this example, as we are only measuring revenue in the financial perspective of the BSC, we use the more detailed sales perspective to calculate revenue growth. As this model has the potential to become quite large, we leave the other perspectives out until a new strategic measurement requires their inclusion.
Unlike the customer fact table data model, this one only contains discrete events; therefore, it is far simpler. We only use one calendar table that describes the exact date when each event occurs. The rest of the dimension tables include descriptive information about sales representatives, items, and customers.
As company information dashboards may be used to communicate strategy to the whole company and also to external stakeholders, we include the same regional settings island tables. We also include the option to create dynamic visualizations as we did in the customer fact table. Let's sum up our data model using the 7Ws table:
Dimensions | ||
---|---|---|
7Ws |
Fields |
Comments |
Who |
|
We first saw |
Who |
|
We first saw |
When |
|
We use only one common calendar, as all events are discrete. In the case that we have to manage accumulating snapshots and periodic snapshots in one data model, we use the customer fact table data model as an example. |
What |
|
We first saw |
What |
|
Like the customer fact sheet data model, we use this field to help us distinguish between the different events in a single fact table. |
Metrics | ||
7Ws |
Fields |
Comments |
How Much |
|
Although the sales quotation and sales invoice share many of the same concepts, we've elected to create a separate set of fields for each document. While this risks creating an extremely wide table, it makes for simpler metric expressions that don't necessarily require set analysis. |
How Much |
|
We count the number of employees that have been in training with this field. |
How Much |
|
We count the number of times an employee has used QlikView with this field. |
When we create a data model containing multiple perspectives, we take care to only add data that is necessary to calculate the required measurements. This is ultimately the best way to optimize the QlikView data model. Column-wise, we remove fields that are not used as a dimension, expression, or filter. Row-wise, we filter data that is not pertinent to the analysis. For example, we eliminate many of the fields that we created for our sales perspective, such as [Gross Sales USD]
and [Cost USD]
. We also reduce the number of rows in the fact table by only including QlikView sessions that pertain to cross-selling analysis.
In the following table, we confirm that all the facts are transactional and that they describe events that take place at a discrete moment in time:
Facts Fact type |
Sales invoices |
Sales credit memos |
Sales quotations |
Employee training |
Employee QlikView usage |
---|---|---|---|---|---|
Transactional |
X |
X |
X |
X |
X |
We also describe how each event is related to each dimension table. In our example data model, the facts that represent employee training and QlikView usage cannot be analyzed by customer or item:
Dimensions Events |
Month/Year |
Date |
Customer |
Sales Rep |
Item |
---|---|---|---|---|---|
Sales Invoices |
X |
X |
X |
X |
X |
Sales Credit Memos |
X |
X |
X |
X |
X |
Sales Quotations |
X |
X |
X |
X |
X |
Employee Training |
X |
X |
X | ||
Employee QlikView Usage |
X |
X |
X |
Now that we've reviewed our BSC data model, let's continue to design how we want to visualize a company's information dashboard based on the BSC method.