Practice Project - Tutorial Microstrategy

INTRODUCTION This practice project is designed to test your ability to configure and deploy a basic MicroStrategy business intelligence environment. You will use your knowledge of MicroStrategy Architect™ and MicroStrategy Desktop™ to create a project and design reports that run against the MicroStrategy Tutorial™ data warehouse. This project will assist you in preparing for specific sections of the MicroStrategy Certified Developer (MCD) Certification. However, it does not provide practice on every subject matter that is required to successfully complete the MCD Certification. To achieve MCD Certification, you also need to master the knowledge attained through the Project Designer, Report Developer, and Platform Administrator focus area certifications. The Project Design section of this document contains requirements and detailed instructions on how you satisfy the requirements. We recommend that you try to complete the entire project using only the requirements. Use the detailed instructions only if you need assistance. If you need to use the detailed instructions to complete a task, we recommend that you redo the task until you no longer need assistance. Remember, this is a practice project. The actual MCD Certification will not include step-by-step instructions. When to Complete This Project This project contains information that is covered in the Certified Project Designer certification and the Certified Report Developer certification. The Certified Project Designer certification contains the MicroStrategy Architect: Project Design course and the MicroStrategy Desktop: Reporting Essentials course and is the first week of the MCD Program. The Certified Report Developer certification contains the MicroStrategy Report Services: Document Essentials course and the MicroStrategy Desktop: Advanced Reporting course and is the second week of the MCD Program. MicroStrategy Education recommends that you attempt this practice project after completing the Certified Report Developer certification. If you are completing the entire 3.5 week MCD Program at one time you should attempt this project after the finishing the second week.

MicroStrategy MCD Practice Project – MicroStrategy Tutorial Revised: 10/8/2009 © 2009 MicroStrategy, Inc. All Rights Reserved

Page 1 of 38

Project Requirements: The following components installed on your computer: o

MicroStrategy Architect 9.0.0 or higher

o

MicroStrategy Desktop Designer 9.0.0 or higher Note: Refer to the MicroStrategy ReadMe file for minimum system requirements and installation instructions.

o

Microsoft® Access 2000 or higher

o

Microsoft Access ODBC driver

Access to the MicroStrategy Tutorial data warehouse provided in the MicroStrategy Practice Project, Tutorial Project Online course (Practice Tutorial_WH). Note: The result checks in the report development phase of this document are based on the included Practice Tutorial_WH. If you do not use the included Practice Tutorial_WH, your data model, schema, and report results may differ from the data model, schema, and report results contained in this document. A MicroStrategy user account that has all necessary privileges to create projects, attributes, facts, metrics, filters, and reports in Desktop Note: Remember that this project is not comprehensive. Completing this project does not guarantee that you will pass the MCD Certification.

Project Guidelines: You may use your course manuals, MicroStrategy product manuals, MicroStrategy online help, and the MicroStrategy Knowledge Base for assistance in completing the project. Note: You need a login to access the MicroStrategy Knowledge Base. Try to complete the entire project by yourself. (During the MCD Certification, you will not be able to discuss the project with fellow students.) Try to complete the entire project without using the detailed instructions. (The MCD Certification does not contain instructions on how to complete a task.)


Page 2 of 38

This practice project is not comprehensive and does not cover all topics that are required to successfully complete the actual MCD project. In addition, this practice project contains step-by-step directions. The MCD project does not contain step-by step instruction. Try to complete this project in 5 hours or less. Remember, this is a practice project. It does not include all topics that are covered on the actual MCD.

PROJECT DESIGN Although this project assumes that you have already installed Architect and Desktop Designer, we recommend that you understand how to install specific MicroStrategy products. This phase of the project covers concepts from the MicroStrategy Architect: Project Design and the MicroStrategy Desktop: Reporting Essentials courses. After completing this phase of the project, you will be ready to begin report development. This practice project is not comprehensive and does not cover all topics that are required to successfully complete the actual MCD project. In addition, this practice project contains step-by-step directions. The MCD project does not contain step-by step instruction.


Page 3 of 38

Metadata and Data Warehouse Before you can begin project creation, you need to create your MicroStrategy metadata database and establish connections to both the metadata database and the data warehouse. The following pages list the steps to create the metadata database and connect to the metadata database and the data warehouse. 1. Create the Metadata Database Create an empty database in Microsoft Access. Name the database Practice_Tutorial_MD. How to create an empty database in Microsoft Access:

