Project design is not just about creating a project in MicroStrategy architect; it involves several steps and thorough analysis, such as how data is stored in the data warehouse, what reports the user wants based on the data, and so on. The following are the steps involved in our project design process:
Once the user have business requirements documented, the user must create a fact qualifier matrix to identify the attributes, facts, and hierarchies, which are the building blocks of any logical data model.
An example of a fact qualifier is as follows:
A logical data model is created based on the source systems, and designed before defining a data warehouse. So, it's good for seeing which objects the users want and checking whether the objects are in the source systems. It represents the definition, characteristics, and relationships of the data. This graphical representation of information is easily understandable by business users too. A logical data model graphically represents the following concepts:
- Attributes: Provides a detailed description of the data
- Facts: Provides numerical information about the data
- Hierarchies: Provides relationships between data
Physical data warehouse design is based on the logical data model and represents the storage and retrieval of data from the data warehouse. Here, we determine the optimal schema design, which ensures reporting performance and maintenance. The key components of a physical data warehouse schema are columns and tables:
- Columns: These store attribute and fact data. The following are the three types of columns:
- ID column: Stores the ID for an attribute
- Description column: Stores text description of the attribute
- Fact column: Stores fact data
- Tables: Physical grouping of related data. The following are the types of tables:
- Lookup tables: Store information about attributes, such as IDs and descriptions:
- Relationship tables: Store information about the relationship between two or more attributes:
So, here the BusinessProduct table is a bridge table between Business Segment and Product table.
- Fact tables: Store factual data and the level of aggregation, which is defined based on the attributes of the fact table. They contain base fact columns or derived fact columns:
- Base fact: Stores the data at the lowest possible level of detail:
- Aggregate fact: Stores data at a higher or summarized level of detail:
- Base fact: Stores the data at the lowest possible level of detail:
The following diagram shows a simple data warehouse model for our chapter:
Once we have a solid foundation, a good logical and physical model design, you can move on to the creation of the actual project in MicroStrategy.
The steps to be followed for project creation are listed here.
In our chapter, we are going to use Oracle 11g, which we installed in Chapter 1, Getting Started with MicroStrategy. There are lots of best practices for integrating Oracle and MicroStrategy, such as:
- Enable use of temporary tables for intermediate result sets
- Enable SQL global optimization to reduce the number of SQL passes generated in MicroStrategy
- Instead of ANSI syntax, use Oracle join syntax
- Enable set operator optimization to combine multiple subqueries into a single subquery
- Use bitmapped indexing for performance and storage saving
- Allow Oracle to identify queries coming from MicroStrategy
- Create a single BIGFILE scratch tablespace for MSTR intermediate tables
Users can find more information using this link:
It is very important to use these best practice, because they help to improve performance by reducing execution time.
Here are the creation steps:
- Navigate to the MicroStrategy bin directory in Linux and type
./mstrconnectwiz. - Click on the Test button, enter the username and password, and click OK.
- Enter all the required information and click finish to create a data source:
The user can now connect to a data warehouse and everything a user creates will be automatically stored in the metadata repository. There are two ways to create a project in MicroStrategy. These are:
- Using the project creation assistant
- Using MicroStrategy Architect
- In MicroStrategy Desktop, click on Schema | Create new project.
- Click Create Project. On the new project screen, enter the project name and click OK:
- This creates the project, enabling the
Select tables from the Warehouse Catalogue option:
- Click on Select tables from the Warehouse Catalogue. This opens the Warehouse Database Instance created by the user in the previous step; select the primary database instance for the project from the dropdown:
- Click OK after database selection, move the tables from tables available to tables being used, and click Save and close in the top-left of the window:
- This enables the options to create facts and attributes:
- Click Create facts, which is a column that's going to be used to measure something. So, in our case, we are going to add a numeric column and tell MicroStrategy to use that column while performing numeric operations, such as sum, avg, and so on.
- In the fact creation wizard, click Next.
- Move the required column names from available to facts and click Next:
- Click Next | Finish, which saves facts and lets the user create attributes.
- Click Create Attributes and in the attribute creation wizard, click Next.
- Move the required attributes from available columns to attributes and click Next:
- Update the description column name, if needed, and click Next:
- Select the lookup tables that contain the description of each attribute and click Next:
- Define the attributes relationship based on the relationship created in the logical data model. The user only creates a relationship between attributes of the same hierarchy and never with other hierarchies. It is a direct relationship.
- Click Next | Finish:
- Click OK on the project creation assistant screen once all the steps are completed to see the project in MicroStrategy Desktop.
The user can create a project and schema objects that reside within the project. Architect provides a centralized interface where users can define the logical tables, attributes, hierarchies, facts, and metrics that are required during project creation. It usually automatically creates attributes and facts based on the data available in the selected data source, but sometimes the user will have to map it manually. Users can either use the project table view or hierarchy view:
- In MicroStrategy Desktop, click Schema | Create new project. Enter the information in the new project window and click OK. Click the arrow for Architect.
- Select database instance from the drop-down list. Click OK. MicroStrategy Architect opens and there the user can add attributes, facts, and hierarchies:
This is the final and ongoing step in the project design process. Over the life of the project, our physical or logical data models can change, so the user's reporting needs can change, which requires a change of schema objects. So this phase of the project life cycle is responsible for this maintenance and involves tasks such as:
- Creating new objects as needed
- Modifying existing objects
- Removing objects that are no longer needed
In the previous section, we learned that metadata components that represent the physical structure of the data warehouse are called schema objects. In the upcoming sections, we will learn in depth about these schema objects, such as attributes, facts and so on, and based on these schema objects, how we can build more complex objects such as metrics and filters, which are known as public objects.