79 terms

* Project Designer

MicroStrategy Architect
Enables you to create new projects and modify the structure of existing projects

Initially populate metadata
Create schema objects
Metadata Functions
Mapping tool allows object to communicate with DW (maps objects to schema objects in DW)

Stores all objects in metadata
Basic Schema objects

Interface directly with tables in DW
Basic Drilling options for reports
Relationships between attributes
Hierarchies and their drilling configuration
Types of Hierarchies
User Defined by user
System Defined by product
Project Design Process 4 Steps
Design Logical Data Model
Design the DW Schema
Create the Project in Architect
Manage the Project Schema in Architect
Design Logical Model Involves
- Determining information users want to see in reports
- Determine what information is available to include in reports
- Logical data model that incorporates the information users want to see and shows how it is related
Design Data Warehouse Schema Tasks
- Considering the advantages and disadvantages of various structures for storing data in the data warehouse
- Determine the optimal schema design that balances reporting requirements, performance requirements, and maintenance overheard
- Create the data warehouse using this schema design or modifying the existing data warehouse to use this schema design
Create Project in MicroStrategy Architect Tasks
Configuring the project metadata if necessary
Configure connectivity to the project metadata and DW
Create the necessary schema objects for the project based on the logical data model and mapping them to appropriate structures in the data warehouse schema
2 Tier
MicroStrategy Project connects to DW and Metadata databases
3 Tier
MicroStrategy Project connects to Intelligence Server which connects to DW and Metadata repositories

4 Tier includes Web
Logical Data Model
Depicts flow of data in an organization
Does not show physical structure of DW
Should include any information users want to see on reports
Logical Data Model Contains
Logical objects that relate aggregatable data stored in the DW to the MicroStrategy reporting environment. they are usually numeric and you can aggregate them to different levels depending on your report need. Form the basis for Metrics.
- Descriptive data that provide context for analyzing facts
- Provide levels for aggregating the qualifying fact data
- Attributes in the logical data model map to attribute schema objects in MicroStrategy projects
- Attributes describe metrics

- In logical data modeling attributes are not columns. They are logical categories that provide context for facts.
Attributes and Attribute Forms
Attributes in logical data models
Attribute Relationships
Essential to the structure of a logical data models
Enable you to join data from different attributes
Enable you to aggregate fact data to different levels
Attributes are Related in the following 2 ways
Direct - A parent-child relationship exists between two or more attributes. Direct relationships are explicitly defined using lookup tables or distinct relationship tables (think Month Date)

Indirect - Two or more attributes are related only through a fact or set of facts. Indirect relationships are stored only in fact tables (think Customer Date).
Direct Relationship Parts
Parent and Child
One to One
Each parent has only one child, and each child has only one parent

Customer to SSN
One to Many
Each parent can have more than one child, but each child has only one parent

Region to Store
Many to Many
Each parent can have more than one child, and each child can have more than one parent.

phone number to persons
Item to Color
Are groupings of directly related attributes ordered to reflect their relationships to provide drilling paths and browsing in the MicroStrategy reporting environment.

Organize attributes into hierarchies based on logical business areas

Contain directly related attributes
Can be related indirectly to other hierarchies
Logical data model must consider
- User Reporting Requirements
Only include information needed for reports
- Existing Source Data
- Technical and Performance Considerations
Creating Logical data model first in MicroStrategy
Facilitates the project creation process in MicroStrategy Architect
Helps identify potential problems with the structure or content of the data warehouse

You can create multiple logical data models from the same source data
Create Logical Data Model Steps
List all of the information from the source data you need to include in the logical data model based on the user reporting reqs
Identify Facts
Identify Attributes

page 62
Facts and Attributes in MicroStrategy Architect
Map to columns in physical schema
Column Types
ID Column Type
ID Columns are often numeric
ID Columns generally serve as the unique key for attributes
All attributes have an ID column
These columns store the text descriptions for attributes
Descriptions are optional for attributes
If an Attribute has multiple description columns
Columns that store fact data. They are usually numeric.
Table Keys
Every table has a primary key that identifies each distinct record in the table

Two types of primary keys
Simple - single column
Compund - multiple columns used to uniquely identify a record
Simple Keys
Require less storage space
Allow for simpler SQL
May require a more complex ETL process
Compound Keys
Require more storage space
Increase SQL complexity and query time
May enable a less complex ETL process
Relationship Tables
Store information about the relationship between tow or more attributes
Enable you to join data for related attributes
Contain the respective ID columns of each attribute

Do not need relationship table for:
One to One relationship
One to Many relationship

Need relationship table for
Many to Many
Fact Tables
Sstore fact data and attribute ID columns that describe the level at which the fact values are recorded

Attributes in a fact table comprise the fact table level
Fact Table Types
Base Fact Table: Table that stores a fact or set of facts at the lowest possible level of detail

Aggregate Fact Table: Table that stores a fact or sets of facts at a higher, or summarized, level of detail. Reduces query times and can have multiple fact tables for same base fact
Normalized vs Denormalized Schemas
A basic characteristic of any physical schema is its degree of normalization or denormalization

Normilization does not store data redundantly

Denormalization data is stored redundantly

Denormalize table for performance purposes to reduce the number of joins between tables for queries
Reporting Requirements Schema Design
Normalize data when users query infrequently

