Using logical tables to create custom views
Logical tables are schema objects that are somehow similar to SQL views. They are not a representation of an existing table but a SELECT statement that returns a series of columns. There are cases in the course of a BI project when you need the data to be in a different shape than in the source tables, so you have two choices: modify the data warehouse or create logical tables.
Sometimes access to the data warehouse is simply out of the question, or it takes weeks, or the paperwork needed to approve the modification is just not worth it.
To be clear, whenever possible, I prefer changing the data warehouse over creating logical tables because there may be other applications that use that data and it's probably wise to have a common base for every app, but I resort to logical tables for cases like the one in this recipe when I need the data to be prefiltered for a specific purpose.
Getting ready
Look at the DimEmployee table in the database diagram. See that it has an arrow pointing to itself, what does that mean? Yes, it's the dreaded recursive relationship that OLTP designers like so much.
Think about a plane: it has wings, tail, fuselage, and more. Each part can be further divided into components: the wing has flaps, engines, and this goes on until the smallest pieces like bolts or wires. Each piece is used to build a bigger one, so it has a parent-child relationship to the part it composes.
Since it is not possible to create a table for every level of detail, OLTP applications use a single recursive table to model this situation, so that every record in that table has a predecessor, which in turn has another predecessor. And this is good, I mean, from the OLTP developer standpoint.
Here, in the DimEmployee table, we have EmployeeKey, which is the primary key and ParentEmployeeKey, which is a foreign key to the same table's primary key. Another example is the DimAccount table (AccountKey and ParentAccountKey).
MicroStrategy is not able to aggregate numbers with this type of dimension tables, so we need to unroll them somehow. In this recipe, we will create different logical tables for each employee level, and we do this because we know there is a finite number of levels in the company hierarchy.
In more complex cases like the parts of the plane, unfortunately, we don't know how many levels we would need to create beforehand, and hence this solution may not be applicable.
You can find the SELECT statement for this recipe in the companion code file.
How to do it...
Follow these steps to create a logical table:
- Go to the Schema Objects | Tables folder, right-click in the right pane and from the context menu select New | Logical Table.
- In the Table Editor, there is a big textbox that says Click here to type a SQL statement, click on it and paste this code:
select EmployeeKey, ParentEmployeeKey, SalesTerritoryKey, FirstName, LastName, DepartmentName from DimEmployee where ParentEmployeeKey is null - In the area below, you see an empty grid with Column object, Data Type. Click on the Add button to create an empty line.
- In the Column Object field, type the exact name of the first field that the SELECT returns,
EmployeeKey, and leave datatypeIntegeras default. - Click again on the Add button and do the same for the second field
ParentEmployeeKeyleaving the datatypeInteger. - Do the same for
SalesTerritorykey. - The remaining fields
FirstName,LastName, andDepartmentNameareNVarChar, so add them as before but change the datatype fromIntegertoNVarChar.
- When you have completed all the fields, click on Save and Close and type a table name, for example,
DimEmployeeLevel1. The definition is saved into the metadata. - Update the schema.
How it works...
MicroStrategy will store the SQL definition of the logical tables, the field names, and the datatype in the metadata. To the MicroStrategy developer, these will be like real tables with no difference from the standard ones.
In the DimEmployeeLevel1 logical table, we are selecting only the employees who have no boss (ParentEmployeeKey is null), that is, the CEO of the company; in the second level, we need to filter the employees whose boss belongs to the group of DimEmployeeLevel1.
Going down the levels is just a matter of nesting Matrioska-style subqueries: the employees of Level 3 have a boss in Level 2 and so on until Level 5. There is no Level 6 in this company.
If you've played around with the database, you may have noticed that the FactInternetSales table does not have a foreign key to the employee dimension, that's understandable as those are direct sales. The FactResellerSales table, on the other end, has an EmployeeKey column that relates to the employee dimension.
There's more...
It's worth noting that in the FactResellerSales table, we have records with a Level 3 EmployeeKey and records with a Level 4 EmployeeKey. This means that Mr. Jiang—North American Sales Manager—has sold some products, and the people reporting to him also have sold items. This complicates a little bit when you want to report, for example, the total of Mr. Jiang's group sales.
A common solution in these cases is to clone Mr. Jiang and all the Level 3 people who make sales, duplicating them in the Level 4 table. Using the same Level 3 primary key values in both Level 4 EmployeeKey and Level 4 ParentEmployeeKey, they would be treated as Level 4 employees reporting to themselves at Level 3.
Exercise 4
Now repeat this recipe steps to do the same for the lower levels, using the following SQL sentences:
