Advanced Project Design.pdf

Full text

(1)MICROSTRATEGY ARCHITECT: ADVANCED PROJECT DESIGN. Course Guide Version: ADVPD-941-MAR14-CG.

(2) © 2000–2014 MicroStrategy Incorporated. All rights reserved. This Course (course and course materials) and any Software are provided “as is” and without express or limited warranty of any kind by either MicroStrategy Incorporated (“MicroStrategy”) or anyone who has been involved in the creation, production, or distribution of the Course or Software, including, but not limited to, the implied warranties of merchantability and fitness for a particular purpose. The entire risk as to the quality and performance of the Course and Software is with you. Should the Course or Software prove defective, you (and not MicroStrategy or anyone else who has been involved with the creation, production, or distribution of the Course or Software) assume the entire cost of all necessary servicing, repair, or correction. In no event will MicroStrategy or any other person involved with the creation, production, or distribution of the Course or Software be liable to you on account of any claim for damage, including any lost profits, lost savings, or other special, incidental, consequential, or exemplary damages, including but not limited to any damages assessed against or paid by you to any third party, arising from the use, inability to use, quality, or performance of such Course and Software, even if MicroStrategy or any such other person or entity has been advised of the possibility of such damages, or for the claim by any other party. In addition, MicroStrategy or any other person involved in the creation, production, or distribution of the Course and Software shall not be liable for any claim by you or any other party for damages arising from the use, inability to use, quality, or performance of such Course and Software, based upon principles of contract warranty, negligence, strict liability for the negligence of indemnity or contribution, the failure of any remedy to achieve its essential purpose, or otherwise. The Course and the Software are copyrighted and all rights are reserved by MicroStrategy. MicroStrategy reserves the right to make periodic modifications to the Course or the Software without obligation to notify any person or entity of such revision. Copying, duplicating, selling, or otherwise distributing any part of the Course or Software without prior written consent of an authorized representative of MicroStrategy are prohibited.. U.S. Government Restricted Rights. It is acknowledged that the Course and Software were developed at private expense, that no part is public domain, and that the Course and Software are Commercial Computer Software and/or Commercial Computer Software Documentation provided with RESTRICTED RIGHTS under Federal Acquisition Regulations and agency supplements to them. Use, duplication, or disclosure by the U.S. Government is subject to restrictions as set forth in subparagraph (c)(1)(ii) of the Rights in Technical Data and Computer Software clause at DFAR 252.227-7013 et. seq. or subparagraphs (c)(1) and (2) of the Commercial Computer Software—Restricted Rights at FAR 52.227-19, as applicable. The Contractor is MicroStrategy, 1850 Towers Crescent Plaza, Vienna, Virginia 22182. Rights are reserved under copyright laws of the United States with respect to unpublished portions of the Software.. Copyright Information. All Contents Copyright © 2014 MicroStrategy Incorporated. All Rights Reserved.. Trademark Information. MicroStrategy, MicroStrategy 6, MicroStrategy 7, MicroStrategy 7i, MicroStrategy 7i Evaluation Edition, MicroStrategy 7i Olap Services, MicroStrategy 8, MicroStrategy 9, MicroStrategy Distribution Services, MicroStrategy MultiSource Option, MicroStrategy Command Manager, MicroStrategy Enterprise Manager, MicroStrategy Object Manager, MicroStrategy Reporting Suite, MicroStrategy Power User, MicroStrategy Analyst, MicroStrategy Consumer, MicroStrategy Email Delivery, MicroStrategy BI Author, MicroStrategy BI Modeler, MicroStrategy Evaluation Edition, MicroStrategy Administrator, MicroStrategy Agent, MicroStrategy Architect, MicroStrategy BI Developer Kit, MicroStrategy Broadcast Server, MicroStrategy Broadcaster, MicroStrategy Broadcaster Server, MicroStrategy Business Intelligence Platform, MicroStrategy Consulting, MicroStrategy CRM Applications, MicroStrategy Customer Analyzer, MicroStrategy Desktop, MicroStrategy Desktop Analyst, MicroStrategy Desktop Designer, MicroStrategy eCRM 7, MicroStrategy Education, MicroStrategy eTrainer, MicroStrategy Executive, MicroStrategy Infocenter, MicroStrategy Intelligence Server, MicroStrategy Intelligence Server Universal Edition, MicroStrategy MDX Adapter, MicroStrategy Narrowcast Server, MicroStrategy Objects, MicroStrategy OLAP Provider, MicroStrategy SDK,.

(3) MicroStrategy Support, MicroStrategy Telecaster, MicroStrategy Transactor, MicroStrategy Web, MicroStrategy Web Business Analyzer, MicroStrategy World, Application Development and Sophisticated Analysis, Best In Business Intelligence, Centralized Application Management, Information Like Water, Intelligence Through Every Phone, Intelligence To Every Decision Maker, Intelligent E-Business, Personalized Intelligence Portal, Query Tone, Rapid Application Development, MicroStrategy Intelligent Cubes, The Foundation For Intelligent E-Business, The Integrated Business Intelligence Platform Built For The Enterprise, The Platform For Intelligent E-Business, The Scalable Business Intelligence Platform Built For The Internet, Office Intelligence, MicroStrategy Office, MicroStrategy Report Services, MicroStrategy Web MMT, MicroStrategy Web Services, Pixel Perfect, Pixel-Perfect, MicroStrategy Mobile, MicroStrategy Integrity Manager and MicroStrategy Data Mining Services are all registered trademarks or trademarks of MicroStrategy Incorporated.. All other company and product names may be trademarks of the respective companies with which they are associated. Specifications subject to change without notice. MicroStrategy is not responsible for errors or omissions. MicroStrategy makes no warranties or commitments concerning the availability of future products or versions that may be planned or under development.. Patent Information. This product is patented. One or more of the following patents may apply to the product sold herein: U.S. Patent Nos. 6,154,766, 6,173,310, 6,260,050, 6,263,051, 6,269,393, 6,279,033, 6,567,796, 6,587,547, 6,606,596, 6,658,093, 6,658,432, 6,662,195, 6,671,715, 6,691,100, 6,694,316, 6,697,808, 6,704,723, 6,741,980, 6,765,997, 6,768,788, 6,772,137, 6,788,768, 6,798,867, 6,801,910, 6,820,073, 6,829,334, 6,836,537, 6,850,603, 6,859,798, 6,873,693, 6,885,734, 6,940,953, 6,964,012, 6,977,992, 6,996,568, 6,996,569, 7,003,512, 7,010,518, 7,016,480, 7,020,251, 7,039,165, 7,082,422, 7,113,993, 7,127,403, 7,174,349, 7,181,417, 7,194,457, 7,197,461, 7,228,303, 7,260,577, 7,266,181, 7,272,212, 7,302,639, 7,324,942, 7,330,847, 7,340,040, 7,356,758, 7,356,840, 7,415,438, 7,428,302, 7,430,562, 7,440,898, 7,486,780, 7,509,671, 7,516,181, 7,559,048, 7,574,376, 7,617,201, 7,725,811, 7,801,967, 7,836,178, 7,861,161, 7,861,253, 7,881,443, 7,925,616, 7,945,584, 7,970,782, 8,005,870, 8,051,168, 8,051,369, 8,094,788, 8,130,918, 8,296,287, 8,321,411 and 8,452,755. Other patent applications are pending.. How to Contact Us MicroStrategy University 1850 Towers Crescent Plaza Tysons Corner, VA 22182 Phone: 877.232.7168 Fax: 703.848.8602 E-mail: [email protected] http://www.microstrategy.com/training-events. MicroStrategy Incorporated 1850 Towers Crescent Plaza Tysons Corner, VA 22182 Phone: 703.848.8600 Fax: 703.848.8610 E-mail: [email protected] http://www.microstrategy.com.