1. On the Microsoft® Windows® Start menu, point to Programs, followed by Microsoft Office, and finally, select Microsoft Office Access 2003. If you have Microsoft Access 2000 installed on your computer, on the Programs menu, select the Microsoft Access. 2. Do one of the following: If you are using Microsoft Access 2007, in the Microsoft Access window, select Blank database If you are using Microsoft Access 2003, on the File menu, select New. On the New File panel, click Blank database. OR If you are using Microsoft Access 2000, in the Microsoft Access window, select Blank Access database. Click OK. 3. In the File New Database window, browse to C:\Program Files\MicroStrategy. If the MicroStrategy folder does not exist, browse to any accessible location. Remember where you save your metadata database. 4. In the File name box, rename the database to Practice_Tutorial_MD.mdb. 5. Click Create. MicroStrategy MCD Practice Project – MicroStrategy Tutorial Revised: 10/8/2009 © 2009 MicroStrategy, Inc. All Rights Reserved

Page 4 of 38

6. Close Microsoft Access.

2. Create the Metadata DSN Create a DSN that points to the Practice_Tutorial_MD database you created in the previous step. Name the DSN Practice_Tutorial_MD_DSN. How to create a DSN to connect to the metadata database:

1. On the Windows Start Menu, point to Settings, and select Control Panel. 2. In the Control Panel window, double-click Administrative Tools. 3. In the Administrative Tools window, double-click Data Sources (ODBC). 4. In the ODBC Data Source Administrator, select the System DSN tab. 5. On the System DSN tab, click Add. 6. In the Create New Data Source window, select Microsoft Access Driver (*.mdb). 7. Click Finish. 8. In the ODBC Microsoft Access Setup window, in the Data Source Name box, enter Practice_Tutorial_MD_DSN. 9. Under Database, click Select. 10. In the Select Database window, browse to C:\Program Files\MicroStrategy or the location in which you saved your metadata database. 11. Select Practice_Tutorial_MD.mdb. 12. Click OK. In the ODBC Microsoft Access Setup window, on the System DSN tab, you should see your new metadata DSN. 13. In the ODBC Microsoft Access Setup window, click OK. 14. In the ODBC Data Source Administrator, click OK. 15. Close the Administrative Tools window. MicroStrategy MCD Practice Project – MicroStrategy Tutorial Revised: 10/8/2009 © 2009 MicroStrategy, Inc. All Rights Reserved

Page 5 of 38

3. Create a 2-tier Project Source Create a 2-tier project source that points to your metadata database. Name the project source Tutorial Practice Project Source. How to create a 2-tier project source:

Before you create the project source, you must create the MicroStrategy metadata shell. You can complete both steps using the MicroStrategy Configuration Wizard. 1. On the Windows Start Menu, point to Programs, followed by MicroStrategy, and finally, select Configuration Wizard. 2. In the Configuration Wizard, select the Metadata Repository and Statistics Tables option. 3. Click Next. 4. On the ensuing window, verify the following settings: Create Metadata Tables check box is selected. Create Statistics Tables and Create History List Tables check boxes are cleared. o While a significant part of a long-term successful business intelligence solution, statistics and history list tables are beyond the scope of this project. 5. Click Next. 6. In the DSN drop-down list, choose Practice_Tutorial_DSN. 7. Click Next. 8. Click Finish. o Wait while the configuration wizard configures the metadata database. 9. Click Close to return to the Welcome screen of the wizard. 10. Select Project Sources. MicroStrategy MCD Practice Project – MicroStrategy Tutorial Revised: 10/8/2009 © 2009 MicroStrategy, Inc. All Rights Reserved

Page 6 of 38

11. Click Next. 12. For Project Source Name, enter Practice Tutorial Project Source. Select Direct (2-tier). 13. Click Next. 14. In the DSN drop-down list, choose Practice_Tutorial_DSN. 15. Select Use id and password entered by user (Standard Authentication). 16. Click Next. 17. Confirm that the summary information is correct. 18. Click Finish. 19. When notified that the configuration was successful, click Close. 20. Click Exit to close the Configuration Wizard.

4. Download the Data Warehouse This project uses the Practice Tutorial_WH that is included with the MCD Project Practice, Tutorial Project online companion course. You can find the Practice Tutorial_WH on the Supporting Documents page. Complete the following steps to download the Practice Tutorial_WH.

1. Click the icon labeled Practice Tutorial WH. 2. Save the database to C:\Program Files\MicroStrategy. 3. Right-click the Practice_Tutorial_HW.zip file, select Winzip and then Extract to here.

5. Connect to the Data Warehouse To connect to the MicroStrategy Tutorial data warehouse, you need to create another DSN. Follow the same steps you used to create the metadata DSN,


Page 7 of 38

except name this DSN Practice_Tutorial_WH_DSN and select the Practice Tutorial_WH.mdb database instead of the Practice_Tutorial_MD.mdb database.

6. Create a Database Instance Create a database instance in your Practice Tutorial Project Source to connect to the MicroStrategy Tutorial data warehouse. Name the database instance Practice Tutorial DBInstance. Name the database connection Practice Tutorial Connection. Name the database login Practice Tutorial Login. How to create a database instance to establish a connection between Desktop to and the MicroStrategy Tutorial data warehouse:

