* Project Designer

79 terms by jontbrooks

Create a new folder

Advertisement Upgrade to remove ads

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

tables
facts
attributes
hierarchies

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

Facts
Attributes
Hierarchies

Facts

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.

Attributes

- 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

Hierarchies

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
Description
Fact

ID Column Type

ID Columns are often numeric
ID Columns generally serve as the unique key for attributes
All attributes have an ID column

Description

These columns store the text descriptions for attributes
Descriptions are optional for attributes
If an Attribute has multiple description columns

Facts

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

Facts
Attributes
Hierarchies

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
or
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

Tables
Facts
Attributes
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.

Please allow access to your computer’s microphone to use Voice Recording.

Having trouble? Click here for help.

We can’t access your microphone!

Click the icon above to update your browser permissions above and try again

Example:

Reload the page to try again!

Reload

Press Cmd-0 to reset your zoom

Press Ctrl-0 to reset your zoom

It looks like your browser might be zoomed in or out. Your browser needs to be zoomed to a normal size to record audio.

Please upgrade Flash or install Chrome
to use Voice Recording.

For more help, see our troubleshooting page.

Your microphone is muted

For help fixing this issue, see this FAQ.

Star this term

You can study starred terms together

NEW! Voice Recording

Create Set