(4)

(5) MicroStrategy Architect: Advanced Project Design. Table of Contents. TABLE OF CONTENTS. Preface. Course Description...................................................................... 9 Who Should Take this Course ............................................... 10 Course Prerequisites ............................................................. 10 Follow-up Courses ................................................................. 10 Related Certifications............................................................. 10 Course Objectives ................................................................. 11 About the Course Materials ......................................................... 12 Content Descriptions ............................................................. 12 Learning Objectives ............................................................... 12 Lessons ................................................................................. 12 Opportunities for Practice ...................................................... 13 Typographical Standards ....................................................... 13 MicroStrategy Courses .......................................................... 15 Core Courses......................................................................... 15 Advanced Courses ................................................................ 16. 1. Introduction to Advanced Project Design. Lesson Description ................................................................... 17 Lesson Objectives ................................................................. 18 Review of the Project Design Process......................................... 19 Review of Schema Objects.......................................................... 22 Using the Fact Editor ............................................................. 24 Lesson Summary......................................................................... 26. © 2014 MicroStrategy Inc.. 5.

(6) Table of Contents. 2. Managing Project Schema. MicroStrategy Architect: Advanced Project Design. Lesson Description ................................................................... 27 Lesson Objectives ................................................................. 28 Review of Database Instances .................................................... 29 Primary/Secondary Database Instances at the Project Level 30 Maintaining Warehouse ............................................................... 32 Maintaining Tables................................................................. 32 Aggregation Awareness............................................................... 35 Review of Base and Aggregate Fact Tables.......................... 35 How Is MicroStrategy Aggregate-Aware?.............................. 36 Data Marts ................................................................................... 40 What Is a Data Mart? ............................................................. 40 Creating Data Marts............................................................... 46 Using Data Marts in a Project ................................................ 54 Lesson Summary......................................................................... 58 Exercises: Managing Project Schema ......................................... 61 Forecasting Project Information ............................................. 61. 3. Using MicroStrategy MultiSource Option. Lesson Description ................................................................... 73 Lesson Objectives ................................................................. 74 Introduction to MicroStrategy MultiSource Option ....................... 75 Primary/Secondary Database Instances at the Table Level .. 76 Support for Duplicate Tables ................................................. 77 SQL Generation for Multisource Reports............................... 78 Supported Use Cases............................................................ 83 Associating Tables to Database Instances.................................. 88 Adding Tables with a Single Data Source.............................. 89 Adding Tables with Multiple Data Sources ............................ 92 Changing the Primary Database Instance for a Table ........... 95 Removing a Database Instance from a Table........................ 98 Creating Objects for Multisource Reports.................................. 100 Creating the Forecast Revenue Fact ................................... 102 Creating the Forecast Revenue Metric ................................ 103 Creating a Multisource Report ................................................... 104 Processing of SQL for Sample Report................................. 105 Lesson Summary....................................................................... 114 Exercises: Using MicroStrategy MultiSource Option ................. 117 (Provisional) Demonstration Exercises ................................ 117 Add Tables from a Secondary Database Instance and Create an Attribute and Metric for a Multisource Report ...................... 121 Create a Multisource Report ................................................ 126. 6. © 2014 MicroStrategy Inc..

(7) MicroStrategy Architect: Advanced Project Design. Table of Contents. 4. Fact Level Extensions Lesson Description ................................................................. 129 Lesson Objectives ............................................................... 130 Overview of Fact Level Extensions............................................ 131 Creating Fact level Extensions in Fact Editor ...................... 133 Degrading Fact Levels............................................................... 135 Steps for Fact Degradation .................................................. 137 Creating a Fact Degradation................................................ 139 Extending Fact Levels ............................................................... 143 Steps for Fact Extension Using the Table Relation Method 146 Creating Fact Extensions Using the Table Relation Method 151 Disallowing Fact Levels ............................................................. 156 Lesson Summary....................................................................... 161 Exercise: Fact Level Extensions................................................ 163 Create a Fact Degradation .................................................. 163. 5. Transformations. Lesson Description ................................................................. 169 Lesson Objectives ............................................................... 170 What Is a Transformation? ........................................................ 171 Types of Transformations .................................................... 173 Transformation Components ............................................... 176 Creating and Using Transformations ......................................... 178 Creating Transformations .................................................... 178 Using Transformations in Metrics ........................................ 181 Transformation Examples .................................................... 185 Lesson Summary....................................................................... 190 Exercise: Transformation........................................................... 191 Create the Last Year’s Transformation ................................ 191. 6. Partitioning. Lesson Description ................................................................. 199 Lesson Objectives ............................................................... 200 Introduction to Partitioning Concepts......................................... 201 Warehouse Partition Mapping ................................................... 204 Overview .............................................................................. 204 Implementing Warehouse Partition Mapping ....................... 205 Metadata Partition Mapping....................................................... 212 Overview .............................................................................. 212 Implementing Metadata Partition Mapping .......................... 212 Lesson Summary....................................................................... 216. © 2014 MicroStrategy Inc.. 7.

(8) Table of Contents. A. Warehouse Catalog. MicroStrategy Architect: Advanced Project Design. Maintaining Individual Tables............................................... 220 Project-Wide Warehouse Catalog Options .......................... 222 Adding tables with Multisource Option....................................... 225. Index ......................................................................................... 233. 8. © 2014 MicroStrategy Inc..

(9) PREFACE Course Description This one day course covers advanced features involved in the creation and maintenance of a MicroStrategy project. The course assumes an understanding of basic report development concepts from the two day MicroStrategy Developer: Reporting Essentials course and the two day MicroStrategy Architect: Project Design Essentials course. First, students will learn how to manage project data sources using primary and secondary database instances and how to maintain a project schema with the Architect graphical interface. Next, students will learn how the MicroStrategy Engine is aggregate-aware and how to create aggregate tables using data marts. Next, students will learn how to use MicroStrategy MultiSource Option™ to configure projects to access heterogeneous data sources. Finally, students will learn about fact level extensions, transformations, and partition mappings. After taking this course, students will understand how to maintain MicroStrategy projects and how to build advanced schema objects.. © 2014 MicroStrategy Inc.. 9.

(10) Preface. MicroStrategy Architect: Advanced Project Design. Who Should Take this Course This course is designed for: •. Project architects. Course Prerequisites Before starting this course, you should know all topics covered in the following courses: •. MicroStrategy Developer: Reporting Essentials. •. MicroStrategy Architect: Project Design Essentials. You should also have a basic knowledge of SQL.. Follow-up Courses After taking this course, you might consider taking the following courses: •. MicroStrategy Advanced Data Warehousing. Related Certifications This course does not have any recommended follow-up certifications.. 10 Who Should Take this Course. © 2014 MicroStrategy Inc..