1. On the Windows Start Menu, point to Programs, followed by MicroStrategy, then Desktop, and finally, select Desktop. 2. In Desktop, in the Login window, click Cancel. 3. In the Folder List, expand the Practice Tutorial Project Source 2-tier project source you created earlier. 4. In the Login window, for Login id, enter Administrator. 5. Leave the Password field blank. 6. Click OK. 7. When informed that no projects were returned by your project source, click OK. Note: You have not yet created any MicroStrategy projects in this project source. 8. Expand the Administration icon. 9. Expand Configuration Managers. 10. In the Folder List, select Database Instances. 11. On the File menu, point to New, and then select Database Instance MicroStrategy MCD Practice Project – MicroStrategy Tutorial Revised: 10/8/2009 © 2009 MicroStrategy, Inc. All Rights Reserved

Page 8 of 38

12. In the Database Instance window, for Database instance name, enter Practice Tutorial DBInstance. 13. For Database connection type, select Microsoft Access 2000/2002/2003. 14. Under Database connection, click New. 15. In the Database Connection window, under Database connection name, rename the database connection to Practice Tutorial Connection. 16. Under Local system ODBC data sources, select Practice_Tutorial_WH_DSN. 17. Under Default database login name, click New. 18. In the Database Logins window, under Database login, rename the database login to Practice Tutorial Login. 19. For Login ID, enter sa. 20. Leave the Password field blank. 21. Click OK. While Access does not require a login or password, Architect requires a database login. 22. In the Database Connections window, under Default database login name, ensure that your new Practice Tutorial Login database login is selected. 23. Click OK. 24. In the Database Instances window, under Database connection (default), ensure that your new Practice Tutorial Connection database connection is selected. 25. Click OK. This completes the definition of your database instance. The information you just created is encrypted and saved to the metadata. Project Creation Now that you have configured your metadata and set up the connections to the metadata and data warehouse, you are ready to create your MicroStrategy project using Architect.


Page 9 of 38

You will be responsible for creating the entire project from beginning to end. You may use the Project Creation Assistant or individual wizards and editors to create your project. Name your project Tutorial Practice Project. Note: This document provides steps on how to create a project using the Project Creation Assistant. Be aware that you can create an incomplete project definition, save it, and complete it later. However, if you exit the Project Creation Assistant early, you cannot use it again to complete the project definition. As such, it is strongly recommended that you create your initial project definition in one sitting. Note: This practice project is not comprehensive and does not cover all topics that are required to successfully complete the actual MCD project. In addition, this practice project contains step-by-step directions. The MCD project does not contain step-by step instruction.

1. Initialize the Project Create a new project inside of the Practice Tutorial Project Source. Name the project Practice Tutorial Project. How to create a new project using the Project Creation Assistant:

1. On the Schema menu, select Create New Project. 2. In the Project Creation Assistant, to the left of Create project, click >. 3. In the New Project window, name the project Practice Tutorial Project. 4. Click OK. Architect now populates the metadata tables with initial project data. When project initialization is complete, the Project Creation Assistant returns with a green check mark next to the Create project step. 2. Select Warehouse Tables Using the Schema in Appendix B, select the tables to be used in the project. Be sure to include all tables diagramed in Appendix B. How to select tables from the Warehouse Catalog:

1. In the Project Creation Assistant, to the left of Select tables from the Warehouse Catalog, click >. MicroStrategy MCD Practice Project – MicroStrategy Tutorial Revised: 10/8/2009 © 2009 MicroStrategy, Inc. All Rights Reserved

Page 10 of 38

2. In the Database Instance window, select Practice Tutorial DBInstance. You can also edit an existing database instance or create a database instance if you did not do so previously. 3. Click OK. 4. In the Warehouse Catalog, from the Tables available in the warehouse list, select all 50 tables listed below (continued on the next page) and click > to add them to the Tables being used in the project list. You can use the CTRL or SHIFT keys to select multiple tables at once. Tables to be included in the Practice Tutorial Project

LU_BRAND

LU_PROMO_TYPE

SUBCATEG_MNTH_CTR_SLS

LU_CALL_CTR

LU_PROMOTION

STATE_SUBCATEG_MNTH_SLS

LU_CATALOG

LU_PYMT_TYPE

STATE_SUBCATEG_REGION_SLS

LU_CATEGORY

LU_QUARTER

ITEM_MTNH_SLS

LU_COUNTRY

LU_REGION

ITEM_EMP_SLS

LU_CUST_CITY

LU_SHIPPER

DAY_CTR_SLS

LU_CUST_REGION

LU_SUBCATEG

CITY_CTR_SLS

