Design of facts, attributes and hierarchy of dimensions in Microstrategy 9.

After configuring the server and created the project (as we saw in the previous entry in the blog ), we proceed to implement the logical model of our Data Warehouse within Microstrategy metadata schema.

This task is essential to start working with our BI tool. Is the starting point to begin preparing the various elements that form our system of business intelligence (reports, dashboards, analysis, etc).

Logical model of Microstrategy metadata

The tasks to do in this step is the definition of the attributes of the dimensions, the relationship between them (hierarchical organization), as well as relevant business indicators in our organization.Here we establish the relationship between logical and physical level equivalents (tables and fields in the database).

I recommend you view the video produced by Microstrategy to see a real example of the definition of these elements.

Making mention of the theory of dimensional model we saw earlier:

  • Acts / indicators: they are the business values ​​by which we will want to analyze our organization (sales amount, margin, profitability).
  • Size: perspectives or different fields for which we will want to analyze these business indicators (those which give meaning to the analysis of business indicators, for without dimensions are not worth more than most). You can answer factual questions and give them a context of analysis.In our model, the dimensions will be the time (provided there is usually a time dimension), customer, product, promotion and logistics.

Asistente creación de proyectos

To set this up within Microstrategy, we will have three main tasks:

1) Selecting Warehouse catalog tables: all the tables we will have in the database of the Data Warehouse, select which ones to go to work.The selected tables and fields will determine the items available for the remaining steps.

2) Creation of facts: selected tables in the previous step, indicate which fields are those corresponding to the facts.These fields will be the basis for creating metrics that they will use in reports, documents and analysis. These metrics, based on the basis of the facts, may include operations, load one or more fields, and the use of complex functions (Microstrategy includes a large number of functions to perform complex calculations on data, including statistical functions). This will allow us to have values ​​that are calculated and are not really stored in the database.

3) Creation of attributes: In this step, the same way, select the fields that correspond to attributes and perform basic setup of them, such as descriptions, management, assignment codes descriptions (lookups) as well as setting hierarchical structure of the different components of a dimension (through parent-child relationships).

To accomplish these tasks, use the wizard to create projects that will lead us in an orderly manner at all steps necessary to complete these tasks.The wizard is only used when creating the project and subsequent maintenance processes of the tables, attributes and made changes since the development tool Microstrategy Architect or from the Desktop.

Let's look a little more in depth each of these steps:

Selecting Warehouse catalog table

This step will tell the database that corresponds to the Data Warehouse, and the catalog to indicate, we identified all the tables appear at the physical level.From these tables, select those that are relevant to our model (as we see in the picture).

DW Selection Tables

MicroStrategy enables us to work with the same table several times through the "alias" table. It can be useful when the same physical dimension is used logically in several places (and do not need to have a physical table for each of the dimensions).Likewise, it also allows us to work towards.

Building Facts

Fields of the tables shown in the previous section, this step will select which ones are those that consider Acts. In principle, though you can configure only taken for this purpose the fields that are defined as numeric.

Fact Column Selection

Creating Attributes

The creation of attributes is a bit more complex and has associated several steps.First of all table fields, select which ones correspond to our attributes. In the event that an attribute is associated with a field identifier and a description field, just select the ID field (since then draw up the relationship between source fields and description fields or lookup).

Attribute creation

Then, for each of the attributes, indicate your lookup field.When a country does not have this (as the Postal Code field, which describes itself), indicate "Use ID as description."

As a last step in the creation of attributes for each of them indicate that the attributes are children (they are later in the hierarchy of the dimension) or what father (they are up in the tree). This step is omitted, then we will do this again with the graphical tool Architect , which is much more agile to make that definition.

Maintenance of the model using Microstrategy Architect

The Architect, together with the Desktop, the primary tool of development in Microstrategy.Using this last tool we could equally well have made the definition of events and attributes (step selection boards should also have made a previous step).

The architect is a graphical tool and is used to perform maintenance tasks in MS. In our case, we did not define the attributes and hierarchies have completed using this tool. This is as simple as selecting the parent attribute and dragging the child attribute for the creation of the relationship. Then select the connector to change the type of relationship between the components (1 an, 1 to 1, etc).

Definition of attribute hierarchies Architect

In the picture, you can see how we have defined the hierarchical structure within the Customer dimension.Also, from here we can create new attributes, new facts or change the properties of these (on the right is the property table). Selecting the item in this section, we listed all the information as configured.

Microstrategy Tutorial

In addition to the project that we have created, using the Desktop Tool observe that there is created a sample project, called MicroStrategy Tutorial.This project works with databases Access test and contains examples of all elements that can be defined and used in Microstrategy. It can be a starting point for learning to work with the various components.

Also, when installing, we have created a Documents folder, where we have a lot of PDF files with full manual all Microstrategy tools such as:

  • Microstrategy Evaluation Guide
  • Installation and Configuration Guide.
  • System Administration Guide.
  • Project Design Guide.
  • Basic Reporting Guide.
  • Advanced Reporting Guide.
  • Report Services Document Creation Guide.

Conclusions

After performing these tasks, the logical model of our project is deployed and configured within Microstrategy 9, and we can start using the tool.

One of the strengths that we observed in the tool is that everything is centralized in the same applications, and from them we will all development tasks, from system configuration, model creation, as well as the creation of the components used users.The product appears to be compact and consistent, this is the first impression it conveys.

Similarly, Microstrategy provides a wealth of documentation, samples, video tutorials, etc. to make it easier to start working with the tool and find information on the different elements that comprise it.To be included in the system, a Web course tool evaluation tests.