(11) MicroStrategy Architect: Advanced Project Design. Preface. Course Objectives After completing this course, you will be able to: •. Describe the project design process, and describe the basic and advanced schema objects you can create with MicroStrategy Architect. (Page 18). •. Define the primary and secondary database instance, use the Architect graphical interface to maintain project tables, describe how the MicroStrategy SQL Engine is aggregate aware, and create aggregate fact tables using data marts. (Page 28). •. Describe how you can use MultiSource Option to access heterogeneous data sources, associate tables in a project to multiple database instances, create objects for multisource reports, and create multisource reports. (Page 74). •. Describe the three types of fact level extensions available in MicroStrategy Architect, create fact degradations to lower the levels of facts, create fact extensions to extend the levels of facts to other hierarchies, and disallow fact levels to prevent unnecessary cross joins. (Page 130). •. Create different types of transformations, use transformations in transformation metrics, and describe common uses for transformations in reporting. (Page 170). •. Describe the purpose of partitioning, explain the difference between the server-level and the application-level partitioning, and use warehouse and metadata partition mapping to support partitioned fact tables in a MicroStrategy project. (Page 200). © 2014 MicroStrategy Inc.. Course Objectives. 11.

(12) Preface. MicroStrategy Architect: Advanced Project Design. About the Course Materials This course is organized into lessons and reference appendices. Each lesson focuses on major concepts and skills that help you to better understand MicroStrategy products and use them to implement MicroStrategy projects. The appendices provide you with supplemental information to enhance your knowledge of MicroStrategy products.. Content Descriptions Each major section of this course begins with a Description heading. The Description introduces you to the content contained in that section.. Learning Objectives Learning objectives enable you to focus on the key knowledge and skills you should obtain by successfully completing this course. Objectives are provided for you at the following three levels: •. Course—You will achieve these overall objectives by successfully completing all the lessons in this course. The Course Objectives heading in this Preface contains the list of course objectives.. •. Lesson—You will achieve these main objectives by successfully completing all the topics in the lesson. You can find the primary lesson objectives directly under the Lesson Objectives heading at the beginning of each lesson.. •. Main Topic—You will achieve this secondary objective by successfully completing the main topic. The topic objective is stated at the beginning of the topic text. You can find a list of all the topic objectives in each lesson under the Lesson Objectives heading at the beginning of each lesson.. Lessons Each lesson sequentially presents concepts and guides you with step-by-step procedures. Illustrations, screen examples, bulleted text, notes, and definition tables help you to achieve the learning objectives.. 12 About the Course Materials. © 2014 MicroStrategy Inc..

(13) MicroStrategy Architect: Advanced Project Design. Preface. Opportunities for Practice The following sections within lessons provide you with opportunities to reinforce important concepts, practice new product and project skills, and monitor your own progress in achieving the lesson and course objectives: • Review • Case Study • Business Scenario • Exercises. Typographical Standards Following are explanations of the font style changes, icons, and different types of notes that you see in this course.. Actions References to screen elements and keys that are the focus of actions are in bold Arial font style. The following example shows this style: Click Select Warehouse.. Code References to code, formulas, or calculations within paragraphs are formatted in regular Courier.New font style. The following example shows this style: Sum(Sales)/Number of Months. © 2014 MicroStrategy Inc.. About the Course Materials. 13.

(14) Preface. MicroStrategy Architect: Advanced Project Design. Data Entry References to literal data you must type in an exercise or procedure are in bold Arial font style. References to data you type that could vary from user to user or system to system are in bold italic Arial font style. The following example shows this style: Type copy c:\filename d:\foldername\filename.. Keyboard Keys References to a keyboard key or shortcut keys are in uppercase letters in bold Arial font style. The following example shows this style: Press CTRL+B.. New Terms New terms to note are in regular italic font style. These terms are defined when they are first encountered in the course. The following example shows this style: The aggregation level is the level of calculation for the metric.. Notes and Warnings. A note icon indicates helpful information. icon calls your attention to very important information that Ayouwarning should read before continuing the course.. 14 About the Course Materials. © 2014 MicroStrategy Inc..

(15) MicroStrategy Architect: Advanced Project Design. Preface. MicroStrategy Courses Core Courses •. Implementing MicroStrategy: Development and Deployment. •. MicroStrategy Web Essentials. •. MicroStrategy Web for Reporters and Analysts. •. MicroStrategy Web for Professionals. •. MicroStrategy Visual Insight Essentials. •. MicroStrategy Report Services: Documents and Dashboards. •. MicroStrategy Mobile for App Developers. •. MicroStrategy Architect: Project Design Essentials. •. MicroStrategy Developer: Reporting Essentials. •. MicroStrategy Developer: Advanced Reporting. •. MicroStrategy Office Essentials. © 2014 MicroStrategy Inc.. About the Course Materials. 15.

(16) Preface. MicroStrategy Architect: Advanced Project Design. Advanced Courses •. MicroStrategy Administration: Configuration and Security. •. MicroStrategy Administration: Application Management. •. MicroStrategy Engine Essentials. •. MicroStrategy Architect: Advanced Project Design. •. MicroStrategy Advanced Data Warehousing. •. MicroStrategy Data Mining and Advanced Analytics. •. MicroStrategy Developer: Advanced Reporting Case Studies. •. MicroStrategy Freeform SQL Essentials. •. MicroStrategy Transaction Services for Mobile App and Dashboard Developers. •. MicroStrategy Web SDK: Customization Essentials. •. MicroStrategy Web SDK: Customizing Security. •. MicroStrategy Web SDK: Portal Integration. All courses are subject to change. Please visit the MicroStrategy Web site for the latest education offerings.. 16 About the Course Materials. © 2014 MicroStrategy Inc..

(17) 1 INTRODUCTION TO ADVANCED PROJECT DESIGN. Lesson Description This lesson introduces you to the MicroStrategy Architect: Advanced Project Design course. In this lesson, you will first review the project design process and learn about the tools and components that enable you to manage the project schema. Next, you will review the basic schema objects that you can create in MicroStrategy Architect. Finally, you will learn about additional schema objects that enable you to perform advanced functions.. © 2014 MicroStrategy Inc.. 17.

(18) 1. Introduction to Advanced Project Design. MicroStrategy Architect: Advanced Project Design. Lesson Objectives After completing this lesson, you will be able to: Describe the project design process, and describe the basic and advanced schema objects you can create with MicroStrategy Architect.. After completing the topics in this lesson, you will be able to: •. Describe the project design process and learn about the tools and components that enable you to manage the project schema. (Page 19). •. Describe the basic schema objects that you can create in MicroStrategy Architect and learn about additional schema objects that enable you to perform advanced functions. (Page 22). 18 Lesson Objectives. © 2014 MicroStrategy Inc..

(19) MicroStrategy Architect: Advanced Project Design. Introduction to Advanced Project Design. 1. Review of the Project Design Process After completing this topic, you will be able to: Describe the project design process and learn about the tools and components that enable you to manage the project schema.. In the MicroStrategy Architect: Project Design Essentials course, you learned about the primary steps involved in the project design process: Project Design Process. Recall that project design involves more than just creating a project in MicroStrategy Architect. Understanding how users want to report on information in the data warehouse, how data in the warehouse is related, and how that data is stored are all fundamental parts of the project design process.. Designing the Logical Data Model When you design the logical data model, you need to determine the information that users want to see in reports and determine what information is actually available in the source systems. Finally, you design the model that incorporates both.. Designing the Data Warehouse Schema When you design the data warehouse schema, you need to first consider the advantages and disadvantages of various structures for storing data in the data warehouse. You then determine the optimal schema design that balances the reporting requirements, performance requirements, and maintenance overhead. Finally, you create the data warehouse using this schema design or modify the existing data warehouse to use this schema design.. © 2014 MicroStrategy Inc.. Review of the Project Design Process. 19.