LU_CUST_STATE

LU_SUPPLIER

CITY_MNTH_SLS

LU_CUSTOMER

LU_YEAR

CITY_SUBCATEG_SLS

LU_DAY

INVENTORY_ORDERS

QTR_CATEGORY_SLS

LU_DIST_CTR

ORDER_DETAIL

MNTH_CATEGORY_SLS

LU_EMPLOYEE

ORDER_FACT

YR_CATEGORY_SLS

LU_INCOME

PROMOTIONS

INVENTORY_CURR

LU_ITEM

RUSH_ORDER

YTD_DAY


Page 11 of 38

LU_MANAGER

REL_CAT_ITEM

QTD_DAY

LU_MONTH

CUSTOMER_SLS

MTD_DAY

LU_MONTH_OF_YEAR

STATE_REGION_MNTH_SLS

5. After you have added all the tables to the project, click Save and Close. The Project Creation Assistant returns with a green check mark next to the Select tables from the Warehouse Catalog step. 3. Facts Next, create five facts for you project using the following table. Name each fact according to the table and map the facts to the appropriate data warehouse tables. Facts Fact Name

Fact Column

Fact Table

Freight

FREIGHT

ORDER_FACT

EOH Quantity

EOH_QTY

INVENTORY_CURR

Rush Charge

RUSH_CHARGE

RUSH_ORDER

UNIT_COST

INVENTORY_CURR LU_ITEM ORDER_DETAIL

Unit Cost

Units Received UNITS_RECEIVED INVENTORY_ORDERS

How to create facts:

1. In the Project Creation Assistant, to the left of Create facts, click >. 2. In the Fact Creation Wizard, click Next.


Page 12 of 38

3. From the Available columns list, select the columns below and click > to add them to the Facts list. Fact Name FREIGHT EOH_QTY RUSH_CHARGE UNIT_COST UNITS_RECEIVED

You can use the CTRL or SHIFT keys to select multiple columns at once. To rename a fact, right-click on the fact and select Rename. Alternatively, hit F2 on your keyboard. 4. Click Next. 5. After reviewing the list of facts that you are about to create, click Finish. The Project Creation Assistant returns with a green check mark next to the Create facts step. 4. Attributes Finally, create all 30 attributes as diagrammed in the MicroStrategy Tutorial logical data model in Appendix A. Use the data warehouse physical schema in Appendix B for assistance. When creating the attributes for your project, rename each attribute to match the names in the logical data model. Take note of the following relationships that are not explicitly defined in the logical data model. Catalog and Item have a many-to-many relationship. Country and Distribution center have a compound relationship. Distribution Center and Call Center have a one-to-one relationship. Manager and Call Center have a one-to-one relationship. Item is a child of Promotion (one-to-many). Day is a child of Promotion (one-to-many). MicroStrategy MCD Practice Project – MicroStrategy Tutorial Revised: 10/8/2009 © 2009 MicroStrategy, Inc. All Rights Reserved

Page 13 of 38

The following table (continued on the next page) lists each attribute that you need to create. Use the table as you work through the attribute creation process. Cells with bold text indicate a compound key or a relationship type that is something other than one-to-many. Detailed instructions on creating the Distribution Center compound attribute are outlined as well. How to create attributes:

1. In the Project Creation Assistant, to the left of the Create attributes, click >. 2. In the Attribute Creation Wizard, click Next. 3. Using the Attributes table below, from the Available columns list, select the ID columns and click > to them to the Attributes list. All columns that end in ID are automatically highlighted for you. Clicking > will move all of these ID columns to the Attributes list. For this project, this action is not desirable. You can use the CTRL or SHIFT keys to select multiple ID columns at once. To rename an attribute, right-click the attribute and select Rename. Alternatively, hit F2 on your keyboard. At this point, do not add Dist_Ctr_ID. Attributes Attribute Name

Lookup Table

ID Column

DESC Column

Children

Birth Date

LU_Employee

Birth_Date

Use ID as description

Employee

Brand

LU_Brand

Brand_ID

Brand_Desc

Item

Call Center

LU_Call_Ctr

Call_Ctr_ID

Center_Name

Employee

Catalog

LU_Catalog

Cat_ID

Cat_Desc

Item(M:M)

Category

LU_Category

Category_ID

Category_Desc

Subcategory

Country

LU_Country

Country_ID

Country_Name

Region and Distribution Center

Cust_City_ID

Cust_City_Name

Customer

Customer City LU_Cust_City


Page 14 of 38

Customer Region

LU_Cust_Region

Cust_Region_ID

Cust_Region_Name

Customer State

Customer State

LU_Cust_State

Cust_State_ID

Cust_State_Name

Customer City

Customer

LU_Customer

Customer_ID

Cust_Last_Name