Denormalize data that users query frequently
Query Performance
Structure of your data warehouse schema should take into account what type of query performance you need to deliver to end users. Detail queries degrade performance

Denormalizing tables can speed query performance
Data Volume
Data structure should take into account the volume of data you store for each attribute in each hierarchy

Attributes and hierarchies with greater data volumes are better candidates for denormalization

You can often use a more normalized schema design for attributes and hierarchies with low data volume
Database Maintenance
The structure of DW schema should take into account the effor involved in maintaing the schema design you choose

Designs that proved more flexibility ofter require more work to maintain

Denormalized tables require more maintenance
Create Project steps
1 Configure project metadata
2 Configure project connectivity
3 Create the necessary schema objects using the project creation workflow
4 Updating the project schema
MicroStrategy Architect is used to initially populate
Project definitions and parameters
Schema Objects
List the 3 components a Logical Data Model must include
Developing a logical data model involves 4 Tasks
1. Communicate with end users to determine the types of questions they need to ask and what they need to analyze
2. Working with users to document their reporting requirements
3. Evaluating whether you can readily support these reporting requirements given the existing source data and environmental characteristics
4. Incorporate user reporting requirements into the logical data model design
What is a physical schema
A physical schema is a detailed graphical representation of the physical structure of a database. DW physical schema shows how your business data is stored.

Shows how underlying data for logical data model objects are stored

Includes Columns and Tables
Completely Normalized schema
A completely normalized schema does not store any data redundantly.

Requires more joins between tables, depending on the level at which you query the data
Moderately Denormalized schema
Stores some data redundantly.

Reduces the number of joins required between tables
Completely Denormalized schema
stores the maximum amount of data redundantly.

Further reduces number of joins required between tables

Lowest level lookup tables contain highest level lookup information within the hierarchy

Referred to as a Star Schema since higher level tables can be removed
Name three schema types
Completely Normalized
Moderately Denormalized
Completely Denormalized
Completely Normalized Schema Lookup Table Structure
Contains an attribute's ID and description columns as well as the ID column of its immediate parent
Completely Normalized Schema Advantages
Requires minimal storage space and no data redundancy
Completely Normalized Schema Disadvantages
Requires more joins for queries on higher level attributes
Moderately Denormalized Schema Lookup Table Structure
Contains an attribute's ID and Description columns as well as the columns of all related higher-level attributes
Moderately Denormalized Schema Advantages
Significantly reduces the number of joins necessary for queries on higher-level attributes
Moderately Denormalized Schema Disadvantages
Requires slightly more storage space and some data redundancy
Completely Denormalized Schema Lookup Table Structure
Contains an attribute's ID and description columns as well as the ID and description columns of all related higher-level attributes
Completely Denormalized Schema Advantages
Further reduces the number of joins necessary for queries on higher level attributes
Completely Denormalized Schema Disadvantages
Requires significantly more storage space and the greatest degree of data redundancy
When creating a DW schema what are four factors to take into consideration?
1. User reporting requirements
2. Query performance
3. Data volume
4. Database maintenance
User Reporting Requirements schema factors
Understanding the information users need on reports helps you determine the query profile (types of queries users will execute and how often they will execute them)
Query Performance schema factors
Granular detail level queries involve accessing a greater volume of data which can degrade performance. Denormalizing tables can often help speed query performance by reducing joins
Data Volume schema factors
Attributes and hierarchies with greater data volumes are often better candidates for denormalization. Normalized works best with lower data volumes
Database Maintenance schema factors
Designs that provide greater flexibility in terms of query performance and detail also require more work to maintain. The more you denormalize the more work you have to maintain the table due to redundancy. Can cause increased complexity in the ETL process
Metadata Shell
Relational database structure that contains the project objects in MicroStrategy
Steps to configure a new project metadata
1. Create empty metadata database
2. Create a data source name (DSN) to connect to the metadata database
3. Create the metadata shell
Create DSN with:
MicroStrategy Connectivity Wizard
ODBC Data Source Administrator
Where is the MicroStrategy Connectivity Wizard located?
Programs -> MicroStrategy -> Tools -> Connectivity Wizard
Create Metadata Shell
Programs -> MicroStrategy -> Configuration Wizard
Configure Project Connectivity Steps
1 Create project source
2 Create database instance including database connection and database login
Project source connects to metadata in which two ways?
Direct - 2 Tier mode specifying DSN, login, password

Server - 3 Tier mode selecting intillegence server that governs and validates the db connection
MicroStrategy Desktop Project Source Manager does what
Creates a Project Source
When a project is created a Database Instance must be created to connect to the DW
Database Instance consists of a database connection which in turn uses a specific DSN and db login

Use Database Instances manager
Project Creation Workflow (4 Steps)
Add Project Tables
Create Facts
Create Attributes and Relationships
Create User Hierarchies

Use Project Creation Assistant (Schema -> Create New Project)
Schema Object Editors
Attribute Editor
Fact Editor
Hierarchy Editor
Logical Table Editor
Architect Graphical Interface works with what 4 objects
User hierarchies
Logical Tables
Store DDL for Physical Tables and schema objects associated with the table columns. The Engine uses logical tables to generate SQL.
Warehouse Catalog
Located in Schema menu and is used to select tables for an existing project. Also accessed via Project Creation Assistant.