(20) 1. Introduction to Advanced Project Design. MicroStrategy Architect: Advanced Project Design. Creating the Project in MicroStrategy Architect You need to have a solid design for the logical data model and data warehouse schema before you move on to creating the actual project. Both of these components can directly affect how you query data in a project, what data you can query, how fast queries run, and so forth.. Managing the Project Schema Managing the project schema is the final and ongoing step in the project design process. Over the life of the project, your logical data model or data warehouse may change, or your reporting needs may change, which can necessitate changes to schema objects.. 20 Review of the Project Design Process. © 2014 MicroStrategy Inc..

(21) MicroStrategy Architect: Advanced Project Design. Introduction to Advanced Project Design. 1. In the MicroStrategy Architect: Advanced Project Design course, you will learn about different strategies and tools that help you manage your project’s schema. In particular, you will learn about the following: •. Primary and secondary database instances—A database instance is a logical object in the MicroStrategy metadata that typically represents a connection to a data warehouse. Each project must have a primary database instance. You can associate any number of secondary database instances—data sources—to a project.. •. Aggregation awareness—MicroStrategy Architect assigns each table a logical table size and uses it to query the aggregate table rather than the base fact table in cases where either table could provide the answer.. •. Data marts—A data mart is a relational table containing results of a report. Among other applications, you can use data marts to create aggregate fact tables.. •. Multisource report execution—By default, the objects in a standard report have to come from a single data source. However, you can use the MultiSource Option—an add-on component to Intelligence Server—to overcome this limitation. MultiSource Option enables you to define a single project schema that uses multiple data sources. As a result, you can create a standard report that executes SQL against multiple data sources.. © 2014 MicroStrategy Inc.. Review of the Project Design Process. 21.

(22) 1. Introduction to Advanced Project Design. MicroStrategy Architect: Advanced Project Design. Review of Schema Objects After completing this topic, you will be able to: Describe the basic schema objects that you can create in MicroStrategy Architect and learn about additional schema objects that enable you to perform advanced functions.. Recall that schema objects are logical objects that relate application objects to data warehouse content. They are the bridge between your reporting environment and your data warehouse. As such, you have to create the basic schema objects a project requires before you can complete any other tasks, such as creating templates, filters, reports, or documents. In the MicroStrategy Architect: Project Design Essentials course you learned how to create the following basic schema objects that form the foundation of a MicroStrategy project: •. Tables—Logical objects that correspond to physical tables stored in the data warehouse that you want to use in a MicroStrategy project. •. Facts—Logical objects that relate aggregatable data stored in the data warehouse to the MicroStrategy reporting environment. They are usually numeric, and you can aggregate them to different levels, depending on your reporting needs.. •. Attributes—Logical objects that relate descriptive (non-fact) data stored in the data warehouse to the MicroStrategy reporting environment. They provide context for reporting on facts and define the level of detail at which you want to analyze facts.. •. Hierarchies—Logical objects that enable you to group attributes to reflect their relationships or provide convenient browsing and drilling paths in the MicroStrategy reporting environment.. 22 Review of Schema Objects. © 2014 MicroStrategy Inc..

(23) MicroStrategy Architect: Advanced Project Design. 1. Introduction to Advanced Project Design. The following illustration shows an example of each of these types of schema objects: Basic Schema Objects. In the MicroStrategy Architect: Advanced Project Design course, you will learn about additional properties of facts. You will learn how to create different types of fact extensions that will enable you to report on facts at additional levels, beyond how they are stored in the data warehouse. You can also create other types of schema objects in MicroStrategy that you use for more advanced functions: •. Transformations—Logical objects most often used to compare values at different times—for example, this year versus last year and today versus month to date. Transformations are useful for discovering and analyzing time-based trends in your data.. •. Partition mappings—Partitioning is the division of a larger table into smaller tables in the data warehouse. You can bring partitioned tables into a MicroStrategy project through warehouse partition mapping or metadata partition mapping.. © 2014 MicroStrategy Inc.. Review of Schema Objects. 23.

(24) 1. Introduction to Advanced Project Design. MicroStrategy Architect: Advanced Project Design. The following illustration shows an example of each of these types of schema objects. You will learn about these objects later in the course: Additional Schema Objects. Using the Fact Editor You created basic facts in the Architect graphical interface. However, if you want to create individual facts, modify existing facts, or add complexity to facts you initially created in Architect, you use the Fact Editor. The Fact Editor is one of the schema object editors available in Developer. It enables you to create or modify any type of fact or fact expression and configure a variety of fact-related settings. To access the Fact Editor:. 1 Open the desired project. 2 Do one of the following: If you are creating a new fact, on the File menu, point to New and select Fact. OR If you are modifying an existing fact, in the Schema Objects folder, select the Facts folder. In the Facts folder, double-click the fact you want to modify.. 24 Review of Schema Objects. © 2014 MicroStrategy Inc..

(25) MicroStrategy Architect: Advanced Project Design. Introduction to Advanced Project Design. 1. The following image shows the Fact Editor: Fact Editor. The Fact Editor has the following tabs: •. Definition—This tab enables you to create, modify, and delete fact expressions.. •. Column Alias—This tab enables you to modify the column alias for a fact.. •. Extensions—This tab enables you to create, modify, and delete level extensions for a fact.. All facts have a definition and column alias, but level extensions for facts are optional. You already know about the first two tabs from the Project Design Essentials course. You will learn more about extensions later in this course.. © 2014 MicroStrategy Inc.. Review of Schema Objects. 25.

(26) 1. Introduction to Advanced Project Design. MicroStrategy Architect: Advanced Project Design. . Lesson Summary In this lesson, you learned: •. The project design process involves the following steps: designing the logical data model, designing the data warehouse schema, creating the project in MicroStrategy Architect, and managing the project schema.. •. As part of managing the project schema, you will learn how to define primary and secondary database instances, understand aggregation-awareness, create data marts, enable multisource report execution, and use fact editors.. •. The following basic schema objects form the foundation of a MicroStrategy project: tables, facts, attributes, and hierarchies.. •. You can also create other types of schema objects in MicroStrategy —such as transformations and partition mappings—that you use for more advanced functions.. •. The Fact Editor is one of the schema object editors available in Developer. It enables you to create or modify any type of fact or fact expression and configure a variety of fact-related settings.. 26 Lesson Summary. © 2014 MicroStrategy Inc..

(27) 2 MANAGING PROJECT SCHEMA. Lesson Description This lesson covers a variety of advanced topics that enable you to maintain a MicroStrategy project as it changes over time and help you optimize performance within your project. In this lesson, you will review the concept of primary and secondary database instances. You will then learn about options that enable you to maintain project tables. You will also learn how the MicroStrategy SQL Engine is aggregate aware. Finally, you will learn how to create aggregate fact tables using data marts. You will learn what a data mart is, how to create data mart reports and data mart tables, and how to incorporate data mart tables into a project.. © 2014 MicroStrategy Inc.. 27.