Order

Day

LU_Day

Day_Date


NONE

Discontinued

LU_Item

Disc_CD


Item

Distribution Center

LU_Dist_Ctr

Dist_Ctr_ID & Country_ID

Dist_Ctr_Name

Call Center(1:1)

Employee

LU_Employee

Emp_ID

Emp_Last_Name

NONE

Hire Date

LU_Employee

Hire_Date


Attribute Name

Lookup Table

ID Column

DESC Column

Children

Income Bracket

LU_Income

Income_ID

Bracket_Desc

Customer

Item

LU_Item

Item_ID

Item_Name

NONE

Manager

LU_Manager

Manager_ID

Mgr_Last_Name

Call Center(1:1)

Month

LU_Month

Month_ID

Month_Desc

Day

Employee

Month of Year LU_Month_of_Year Month_of_Year

Month_of_Year_Name Month

Order

Order_Detail

Order_ID


NONE

Promotion

LU_Promotion

Promo_Sale_ID

Promo_Sale

Item, Day

Promotion Type

LU_Promo_Type

Promo_Type_ID

Promo_Type_Desc

Promotion

Payment Method

LU_Pymt_Type

Pymt_Type

Pymt_Desc

Order

Quarter

LU_Quarter

Quarter_ID

Quarter_Desc

Month

Region

LU_Region

Region_ID

Region_Name

Call center

Shipper

LU_Shipper

Shipper_ID

Shipper_Desc

Order

Subcategory

LU_Subcateg

Subcat_ID

Subcat_Desc

Item

Supplier

LU_Supplier

Supplier_ID

Supplier_Name

Item

Year

LU_Year

Year_ID


Quarter

4. To create the compound attribute, Distribution Center, do the following: Click Compound Attributes. Click Add.


Page 15 of 38

Name the compound attribute Distribution Center. Select COUNTRY_ID and while holding down the CTRL key, select DIST_CTR_ID. -

Ensure that you do not have the first column (Address) selected because it will cause an error.

Click OK. -

Under Compound Attributes, you should now see your Distribution Center attribute. You can expand it to see its compound ID columns.

5. Click Next. 6. Specify attribute description columns according to the Attributes table. Note: Architect makes an educated guess for the description column based on the attribute’s ID column that you selected. Attributes whose description columns may need to be changed include Call Center, Customer City, Customer Region, Customer, Discontinued, Employee, Item, Manager, Order, Promotion, and Year. You specify the description column for Distribution Center in a later step. 7. Specify attribute lookup tables according to the Attributes table. Note: Architect makes an educated guess for the lookup table based on the attribute ID and description columns that you selected. The Day attribute’s lookup table may need to be changed. You specify the lookup table for Distribution Center in a later step. 8. Click Next.


Page 16 of 38

9. For the Distribution Center compound attribute, do the following: For the lookup table, select LU_DIST_CTR. For the description column, click Add and select DIST_CTR_NAME. Click OK. 10. Click Next. 11. Specify the children for each attribute according to the Attributes table by doing the following: From the Attributes list, select an attribute and click Add. In the Select Children Attributes window, select all attributes that you want to assign as immediate children and click OK. In the Children of list, select the relationship type. -

Note: Architect makes an educated guess for the children based on the attribute ID columns that you selected.

12. Click Next. 13. After reviewing the list of attributes you are about to create, click Finish. The Project Creation Assistant returns with a green check mark next to the Create attributes step 14. In the Project Creation Assistant, click OK. You should now see Practice Tutorial Project under Practice Tutorial Project Source.


Page 17 of 38

5. Create Additional Facts Using the Fact Editor, create two additional facts according to the table below: Fact Name ([QTY_SOLD] * ([UNIT_PRICE] DISCOUNT))

Revenue [TOT_DOLLAR_SALES]

[ORDER_AMT]

Fact Expressions

Source Tables

ORDER_DETAIL CITY_CTR_SLS CITY_MNTH_SLS CITY_SUBCAT_SLS CUSTOMER_SLS DAY_CTR_SLS ITEM_EMP_SLS ITEM_MNTH_SLS MNTH_CATEGORY_SLS QTR_CATEGORY_SLS STATE_REGION_MNTH_SLS STATE_SUBCAT_MNTH_SLS STATE_SUBCAT_REGION_SLS SUBCAT_MNTH_CTR_SLS YR_CATEGORY_SLS ORDER_FACT

Discount QTY_SOLD * DISCOUNT ORDER_DETAIL

How to create facts using the Fact Editor:

1. Expand the Practice Tutorial Project. 2. Navigate to the Schema Objects\Facts folder. Notice that the facts you created earlier were saved to this location. 3. On the File menu, point to New, and select Fact. 4. In the New Fact - Create New Fact Expression window, for Source table, select ORDER_DETAIL.


