MicroStrategy Architect and Project Design
- Overview of MicroStrategy Architect and Schema Objects
- Roles of MicroStrategy Architect and its importance
- Building Logical table design and Warehouse catalogue
- Architect Interface
- Creating Project Tables, Facts, Attributes and its relationships and hierarchies
Overview of MicroStrategy Architect and Schema Objects
MicroStrategy Architect provides a set of tools that are used to create new projects and modify the structure of existing projects.
It enables to perform the following tasks:
- Initially populate the metadata
- Create schema objects
The role of the metadata in generating report and document results
Any time you create, modify, or remove any type of project object, those changes are stored in the metadata.
Creating Schema Objects
The most fundamental objects stored in the metadata are schema objects. The basic schema objects are:
Roles of MicroStrategy Architect and its Importance
MicroStrategy Architect supports the following project functions :
- Reporting
- Drilling
- Browsing
MicroStrategy Architect and Reporting
Attributes and metrics are two of the most frequently used objects in designing reports. Metrics consist of facts, which are also schema objects.
MicroStrategy Architect and Drilling
The basic drilling options available for a report directly relate to the definition of following items in MicroStrategy Architect:
- Relationships between attributes
- Hierarchies and their drilling configuration
MicroStrategy Architect and Browsing
MicroStrategy Architect is used to create and define hierarchies, which determine the various paths for browsing attributes.
Building Logical table design and Warehouse catalogue
Logical Data Model
The first step in project design process is to design the logical data model. Logical Data Model is a diagram that shows what information is to be analyzed in a project and seen in reports and how that information is related. It depicts the flow of data in an organization but does not show the physical structure of how the information is stored in the data warehouse.
Simple Logical Data Model
Logical Data Model components
A logical data model includes the following three components :
- Facts
- Attributes
- Hierarchies
Facts
Facts are measures that are used to analyze business. Fact data is typically numeric, and it is generally aggregated. In MicroStrategy projects, facts map to fact schema objects, which form the basis for all the metrics you use in reports. The other components of a logical data model provide context for the facts.
Attributes
Attributes are descriptive data that provide context for analyzing facts. Attributes provide levels for aggregating and qualifying fact data. They map to attribute schema objects, which describe the metrics on reports.
Attribute Relationships
They help to join data from different attributes and aggregating fact data to different levels. Attributes are related to each other in 2 ways :
• Direct - A parent-child relationship exists between two or more attributes.
• Indirect - Two or more attributes are related only through a fact or set of facts.
Types of Direct Relationships
Hierarchies
Hierarchies are groupings of directly related attributes ordered to reflect their relationships. Hierarchies contain only attributes that are directly related to each other. Attributes in one hierarchy are indirectly related to attributes in other hierarchies.
Structure of a Logical Data Model
When all components - facts, attributes and their relationships, and hierarchies are put together we have a logical data model.
Creating a Logical Data Model
Factors that influence the design of the logical data model :
- User reporting requirements
- Existing source data
- Technical and performance considerations
User reporting requirements
The Logical data model should take into account the reporting requirements of end users. It should include all information needed for reports and must not include any information captured in source systems that is not needed for reports.
Existing Source Data
The Logical data model should take into account what source data is available for use. It should be ensured through an initial review of source systems that sufficient source data is present to support user reporting requirements.
Technical and Performance Considerations
Many technical and performance factors affect the design of logical data model, mostly with regard to its size and complexity. Technical factors are Robustness of database server and software, Network bandwidth and Volume of concurrent users. Complex user reporting requirements or source data structures pose greater challenges to delivering optimal performance.
Steps to Create a Logical Data Model
Factors that influence the design of the logical data model :
- List all the information from the source data needed to include in the logical data model
- Identify which items are facts
- Identify which items are attributes
- Determine the direct relationships between attributes
- Organize directly related attributes into hierarchies
Introduction to Physical Schema
The second step in the project design process is to design the data warehouse schema.
A physical schema is a detailed, graphical representation of the physical structure of a database. The physical schema is designed based on the organization of the logical data model. While the logical data model shows the facts and attributes, the physical schema shows how the underlying data for these objects is stored in the data warehouse.
Physical Schema Components
A physical schema includes the following two primary components:
Column Types
In a data warehouse, the columns in tables store fact or attribute data. The following are the three types of columns:
- ID Columns
- Description Columns
- Fact Columns
Table Keys
Every table has a primary key that consists of a unique value that identifies each distinct record (or row) in the table. There are two types of primary keys :
Lookup Tables
Lookup tables store information about attributes, including their IDs and any descriptions. They enable you to easily browse attribute data. Depending on the design of physical schema, a lookup table can store information for a single attribute or multiple related attributes.
Relationship Tables
Relationship tables store information about the relationship between two or more attributes. They enable you to join data for related attributes. To map the relationship between two or more attributes, their respective ID columns must exist together in a relationship table.
One-to-One Relationship
For one-to-one relationship, there is no separate relationship table and separate lookup table for the parent attribute. Instead, the parent is directly placed in the lookup table of the child attribute to map the relationship between the two attributes.
One-to-Many Relationship
For a one-to-many relationship, there is no need to have a separate relationship table. Instead, the parent-child relationship can be defined by including the ID column for the parent attribute in the lookup table of the child attribute.
Many-to-Many Relationship
For a many-to-many relationship, a separate relationship table with the IDs of parent and child attributes is created to map the parent-child relationship.
Fact Tables
Fact tables store fact data and attribute ID columns that describe the level at which the fact values are recorded. They enable to analyze fact data with regard to the business dimensions or hierarchies those attributes represent.
Base and Aggregate Fact Tables
Base fact tables are tables that store a fact or set of facts at the lowest possible level of detail. Aggregate fact tables are tables that store a fact or set of facts at a higher, or summarized, level of detail.
Schema Types
The type of schema design for the data warehouse depends on the nature of the data, how users want to query the data and other factors unique to the project and database environments. Commonly used schema designs :
- Completely normalized schema
- Moderately denormalized schema
- Completely denormalized schema
Normalized Versus Denormalized Schemas
Normalization occurs whenever there is a schema design that does not store data redundantly. Denormalization occurs whenever there is a schema design that stores at least some data multiple times, or redundantly.
Completely Normalized Schema
A completely normalized schema does not store any data redundantly.
Moderately Denormalized Schema
A moderately denormalized schema stores some data redundantly.
Completely Denormalized Schema
A completely denormalized schema stores the maximum amount of data redundantly.
Creating a Data Warehouse Schema
The following factors influence the design of the schema :
- User reporting requirements
- Query performance
- Data volume
- Database maintenance
Architect Interface
Introduction to Architect
Overview of Architect Components
The Architect graphical interface consists of several components that combine most of the functions of the Project Creation Assistant with most of the functions of the schema object editors.
The Architect graphical interface has the following components:
- Warehouse Tables pane
- Project Tables View tab
- Hierarchy View tab
- Properties pane
- Project objects pane
- Menu bar
- Toolbar
Architect Interface
Architect Graphical Interface
Warehouse Tables Pane
The Warehouse Tables pane enables to view database instances and their associated tables and select the tables to be included in a project. The Warehouse Tables pane also displays only database instances that have been associated to the project.
Project Tables View Tab
The Project Tables View tab displays images of the tables used in a project. It uses layers to display the tables. The All Project Tables layer enables to view all the tables used in a project. This layer exists by default when a project is created.
Hierarchy View Tab
The Hierarchy View tab displays all of the attributes that have been created in a project. This tab is used to create, modify, and remove relationships between attributes, which builds the system hierarchy. It can also be used to create, modify, and remove user hierarchies.
Properties Pane
The Properties pane enables to view and modify the properties for attributes, facts, and tables. It has three tabs-Attributes, Facts, and Tables.
Architect Interface
Project Objects Pane
The Project objects pane enables to view the number of attributes, facts, and tables that have been created in a project. It also shows the project name and the current user.
Subscribe