(28) 2. Managing Project Schema. MicroStrategy Architect: Advanced Project Design. Lesson Objectives After completing this lesson, you will be able to: Define the primary and secondary database instance, use the Architect graphical interface to maintain project tables, describe how the MicroStrategy SQL Engine is aggregate aware, and create aggregate fact tables using data marts.. After completing the topics in this lesson, you will be able to: •. Describe the primary and secondary database instances and their use in MicroStrategy project. (Page 29). •. Describe how the Warehouse Tables pane options in the Architect graphical interface can help you maintain a project over time. (Page 32). •. Describe how MicroStrategy Architect calculates logical table size, and how the SQL Engine uses logical table size to select the optimal table for a query. (Page 35). •. Define a data mart, and list and define data mart objects; create a data mart table by creating and executing a data mart report; list and define data mart column creation options, and use a data mart table in a project. (Page 40). 28 Lesson Objectives. © 2014 MicroStrategy Inc..

(29) MicroStrategy Architect: Advanced Project Design. 2. Managing Project Schema. Review of Database Instances After completing this topic, you will be able to: Describe the primary and secondary database instances and their use in MicroStrategy project.. A database instance is the logical object in the MicroStrategy metadata that typically represents a connection to a data warehouse. Whenever you run a report, you connect to the data warehouse using the DSN, login, and password stored as part of the database instance definition. learned how to create a database instance in the MicroStrategy You Architect: Project Design Essentials course. you are using security features such as warehouse authentication or Ifconnection mapping, different users may access the same data. warehouse using different DSNs or logins. However, even in these cases, the project database instance is still associated with a default DSN and login.. Although a project uses a single primary database instance to access the data warehouse, you can create any number of secondary database instances that point to a variety of data sources. You can then use these database instances for other tasks such as creating data marts and Freeform SQL or Query Builder reports. create and configure database instances, you must have the Toappropriate administrative privileges. more information about Freeform SQL reports, refer to the For MicroStrategy Freeform SQL Essentials course. also create and configure a database instance to connect to an MDX You data source. For more information on MDX reports, refer to the MDX Cube Reporting Guide product manual.. © 2014 MicroStrategy Inc.. Review of Database Instances. 29.

(30) 2. Managing Project Schema. MicroStrategy Architect: Advanced Project Design. Primary/Secondary Database Instances at the Project Level By default, the database instance you select during the project creation process becomes the project’s primary database instance. All standard reports in a project execute against the data source defined in the primary database instance by default. Each MicroStrategy project must have a primary database instance. However, you can associate any number of secondary database instances with a single project. If you want to use a secondary database instance in a project to create data marts, Freeform SQL, Query Builder, or MDX reports, you must first explicitly associate it with the project. When you create a non-standard report, it then executes SQL against the database instance to which it points. can also associate a secondary database instance with a project You automatically, by adding a table from the secondary database instance to a project using MultiSource Option. MultiSource Option is only available with three tier projects. You will learn about MultiSource Option later in this course.. To associate a secondary database instance with a project:. 1 In Developer, log in to the project source that contains your project. 2 Using the Database Instances manager, create the database instance that points to the secondary data source. learn how to create database instances, refer to the MicroStrategy ToArchitect: Project Design Essentials or MicroStrategy Administration: Configuration and Security courses.. 3 Right-click the project name and select Project Configuration. 4 In the Project Configuration Editor, in the Categories list, expand the Database instances category. 5 In the Available Data Mart, Query Builder, Freeform and non-primary warehouse database instances list, select the database instance that you want to associate to the project.. You can also create new database instance by clicking New. 30 Review of Database Instances. © 2014 MicroStrategy Inc..

(31) MicroStrategy Architect: Advanced Project Design. Managing Project Schema. 2. 6 In the message window, click No. window asks you if you want to configure the data mart This optimization for the database instance. You do not have to configure data mart optimization if you do not plan to use that database instance to create data marts. For information on database optimization, see “Data Mart Optimization” starting on page 44.. 7 Click OK to close Project Configuration Editor. The following image shows the Project Configuration Editor with a single primary database instance and multiple secondary database instances associated with the MicroStrategy Tutorial project: Primary and Secondary Database Instances. Whether you create a standard or a non-standard report, it always executes SQL against a single data source (database instance). If you want to combine data from multiple data sources, you can then create a Report Services document and include standard and non-standard reports that connect to different data sources as its datasets. However, you cannot access two distinct data sources within a single standard or non-standard report unless you use MultiSource Option, which is covered later in this lesson.. © 2014 MicroStrategy Inc.. Review of Database Instances. 31.

(32) 2. Managing Project Schema. MicroStrategy Architect: Advanced Project Design. Maintaining Warehouse After completing this topic, you will be able to: Describe how the Warehouse Tables pane options in the Architect graphical interface can help you maintain a project over time.. In the MicroStrategy Architect: Project Design Essentials course, you learned how to create a MicroStrategy project. However, a project grows and changes over time as the volume of data or number of users increase, new requirements arise, or existing requirements change. You need to be able to adequately maintain a project throughout all stages of its life cycle. MicroStrategy Architect provides the functionality to assist you with project maintenance. also has a variety of administration functionalities MicroStrategy designed to help you with project maintenance. For more information,. see the MicroStrategy Administration: Application Management course.. You already learned how to use the Warehouse Tables pane in Architect graphical interface to select the data warehouse tables you want to use in a MicroStrategy project. Now, you will learn about other options available that enable you to maintain a project.. Maintaining Tables After you add warehouse tables to a project and create schema and application objects, you may find that the warehouse schema changes over time. The database administrator may alter the structure of a table, for example, by adding additional columns. Some table sizes may grow over time, while others remain the same, making them better candidates for aggregate queries. Some tables may become obsolete and may be removed from the warehouse.. 32 Maintaining Warehouse. © 2014 MicroStrategy Inc..

(33) MicroStrategy Architect: Advanced Project Design. Managing Project Schema. 2. The Warehouse Tables pane in Architect graphical interface enables you to maintain the integrity of the logical tables with the data warehouse structure. It provides a variety of options that apply to the project tables on an individual basis. You access these options in the Warehouse Tables pane by right-clicking any table you have added to a project. The following image displays these table options: Table Options. The Warehouse Tables pane provides the following options for individual tables: •. Update Structure—If the table structure has changed since you added the table to the project, you can click Update Structure to force MicroStrategy Architect to recognize the changes.. •. Show Sample Data—This option enables you to view the first 100 rows of data in a table.. •. Select Database Instance—This option enables you to add additional database instances associated with the project.. © 2014 MicroStrategy Inc.. Maintaining Warehouse. 33.

(34) 2. Managing Project Schema. MicroStrategy Architect: Advanced Project Design. can view a table’s structure before you add it to the project. To show You the columns in a table, click expand.. 34 Maintaining Warehouse. © 2014 MicroStrategy Inc..

(35) MicroStrategy Architect: Advanced Project Design. Managing Project Schema. 2. Aggregation Awareness After completing this topic, you will be able to: Describe how MicroStrategy Architect calculates logical table size, and how the SQL Engine uses logical table size to select the optimal table for a query.. Review of Base and Aggregate Fact Tables In the MicroStrategy Architect: Project Design Essentials course, you learned that there are two types of fact tables that you typically have in a data warehouse. 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. For example, consider the following two fact tables: Base and Aggregate Fact Tables. The FACT_SALES table stores dollar and unit sales data at the lowest possible level of detail—by item, employee, and date. Therefore, it is the base fact table for these two facts. The FACT_SALES_AGG table stores dollar and unit sales data at a higher level of detail—by category, region, and month. Therefore, it is an aggregate fact table for these two facts. Because they store data at a higher level, aggregate fact tables reduce query time. For example, if you want to view a report that shows unit sales by region, you can obtain the result set more quickly using the FACT_SALES_AGG table than the FACT_SALES table.. © 2014 MicroStrategy Inc.. Aggregation Awareness. 35.