Page 18 of 38

5. From the Available columns list, select the appropriate fact columns to define the following expression in the Fact expression window: ([QTY_SOLD] * ([UNIT_PRICE] - DISCOUNT)) 6. Click Validate to check the syntax. 7. Under Mapping method, ensure Automatic is selected. 8. Click OK. 9. In the Fact Editor, on the Definition tab, click New and define the two other fact expressions of Revenue. 10. Click Save and Close. 11. In the Save As window, name the fact Revenue. 12. Click Save. 13. Following a similar procedure, use the table to create the Discount fact. 14. Update the project schema. 6. The Customer Attribute Add the following four additional attribute forms to the Customer attribute: First Name, Address, Email, and Full Name. Full Name should display the last name and then the first name of a customer (for example, Smith, Jones). The default report display form should be Full Name. The default browsing display forms should be Last Name and First Name. How to add attribute forms to an existing attribute:

1. Navigate to the Schema Objects\Attributes folder. Notice that the attributes you created earlier were saved to this location. 2. Double-click Customer. 3. In the Attribute Editor, on the Forms tab, select the DESC form and click Modify.


Page 19 of 38

4. In the Modify Attribute Form window, under Form general information, rename the form to Last Name. 5. Click OK. 6. On the Forms tab, click New. 7. In the Create New Form Expression window, for Source table, select LU_Customer. 8. In the Available columns list, double-click Cust_First_Name to add it to the Form expression window. 9. Under Mapping method, ensure Automatic is selected. 10. Click OK. 11. In the Create New Attribute Form window, under Form general information, rename the form to First Name. 12. Click OK. 13. Using the following table, follow a similar procedure to create the remaining three attribute forms: Full Name, Address, and E-mail. Attribute Form Name Attribute Form Expression

Source Table CUSTOMER_SLS LU_CUSTOMER

ID

CUSTOMER_ID ORDER_DETAIL ORDER_FACT

Last Name

CUST_LAST_NAME

LU_CUSTOMER

First Name

CUST_FIRST_NAME

LU_CUSTOMER

Full Name

CUST_LAST_NAME + ", " + CUST_FIRST_NAME LU_CUSTOMER

Address

ADDRESS

LU_CUSTOMER

E-mail

EMAIL

LU_CUSTOMER

14. After you created the remaining forms, in the Attribute Editor, click the Display tab. 15. From the Report display forms list, select each form except for Full Name and click < to return them to the Available forms list. 16. From the Browse forms list, select each form except for Last Name and First Name and click < to return them to the Available forms list.


Page 20 of 38

17. Click Save and Close. 18. Update the project schema. 7. Rush Order Attribute Create a Rush Order attribute, basing the ID attribute form on the Rush_Order column found in the Order_Fact table. Next, add an implicit ID form expression of “Y” for the Rush_Order table. How to create an implicit attribute form expression for the Rush Order attribute:

1. On the File menu, point to New and select Attribute. 2. In the Create New Form Expression window, for Source table, select Order_Fact. 3. From the Available columns list, double-click Rush_Order to add it to the Form expression window. 4. Under Mapping method, ensure that Automatic is selected. 5. Click OK. 6. In the Create New Attribute Form window, click New. 7. In the form expression window, enter "Y" (including the quotes). 8. Under Mapping method, ensure Manual is selected. 9. Click OK. 10. In the Create New Attribute Form window, in the Source tables list, check RUSH_ORDER. 11. Click OK. 12. When you receive a warning about the data type being text/character based, click OK. 13. In the Attribute Editor, click the Children tab. 14. Click Add. 15. In the Add Children Attributes window, from the Child candidates list, select Order and click > to add it to the Selected children list.


Page 21 of 38

16. Click OK. 17. In the Attribute Editor, click Save and Close. 18. In the Save As window, name the attribute Rush Order. 19. Click Save. 20. Update the project schema. 8. Customer Age Attribute Customer Age is a derived attribute. Use an ApplySimple statement to calculate a customer’s age based on their birth date. How to create a derived Customer Age Attribute:

1. On the File menu, point to New and select Attribute. 2. In the Create New Form Expression window, for Source table, select LU_CUSTOMER. 3. In the form expression window, enter the following expression: ApplySimple(“datediff(‘YYYY’,#0,Now())",[CUST_BIRTHDATE]) 4. Click Validate to check the syntax. 5. Under Mapping method, ensure Automatic is selected. 6. Click OK. 7. In the Create New Attribute Form window, click OK. 8. In the Attribute Editor dialog box, click the Children tab. 9. Click Add. 10. In the Add Children Attributes window, from the Child candidates list, select Customer and click > to add it to the Selected children list. 11. Click OK. 12. In the Attribute Editor, click Save and Close. 13. In the Save As window, rename the attribute to Customer Age.


Page 22 of 38

14. Click Save. 15. Update the project schema.

9. User Hierarchies Now that you have created the attributes and specified their parent-child relationships, you are ready to create user hierarchies. User hierarchies enable you to easily browse through attributes in Desktop. Create the following five user hierarchies according to their requirements: Time Hierarchy -

Include all time-related attributes as diagrammed in the logical data model.

-

Make all attributes entry points.

-

Allow drilling on the hierarchy.

Customers Hierarchy -

Include all customer-related attributes as diagrammed in the logical data model. In addition, include the Customer Age and Rush Order attributes created in section 8 and 9.

-

Make all attributes entry points, except Order.

-

Lock the Order attribute.

-


Geography Hierarchy -

Include all geography-related attributes as diagrammed in the logical data model.

-


-


Promotions Hierarchy -

Include all promotion-related attributes as diagrammed in the logical data model.

-



Page 23 of 38

-


Products Hierarchy -

Include all product-related attributes as diagrammed in the logical data model, except Discontinued.

-


-


How to create user hierarchies:

1. Navigate to the Schema Objects\Hierarchies\Data Explorer folder. 2. On the File menu, point to New and select Hierarchy. 3. In the Select Attributes window, from the Available objects list, select Year, Quarter, Month, Month of Year, and Day and click > to add them to the Selected objects list. You can use the CTRL or SHIFT key to select multiple attributes at once. 4. Click OK. In the Hierarchy Editor, notice that the attributes are arranged according to their parent-child relationships. Attributes with a green check mark are entry points. By default, any attribute without a parent is an entry point. 5. Right-click Quarter and select Set as Entry Point. 6. Follow the same procedure for Month and Day. To remove an entry point, right-click an attribute and select Remove as Entry Point. To lock an attribute, right-click the attribute, point to Element Display and select Locked. 7. Click Save and Close. 8. At the bottom of the Hierarchy Editor, leave the Use as a drill hierarchy check box selected.


Page 24 of 38

9. Click Save and Close. 10. When notified that this hierarchy has been defined as a drill hierarchy, click OK. 11. In the Save As window, rename the hierarchy to Time Hierarchy. 12. Click Save. 13. Following a similar procedure create the remaining user hierarchies (Product, Customer, Geography, and Promotions hierarchies) following the guidelines listed below: The Customers Hierarchy should contain the following attributes: Customer, Customer City, Customer Region, Customer State, Income Bracket, Order, Payment Method, Rush Order, Customer Age, and Shipper. Remember to make all attributes entry points except Order. Lock the Order attribute and allow drilling on the hierarchy. The Geography Hierarchy should contain the following attributes: Birth Date, Call Center, Country, Distribution Center, Employee, Hire Date, Manager, and Region. Remember to make all attributes entry points and allow drilling on the hierarchy. The Promotions Hierarchy should contain the following attributes: Promotion Type and Promotion. Remember to make all attributes entry points and allow drilling on the hierarchy. The Products Hierarchy should contain the following attributes: Brand, Discontinued, Catalog, Category, Item, Subcategory, and Supplier. Remember to make all attributes entry points and allow drilling on the hierarchy. 14. Update the project schema.


Page 25 of 38

REPORT DEVELOPMENT This phase of the project requires you to create a series of advanced reports. It covers concepts from the MicroStrategy Desktop: Advanced Reporting course, but it is limited to those advanced reporting techniques that can be completed in a 2-tier project. The actual MCD Certification contains additional reporting requirements. Be sure to review all concepts from the MicroStrategy Desktop: Advanced Reporting and MicroStrategy Report Services Essentials courses prior to attempting the MCD Certification. The result checks in the report development phase of this document are based on the included Practice Tutorial_WH. If you do not use the included Practice Tutorial_WH, your data model, schema, and report results may differ from the data model, schema, and report results contained in this document. Note: This practice project is not comprehensive and does not cover all topics that are required to successfully complete the actual MCD project. In addition, this practice project contains report development information that is not included in the actual MCD project.

REPORT 1: PEAK SALES Purpose: Sales for all items typically peak in December, right around the holiday season. However, after some further investigation, there were other peak months in 2005 for certain subcategories. For example, sales for Cameras peaked in April while those for Art and Architecture peaked in June. In addition, sales for Science and Technology peaked in March. This report will be used to illustrate how much sales during the peak months contributed to sales of the same items during the entire year. Report Requirements: Display the revenue for each item in the three selected subcategories during their peak periods (for example, Cameras in April 2005), as well as sales of the same items for the entire year. For each item, show the percent contribution their peak sales contributed to their annual sales. Display a totals row that shows a total for each metric by subcategory. Be sure the percent contribution metric displays Smart Totals. Change the formatting of these totals to make them stand out.


Page 26 of 38

Required Functionality: Joint Element List Metrics (Compound) Metrics (Conditionality) Metrics (Level) Totals, including advanced features and formatting Report Columns: Month (not Month of Year) Subcategory Item Seasonal Revenue (Revenue for items in their associated subcategory during its particular peak month in 2005) Revenue All Year Revenue (Revenue for items in their associated subcategory for the entire year) Percent Contribution of Seasonal to Annual Sales Result Set Check: The report result returns a total of 44 rows. Additional Information (will not be provided in the MCD Project): A screenshot of the first few rows of the report is displayed below:


Page 27 of 38

REPORT 2: DELAYED ORDERS Purpose: This report will be used to identify the customer cities where the time between order date and ship date is unacceptably high. A two to three week delivery period is guaranteed to your customers, so any orders that are shipped three weeks, or 21 days, past the date the order was placed is considered delayed. Report Requirements: Include all data in the data warehouse with no restriction to a particular time period. Display each customer city where items were shipped 21 days or more later than the day they were ordered. Display both the number of delayed orders from the customer cities as well as the total number of orders of those cities. Also, display the percent of delayed orders to total orders rounded to the nearest whole percent. Display customer cities grouped by their customer regions. Include a row showing totals by Customer Region for each metric on the report. Also include a grand total for all metrics. Be sure the percent metric displays Smart Totals. Required Functionality: Attribute (Creation) Filters (Attribute-to-attribute expression) Metrics (Compound) Metrics (Count) Metrics (Level) Number Formatting Smart Totals Report Columns: Customer Region Customer City Orders Delayed >= 21 days Count of All Orders by Customer City % Late Orders Hints: You need to create the Ship Date attribute. Which fact table should you use for the Orders Delayed >= 21 days metric? Result Set Check: The report result returns a total of 444 rows. Albany had a total of 370 orders and 96 delayed orders. MicroStrategy MCD Practice Project – MicroStrategy Tutorial Revised: 10/8/2009 © 2009 MicroStrategy, Inc. All Rights Reserved

Page 28 of 38

Additional Information (will not be provided in the MCD Project): The Day attribute should have two ID form expressions: One for Day_Date and another for Order_Date. For the Orders Delayed >=21 days metric, the Count parameter of Fact ID should be Freight. (This forces the metric to count from the correct fact table.) The child of Ship Date is Customer. A screenshot of the first few rows of the report is displayed below:


Page 29 of 38

REPORT 3: BEST CUSTOMER QUARTILING Purpose: This report will identify all customers who have made more than 20 purchases in 2006. The final report needs to place these customers into quartiles in each of their customer regions based on the total dollars that they spent. Report Requirements: Display Customer Region, Customer Information (including last name, first name, and address), total dollars spent by each customer, and the quartile each customer falls into (for example, 1, 2, 3, or 4 with 1 being the 'best' customers). Sort the customers alphabetically by Customer Region, then by quartile within the region (with the best customers appearing first), and finally by dollar sales within the quartile in descending order. Include all customer regions, both foreign and domestic. Prompt the user to select the number of repeat purchases the customer must have made in order to be included in the report. The user should be limited to selecting greater than or equal to a number between 15 and 50. (For the initial report, select 20.) Required Functionality: Attribute Forms Metrics (Advanced Functions) Advanced Sorting Auto Prompts Report Columns: Customer Region Customer last name, first name, and address Revenue Customer Quartile Results Set Check: With a prompt answer of 20, the report result returns a total of 71rows. The Northwest customer region has two customers: one in quartile 2 and another in quartile 4.


Page 30 of 38

Additional Information (will not be provided in MCD): The Descending Quartile metric uses the NTile function. (The NTile parameters specify 4 tiles and break by Customer Region.) The report filter contains a set qualification based on Count of Orders, and embedded in the set qualification is a value prompt. The attribute forms for displayed for Customer should be specified only on this report. A screenshot of the first few rows of the report is displayed below:


Page 31 of 38

Document Development Because this project is limited to functionalty available within a 2-tier project, it does not include a Document Creation phase. However, be sure to review all concepts covered in the MicroStrategy Report Services: Document Essentials and the MicroStrategy Report Services: Dynamic Dashboards courses, as these concepts are addressed in the MCD Certification.


Page 32 of 38

APPENDIX A Geography Hierarchy

Promotions Hierarchy


Page 33 of 38

Products Hierarchy

Time Hierarchy


Page 34 of 38

Customer Hierarchy


Page 35 of 38

APPENDIX B Geography Schema

Products Schema


Page 36 of 38

Customers Schema

Time Schema


Page 37 of 38

Promotions Schema

Fact Tables


Page 38 of 38

Practice Project - Tutorial Microstrategy

Recommend Documents