(36) 2. Managing Project Schema. MicroStrategy Architect: Advanced Project Design. In a data warehouse, you often have multiple aggregate fact tables for the same fact or set of facts to enable you to more quickly analyze fact data at various levels of detail. guidelines on building aggregate fact tables, refer to the For MicroStrategy Advanced Data Warehousing course.. Using Aggregate Tables in a Project There are two actions that you need to perform to integrate an aggregate fact table into an existing project: 1 Add the table to the project using the Warehouse Tables pane in Architect graphical interface. 2 If necessary, map the existing attributes and facts to the aggregate table. If your aggregate fact table structure is consistent with your base fact table structure, MicroStrategy Architect will automatically add the table to the definitions of your existing attributes and facts. However, if your aggregate fact table structure contains new columns that have not been mapped to existing attribute form expressions and fact expressions, you must manually map the new table to the desired attributes and facts. the aggregate fact table structure matches the base fact table, IfMicroStrategy Architect can automatically map the new table to existing attributes and facts as long as automatic mapping is used for the corresponding attribute form expressions and fact expressions.. How Is MicroStrategy Aggregate-Aware? At this point, you should understand how MicroStrategy Architect becomes aware of aggregate fact tables. However, the question remains as to how MicroStrategy Architect knows to use the aggregate table rather than the base fact table in cases where either table can provide the answer.. 36 Aggregation Awareness. © 2014 MicroStrategy Inc..

(37) MicroStrategy Architect: Advanced Project Design. Managing Project Schema. 2. MicroStrategy Architect assigns a size to every table when you initially add them to a project. These size assignments are stored in the metadata. MicroStrategy Architect assigns sizes based on the columns in the tables and the attributes to which those columns correspond. Because MicroStrategy Architect uses the logical attribute definitions to assign a size to each table in the project, this measurement is referred to as logical table size. The following illustration is a visual representation of the algorithm used by MicroStrategy Architect in assigning logical table sizes: Calculating the Logical Table Size. Logical table size is the sum of the weight for each attribute contained in the table. Attribute weight is defined as the position of an attribute in its hierarchy divided by the number of attributes in the hierarchy, multiplied by a factor of 10. Using this formula, MicroStrategy Architect calculates the respective weight of each attribute as shown in the illustration above. The logical table size of each fact table is simply the sum of its respective attribute weights. You can view the logical table size for each table in the Logical Table Editor. When the SQL Engine can obtain data from two or more tables in the warehouse, it looks at the logical table size and generates SQL against the table with the smallest logical table size. This process helps the SQL Engine select the optimal table for a query.. © 2014 MicroStrategy Inc.. Aggregation Awareness. 37.

(38) 2. Managing Project Schema. MicroStrategy Architect: Advanced Project Design. Changing the Logical Table Size At times, you may need to reassign the logical table size for a table. For example, in the previous illustration for logical table sizes, there are two aggregate fact tables that both have the same logical table size of 15. However, one of these tables contains item and region information, and the other one has class and store information. Clearly, based on the attributes they contain, the table with item and region information is larger. There are many more items than classes to which items belong. In this example, where the logical table size is the same but the physical size is actually very different, you can change the logical table size automatically assigned by MicroStrategy Architect. Generally, smaller logical size does equate to smaller physical size. Tables with higher-level attributes usually have a smaller logical table size than tables with lower-level attributes. However, there are times when this is not the case due to the particular combination of attributes in a table. In such cases, you have to change the logical table size to force the SQL Engine to use the table that you know has a smaller physical size. To change the logical table size for a table:. 1 In Architect graphical interface, on the Project Tables view tab, select a table. 2 In the Properties pane, in the Definition section, click the Logical Size box, type the new logical table size value. lock the logical size of the table you need to access Logical Size ToEditor. You cannot lock the size of the table from the Properties pane. To change logical table sizes and lock the table sizes using the Logical Size Editor:. 1 On the Design tab, in the Editors section, click Edit logical size of tables button.. 38 Aggregation Awareness. © 2014 MicroStrategy Inc..

(39) MicroStrategy Architect: Advanced Project Design. Managing Project Schema. 2. 2 In the Logical Size Editor, for the table you want to modify, in the Size value box, type a new logical table size value. 3 If you want to preserve the logical table size of a table, select its Size locked check box. 4 Click OK to close the Logical Size Editor. The following image shows the Logical Size Editor: Changing Logical Size in the Logical Size Editor. should select Size Locked option if you want to ensure that the You logical size you have selected is not overwritten by MicroStrategy. Architect during updates of the project schema. When you update the project schema, you can choose to update logical table sizes. You may need to perform this action for other tables. Selecting this option allows you to update the logical sizes of other tables while preserving the sizes of tables that you have manually assigned.. 5 Click Save and Close. 6 Update the project schema.. © 2014 MicroStrategy Inc.. Aggregation Awareness. 39.

(40) 2. Managing Project Schema. MicroStrategy Architect: Advanced Project Design. Data Marts After completing this topic, you will be able to: Define a data mart, and list and define data mart objects; create a data mart table by creating and executing a data mart report; list and define data mart column creation options, and use a data mart table in a project.. What Is a Data Mart? A data mart is a relational table containing results of a report. You create the data mart report in Developer and save the data mart table in a warehouse of your choice. After you create a data mart table, you can add it to a project and use it as a source table. Common applications for data marts include: •. Creating aggregate fact tables. •. Creating tables for very large result sets and then using other applications such as Microsoft Excel or Microsoft Access to access the data. •. Creating tables for off-line analysis. In this lesson, you will use data marts to create aggregate fact tables. can use data marts in other usage scenarios. Combining data marts You with MicroStrategy data mining features or with Freeform SQL reports are two such scenarios.. 40 Data Marts. © 2014 MicroStrategy Inc..

(41) MicroStrategy Architect: Advanced Project Design. Managing Project Schema. 2. For example, consider the following scenario: Base Fact Table. In this example, forecasting data is stored at the employee and date level in the FORECAST_SALES base fact table. However, you want to report on the Forecast Unit Sold at the Region level. This requires three joins from the fact table to the LU_REGION lookup table. In addition, the FORECAST_SALES table may have millions of rows. This query may be very costly, especially if users request it often. What if you could create an aggregate table that limits the number of joins and the number of rows in the fact table? You can achieve this by creating a data mart table. You can then bring this table into your project, map the Forecast Unit Sales and metric to it, and have your region-level reports automatically use it, as shown below: Aggregate Fact Table Created as Data Mart. © 2014 MicroStrategy Inc.. Data Marts. 41.

(42) 2. Managing Project Schema. MicroStrategy Architect: Advanced Project Design. Data Mart Objects Creating data marts involves creating two objects: •. Data mart report—This is a metadata object that you create in the Report Editor. When executed, the data mart report creates the data mart table in the warehouse of your choice. The data mart report contains attributes, metrics, and other application objects that translate into columns in the data mart table.. •. Data mart table—This is the relational table created after the execution of a data mart report.. Data Mart Database Instances When you create a data mart report, you must specify a database instance in which to create the data mart table. You create a data mart in a database instance in one of the following ways: •. Option 1—Use the project’s primary database instance.. •. Option 2—Use a secondary project database instance that exists in the same warehouse as the primary project database instance.. •. Option 3—Use a different database instance than the project, and one that is in a different warehouse than the primary project database instance.. 42 Data Marts. © 2014 MicroStrategy Inc..

(43) MicroStrategy Architect: Advanced Project Design. Managing Project Schema. 2. The following figure illustrates each of these data mart database instance options: Data Mart Database Instance Options. If you use the primary project database instance, then you do not need to take any additional steps to create a data mart. You simply select the primary data mart database instance as a target when you create the data mart report.. © 2014 MicroStrategy Inc.. Data Marts. 43.

(44) 2. Managing Project Schema. MicroStrategy Architect: Advanced Project Design. If you plan to use a secondary project database instance, then you must create that database instance before creating the data mart. You then associate this database instance to the project in the Project Configuration Editor. instructions on how to associate a secondary database instance to a For project, see “To associate a secondary database instance with a project:” starting on page 30.. Data Mart Optimization When you associate a secondary database instance to a project in the Project Configuration Editor, a message window displays prompting you to configure data mart optimization: Data Mart Optimization Warning Message. message does not display if you have enabled data mart This optimization for the data mart database instance before you associated this database instance to a project.. When you click Yes, the Database Instances editor for the data mart database instance opens with the Advanced tab automatically selected. mart optimization occurs when you create a data mart in the Data primary project database instance or in a database instance that points to the same data warehouse as the primary project database instance.. To optimize a database instance:. 1 In the Database Instances editor, on the Advanced tab, under Data mart optimization, select the This database instance is located in the same warehouse as check box.. 44 Data Marts. © 2014 MicroStrategy Inc..

(45) MicroStrategy Architect: Advanced Project Design. Managing Project Schema. 2. the data mart database instance does not reside in the same Ifwarehouse as the project database instance, do not select this check box.. 2 In the list of database instances, select the primary project database instance. 3 Click OK. The following image shows the data mart optimization option for the Forecast Data database instance residing in the same warehouse as the Tutorial Data: Data Mart Optimization Option. Why Optimize? When you create a data mart using the primary project database instance or using a database instance that resides in the same warehouse as the primary project database instance, you simplify the SQL that is generated to create the data mart. You also conserve the Intelligence Server machine resources by minimizing the memory footprint.. © 2014 MicroStrategy Inc.. Data Marts. 45.

(46) 2. Managing Project Schema. MicroStrategy Architect: Advanced Project Design. The following table displays sample SQL generated when creating a data mart using a database instance in the same data warehouse as the primary project database instance, and when using a different data warehouse: Sample SQL Same Data Warehouse. Different Data Warehouse. drop table Same_ Datamart _Instance. select a11.Month_Id Month_Id, max(a12.Month_Desc) Month_Desc, sum(a11.Tot_Dollar_Sales) DOLLARSALES. create table Same_ Datamart _Instance (Month_Id INTEGER, Month_Desc VARCHAR(100), DOLLARSALES FLOAT). from MNTH_CATEGORY_SLS a11 join LU_MONTH a12 on (a11.Month_Id = a12.Month_Id). insert into Same_Datamart_Instance. group by a11.Month_Id. select a11.Month_Id Month_Id, max(a12.Month_Desc) Month_Desc, sum(a11.Tot_Dollar_Sales) DOLLARSALES. drop table Different_ Datamart _Instance. from MNTH_CATEGORY_SLS a11 join LU_MONTH a12 on (a11.Month_Id = a12.Month_Id). create table Different_ Datamart _Instance (Month_Id INTEGER, Month_Desc VARCHAR(100), DOLLARSALES FLOAT) insert into Different_ Datamart _Instance values 201201, 'Jan 2012', 8817). When you create the data mart in the same data warehouse, MicroStrategy Intelligence Server creates the data mart table in the project warehouse and then inserts the result data rows directly into the table. When you create the data mart in a different data warehouse, MicroStrategy Intelligence Server extracts the results from the project data warehouse with a SELECT statement and brings the result set into the Intelligence Server machine’s memory. It then creates the data mart table in the different data warehouse and inserts the results.. Creating Data Marts In Developer, you create a data mart report by converting an existing report or by creating a new report.. 46 Data Marts. © 2014 MicroStrategy Inc..

(47) MicroStrategy Architect: Advanced Project Design. Managing Project Schema. 2. To create a data mart:. 1 In the Report Editor, on the Data menu, select Configure Data Mart. report template must contain an attribute, a metric, or some The other object for this option to be enabled. 2 In the Report Data Mart Setup window, on the General tab, in the Data mart database instance drop-down list, select the database instance in which you want to create the data mart table. 3 In the Table name box, type the name of the data mart table you want to create.. The table name you type is not validated by the system at this point.. By default, the This table name contains placeholders check box is selected. The selection of this check box enables you to specify whether the data mart table uses placeholders to name the table. Placeholder names enable you to modify table names dynamically. The following table lists placeholders available for naming data mart tables . Data Mart Placeholders Placeholder Replacement Option. © 2014 MicroStrategy Inc.. !U. user name. !D. date on which table was created. !O. report name. ???. temporary table name. !!!. all column names. !a. attribute column names. !j. job ID. !r. report GUID. !t. timestamp. !p. project name. !z. project GUID. !s. user session GUID. Data Marts. 47.

(48) 2. Managing Project Schema. MicroStrategy Architect: Advanced Project Design. 4 Select one of the following options: •. Create a new table. •. Append to existing table—This option enables you to add the data mart report results to an existing table. The name specified in the Table name box must be the name of the existing table you want to append.. 5 On the Advanced tab, specify data mart governors and table creation properties. 6 On the SQL Statements tab, specify SQL statements that can be inserted before and after the table is created or before data is inserted in the table. information on the data mart governors, table creation For properties, and SQL statements refer to the Advanced Reporting Guide product manual.. 7 Click OK to close the Report Data Mart Setup window. may see a warning that data mart tables created in common table You spaces may overwrite someone else’s data mart table. If you want to proceed, click OK.. 8 Save the report. 9 Execute the data mart report to create the data mart table. 10 Update project schema. For example, using the Forecasting Project you can create a data mart report that contains the Region and Year attributes and the Forecast Units Sold metric, as shown in the image below: Data Mart Report Definition. 48 Data Marts. © 2014 MicroStrategy Inc..

(49) MicroStrategy Architect: Advanced Project Design. Managing Project Schema. 2. You can then convert this report into a data mart. The following image shows the Report Data Mart Setup Window with data mart report configured as REGION_YEAR_FORECAST_UNIT_SALES table in the Forecast Data database instance: Report Data Mart Setup Window. The following image shows a message displayed by the data mart report when it is executed: Data Mart Execution Complete Message. © 2014 MicroStrategy Inc.. Data Marts. 49.

(50) 2. Managing Project Schema. MicroStrategy Architect: Advanced Project Design. Data Mart Options A data mart table has the same structure as any other data warehouse table. By default, it contains columns corresponding to all attribute forms and metric columns present on the report template. You can control the structure of a data mart table in the following ways: •. You can control what attribute columns are included in the data mart table.. •. You can determine the names for the columns that contain the metric calculations.. Attribute Columns A data mart table contains an attribute ID column for each attribute selected in the data mart report. Additionally, depending on the default display for each attribute in the data mart report, the data mart table can also include attribute description columns. you would remove any non-ID form descriptions from the Generally, data mart report display to avoid storing duplicate attribute descriptions in the data mart table.. Consider the data mart report from the previous example that has the Region and Year attributes and the Forecast Units Sold metric on the template. Assuming that the default display for the Region attribute is ID and description, and for Year is ID, when the data mart report is executed, the data mart table contains the following columns: •. REGION_ID. •. REGION_NAME. •. YEAR_ID. •. WJXBFS1. If you do not want to include attribute description columns in your data mart table to improve query performance, you must modify the attribute display and forms available in report objects for each attribute in the data mart report.. 50 Data Marts. © 2014 MicroStrategy Inc..

(51) MicroStrategy Architect: Advanced Project Design. Managing Project Schema. 2. To modify the attribute display in a data mart report:. 1 In the Report Editor, on the Data menu, select Attribute Display. 2 In the Attribute Display window, in the Attribute drop-down list, select the attribute whose display you want to modify. 3 Under Select one of the display options below, click Use the following attribute forms. 4 In the Available forms list, select the ID form. 5 Click the upper > button to move the ID form to the Displayed forms list. 6 In the Displayed forms list, select all non-ID forms. 7 Click the upper < button to remove the non-ID forms from the report display. 8 In the Report objects forms list, select all non-ID forms. 9 Click the lower < button to remove the non-ID forms from the report objects. 10 Click OK.. © 2014 MicroStrategy Inc.. Data Marts. 51.

(52) 2. Managing Project Schema. MicroStrategy Architect: Advanced Project Design. The following image shows the Attribute Display window with the Region attribute configured to use only the ID form: Attribute Display Options. 52 Data Marts. © 2014 MicroStrategy Inc..

(53) MicroStrategy Architect: Advanced Project Design. Managing Project Schema. 2. Using the previous example, after you change the display for the Region attribute from description to ID only, and then execute the data mart report, the data mart table contains the following columns: •. REGION_ID. •. YEAR_ID. •. WJXBFS1. Metric Column Alias A data mart table contains a column that corresponds to each metric selected in the data mart report. These columns, created from metric calculations, become the fact columns. By default, the alias generated for a fact column in a report SQL is WJXBFS<n>, where n is a number. The first metric alias MicroStrategy Engine creates for a report is WJXBFS1, the next WJXBFS2, and so forth. If you want to use a different name, you can create a column alias for the fact column that contains the metric calculation. You specify the column alias in the Metric Editor of the metric on which the column is based. To name a fact column in a data mart table:. 1 In the Metric Editor, on the Tools menu, point to Advanced Settings and select Metric Column Options. 2 In the Metric Column Alias Options window, in the Column Name used in table SQL creation box, type a name for the metric column. 3 In the Data type drop-down list, select the data type and, if appropriate, define other relevant parameter setting(s).. © 2014 MicroStrategy Inc.. Data Marts. 53.

(54) 2. Managing Project Schema. MicroStrategy Architect: Advanced Project Design. 4 Click OK to close the Metric Column Alias Options window. 5 Save and close the metric. 6 Update project schema. The following image shows a custom Total_Unit_Sales column alias for the Forecast Units Sold metric: Metric Column Alias Options Window. Using the same example, after you change the column alias for the Forecast Revenue metric, and then execute the data mart report, the data mart table contains the following columns: •. REGION_ID. •. YEAR_ID. •. Total_Unit_Sales. Using Data Marts in a Project After you create the data mart report and execute it, the data mart table (with report result set) is created in the data warehouse. This table is like any other physical data warehouse table. To use a data mart table as a source table in the project in which the data mart was created, you must first add the table to the project, then update the appropriate fact, and finally update the project schema.. 54 Data Marts. © 2014 MicroStrategy Inc..

(55) MicroStrategy Architect: Advanced Project Design. Managing Project Schema. 2. Adding the Data Mart table to the Project:. 1 In Developer, on the Schema menu, select Architect. 2 On the Warehouse Tables pane, expand the Forecast Data. You can now see the data mart table. 3 Right-click the data mart table and select Add Table to Project. 4 The Results Preview window shows attributes and facts that will be created. In the Results Preview window, in the Fact tab, clear the check box for the facts. might be scenarios where you want to keep the facts. If you want There to create facts, ensure you select the appropriate fact check box in the Fact tab of the Results Preview window.. 5 Click OK. 6 On the toolbar, click Save and Close. The following image shows the REGION_YEAR_FORECAST_UNIT_SALES table added to the project: REGION_YEAR_FORECAST_UNIT_SALES Added to the Project. Updating the Fact To use the data mart table as a source table from which to execute reports, you must update the fact on which the metric used to create the data mart table is based.. © 2014 MicroStrategy Inc.. Data Marts. 55.

(56) 2. Managing Project Schema. MicroStrategy Architect: Advanced Project Design. To update the fact expression:. 1 In Architect graphical interface, edit the fact that is used in the metric of the data mart report. relationship of the metric to the fact on which it is based is The referred to as a child dependency. In Developer, you can use. MicroStrategy Object Manager to quickly locate child dependencies for the metric.. 2 In the Project Tables view tab, locate the table that contains the fact and right-click to edit the fact on which the data mart metric is based. 3 In the Fact Editor, create a new fact expression that uses the data mart table as a source table. the fact column in the data mart table is named the same as the Ifunderlying fact, you may select the Automatic mapping method for the new fact expression. By default, when you create a data mart table, the fact has a unique name that is different from the other facts in the project.. 4 Click OK.. 56 Data Marts. © 2014 MicroStrategy Inc..

(57) MicroStrategy Architect: Advanced Project Design. Managing Project Schema. 2. The following image shows the Forecast Units Sold fact mapped to the data mart aggregate table: Mapping a Fact to a Data Mart Table. Updating the Project Schema After you add the data mart table to a project and update the appropriate fact object, you must also update the schema logical information in the metadata.. © 2014 MicroStrategy Inc.. Data Marts. 57.

(58) 2. Managing Project Schema. MicroStrategy Architect: Advanced Project Design. . Lesson Summary In this lesson, you learned: •. The database instance you select during the project creation process becomes this project’s primary database instance.. •. You can associate any number of secondary database instances with a single project. You use secondary database instances to create data marts, Freeform SQL, Query Builder, and MDX reports. You associate secondary database instances with a project using the Project Configuration Editor.. •. The Warehouse Tables pane in Architect graphical interface enables you to maintain the integrity of the logical tables with the data warehouse structure by providing a variety of options that apply to the project tables on an individual basis.. •. 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.. •. To use aggregate tables in the project, you first add the table to the project using the Warehouse Tables pane in Architect graphical interface. If necessary, you also map the existing attributes and facts to the aggregate table.. •. MicroStrategy Architect assigns a logical table size to every table in a project when you initially add them to the project and stores these size assignments in the metadata. It assigns sizes based on the columns in the tables and the attributes to which those columns correspond.. •. Logical table size is the sum of the weight for each attribute contained in the table. Attribute weight is defined as the position of an attribute in its hierarchy divided by the number of attributes in the hierarchy multiplied by a factor of 10.. •. You can change the logical table size either in the Logical Size Editor or from the Properties pane in Architect graphical interface.. •. A data mart is a relational table containing a report result set. A data mart consists of two objects: the data mart report and the data mart table.. •. You can use data marts to create aggregate tables and tables based on large report result sets.. 58 Lesson Summary. © 2014 MicroStrategy Inc..

No results found