Search This Blog

Loading...

Tuesday, March 9, 2010

FAQS

What is Data warehouse?

What is BI Tool?

What is ETL?

What is OLAP?

What is Meta Data?

What is the Difference b/w Microstrategy and cognos Reporting Tool?

cognos reportnet is only a web-based product but microstrategy is a web-based as well as desktop based.


1. Which of the following object is not under Schema Object?

A. Attribute
B. Consolidation and Custom Group
C. Transformation
D. Fac

2. Which of the following object is not under Public Object?

A. Metric
B. Drill Maps
C. Transformation
D. Templates

3. Which of the following object, we can’t put directly in the report?

A. Metric
B. Fact
C. Filter
D. Prompt

4. How many types of metrics can be created in MicroStrategy based on their formula?

A. 2
B. 3
C. 4
D. 5

5. MicroStrategy is basically a ____ tool.

A. MOLAP
B. ROLAP
C. HOLAP
D. None of the above.

6. Where we can view the System hierarchy?

A. In the Data Explorer only
B. In the Hierarchy Viewer only
C. Both A and B
D. None of the above

7. A compound metric should have –

A. Filter
B. Transformation
C. Dimensionality
D. None of the above


8. A simple metric should have

A. At least one Group function.
B. At least one filter
C. At least one Transformation
D. All of the above

9. Nested metric is special type of –

A. Simple Metric
B. Compound Metric
C. Derived Metric
D. None of the above

10. A simple metric can have more than one Transformation.

A. Yes
B. No

11. What is the default dimensionality of metric?

A. Level of the fact present in the fact table
B. Report level
C. All possible levels
D. None of the above

12. Smart Metrics option only available in _______

A. Simple Metrics
B. Compound Metrics
C. Both A and B
D. None of the above

13. A metrics can be created using

A. Only Fact
B. Fact and Attribute
C. Fact and Metric
D. All of the above

14. Which of the following type of metric is not reusable across reports?

A. Simple Metrics
B. Compound Metrics
C. Derived Metrics
D. Nested Metrics

15. A simple metric should have

A. A formula and at least one group function
B. A level and a formula
C. At least one group function
D. All of the above

16. For which of the following reason, a metric can be called as smart metrics?

A. When a metric capable to calculate data irrespective of data type of the fact column.
B. When a metric calculate data without hitting the fact table.
C. When a metric calculate data using the non-aggregatable fact.
D. None of the above.



17. Which is not a component of Metrics?

A. Level
B. Conditionality
C. Prompt
D. Transformation

18. Which of the following metric component is related to “Group By” clause of Report SQL generated at run time of report?

A. Formula
B. Level
C. Conditionality
D. Transformation

19. Which of the following metric component falls into the “Where” clause of Report SQL generated at run time of report?

A. Formula
B. Level
C. Conditionality
D. None of the above.

20. Which of the following metrics always calculated by the Analytical Engine?

A. Compound Metrics
B. Derived Metrics
C. Simple Metric
D. Smart metrics


21 What is the difference b/w report filter and view filter?

Report Filter:

A report filter conditions appear in SQL used to retrieve the report result set from the database. The report filter is created as part of the report and is saved with the report definition.

View Filter:

View filters do not modify the SQL for the report like normal report filters do. Instead, view filters are applied to the overall result set after the SQL is executed and results are returned from the data warehouse.

22 What is smartmetric?

Consider a compound metric with formula sum (profit/units ) where profit and units are simple metrics. It will give the whole sum of the level. but if you want the total to be done individually..Say sum(profit)/sum(units) then check in the smart metrics.

Report Execution

Processing of report execution
Reports are perhaps the most common requests made of Intelligence Server. All report requests have the following pieces:
• A report instance is a container for all objects and information needed and produced during report
execution including templates, filters, prompt answers,
generated SQL, report results, and so on.
• A task list is a list of tasks that must be accomplished to complete a job. All jobs have a task list associated with them. Intelligence Server coordinates the report instance being passed from one internal Intelligence Server component to another as a report is executed.

Analytical Engine Server:
Performs complex calculations on a result set returned from the data warehouse, such as statistical
and financial functions. Also, sorts raw results returned from the Query Engine into a cross-tabbed
grid suitable for display to the user. In addition, it performs subtotal calculations on the result set.
Depending on the metric definitions, the Analytical Engine will also perform metric calculations that were
not or could not be performed using SQL, such as complex functions.

Metadata Server Controls all access to the metadata for the entire project.
Object Server Creates, modifies, saves, loads and deletes objects from metadata. Also maintains a server cache of recently used objects.Tthe Object Server does not manipulate metadata directly. The Metadata Server does all reading/writing from/to the metadata; the Object Server uses the Metadata Server to make any changes to the metadata.

Query Engine Sends the SQL generated by the SQL Engine to the data warehouse for execution.

Report Server Creates and manages all server reporting instance objects. Maintains a cache of executed reports.

Resolution Server Resolves prompts for report requests. Works in conjunction with Object Server and Element Server to retrieve necessary objects and elements for a given request.

SQL Engine Server Generates the SQL needed for the report.

Steps For Processing of Report Execution.



1 Intelligence Server receives the request.
2 The Resolution Server checks for prompts. If the report has one or more prompts, the user must answer them.
3 The Report Server checks the internal cache, if the caching feature is turned on, to see whether the report results already exist. If the report exists in the cache, Intelligence Server skips directly to the last step and delivers the report to the client. If no valid cache exists for the report, Intelligence Server creates the task list necessary to execute the report.
Prompts are resolved before the Server checks for caches. Users can retrieve results from cache even if they have personalized the report with their own
prompt answers.
4 The Resolution Server obtains the report definition and any other required application objects from the Object Server. The Object Server retrieves these objects from the object cache, if possible, or reads them from the metadata via the Metadata Server. Objects retrieved from metadata are stored in the object cache.
5 The SQL Generation Engine creates the optimized SQL specific to the RDBMS being used in the data warehouse. The SQL is generated based on the definition of the report and associated application objects retrieved in the previous step.
6 The Query Engine runs the SQL against the data warehouse. The report results are returned to Intelligence Server.
7 The Analytical Engine performs additional calculations as necessary. For most reports, this includes cross-tabbing the raw data and calculating subtotals. Some reports may require additional calculations that cannot be performed in the database via SQL.
8 Depending on the analytical complexity of the report, the results might be passed back to the Query Engine for further processing by the database until the final report is ready (in this case, steps 5–7 are repeated).
9 Intelligence Server’s Report Server saves or updates the report in the cache, if the caching feature is turned on, and passes the formatted report back to the client, which displays the results to the user.

Monday, March 1, 2010

Transformations

Suppose you want to compare how much revenue your company grew last year to how much it grew this year. This type of analysis, called a TY/LY comparison (This Year versus Last Year), is a commonly used form of time-series analysis and is relevant to many different industries, including retail, banking, and telecommunications.

Transformations—schema objects you can create using attributes in your project—are one of the many MicroStrategy techniques used to perform time-series analysis. To calculate a variance or a growth percentage such as last year’s revenue versus this year’s revenue, it is very convenient to use a transformation. Transformations are often the most generic approach and can be reused and applied to other time-series analyses. To use a transformation, a report
designer creates a metric and applies the transformation to it.
Creating transformations
A transformation is a schema object that typically maps a specified time period to another time period, applying an offset value, such as current month minus one month.
Usually defined by a project designer, transformations are used in the definition of a metric to alter the behavior of that metric. Such a metric is referred to as a transformation metric. For example, time-related transformations are commonly used in metrics to compare values at different times, such as this year versus last year or current date versus month-to-date. Any transformation can be included as part of the definition of a metric and multiple transformations can be applied to the same metric. Transformation metrics are beyond the scope of this guide;

Recall the example used in the introduction, the TY/LY comparison. To calculate this year’s revenue, you can use the Revenue metric in conjunction with a filter for this year.
Similarly, to calculate last year's revenue, you can use the Revenue metric in conjunction with a filter for last year. However, a more flexible alternative is to use a previously created Last Year transformation in the definition of a new metric, last year’s revenue. With a single filter, on 2003 for example, the two metrics Revenue and Last Year Revenue give you results for 2003 and 2002, respectively. Since a transformation represents a rule, it can describe the effect of that rule for different levels. For instance, the Last Year transformation intuitively describes how a specific year relates to the year before. It can in addition express how each month of a year corresponds to a month of the prior year. In the same way, the transformation can describe how each day of a year maps to a day of the year before. This information defines the transformation and abstracts all cases into a generic concept. That is, you can use a single metric with a
last year transformation regardless of the time attribute contained on the report. While transformations are most often used for discovering and analyzing time-based trends in your data, not all transformations have to be time-based. An example of a non-time-based transformation is This Catalog/Last Catalog, which might use Catalog_ID-1 to perform the transformation.

Expression-based versus table-based transformations
The definition of the association between an original value and a transformed one can be represented in an expression that uses columns of the warehouse, constants, arithmetic operators, and mathematical functions. This is known as an expression-based transformation. However, it is sometimes desirable to precalculate these values and store them in a table designed for the transformation. This method is sometimes referred to as a table-based transformation. The advantage of a table-based transformation is the possible use of indexing to speed query times. Another advantage is that table-based transformations provide additional flexibility beyond what formula expressions can produce. The drawback of this kind of transformation is that it requires the creation and management of an additional table in the warehouse. However, once the table is created, it usually significantly decreases the query time. Returning to the
TY/LY example, you have the option of using a simple formula such as Year_ID - 1 in the definition of the transformation or precalculating the data and storing it in a
column in a table.
A table-based transformation is required when a many-to-many transformation is performed. An example is a year-to-date calculation. A significant advantage to the dynamic calculation of an expression-based transformation is that the database
administrator does not have to create and maintain a transformation table. The drawback is that the system must perform the calculation every time. A single transformation can use a combination of table-based and expression-based transformations. For example, you can create a last year transformation based on Year and Month. The ID of the Year attribute is in the format YYYY, so the transformation can use the expression Year_ID - 1. The ID for the Month attribute is in the format MonthName,’ so you cannot easily use a mathematical expression. You must use a
table instead. The following sections walk you through creating both a table-based transformation and an expression-based one.

Building a table-based transformation
The following example shows how to create a last year transformation based on a lookup table in MicroStrategy Tutorial, which pairs each year with the previous year. This transformation is used in the report displayed below, which compares revenue for this year and last year.

To create a last year transformation based on a table

1 Log in to the project source that contains your project in MicroStrategy Desktop and expand your project.

2 From the File menu, point to New, and select Transformation. The Transformation Editor opens with the Select a Member Attribute dialog box displayed.


3 Double-click Time to open the folder, then double-click Year. The Year - Define a new member attribute expression dialog box opens.

4 Select the LU_Year table from the Table drop-down list. The table's columns appear in the Available columns list.


Notice that this table contains a previous year column,
which maps this year to last year.

5 Double-click the PREV_YEAR_ID column to place it in the expression box.

6 Click OK.

7 Click Save and Close on the toolbar. Name the transformation Last Year (Table).
You have now created the transformation. A report designer can now use the
transformation in a revenue metric to calculate last year’s revenue, then create a
. report using that transformation metric to obtain last year’s revenue


Building an expression-based transformation
This example shows how to create a last year transformation using an expression rather than a table. The Year_ID is in the format YYYY, so the previous year is simply Year_ID minus one. For example, one subtracted from the year 2005 results in the previous year, 2004. This transformation is added to the report shown in the table-based transformation example above.

To create a last year transformation based on an expression

1 In MicroStrategy Desktop, from the File menu, point to New, and select Transformation. The Transformation Editor opens with the Select a Member Attribute dialog box displayed.

2 Double-click Time to open the folder, then double-click Year. The Year - Define a new member attribute expression dialog box opens.


3 Select the LU_Year table from the Table drop-down list. The table's columns appear in the Available columns list.

4 Double-click the YEAR_ID column to place it in the expression box.

5 Type -1 in the expression box. The transformation will subtract 1 from the Year ID to calculate last year’s ID.

6 Click Validate. The message “Valid expression” appears with a green check mark.


7 Click OK.
8 Click Save and Close on the toolbar. Name the transformation Last Year
(Expression). You have now created the last year transformation. A report
designer can now use the transformation in a revenue metric to calculate last year’s revenue, then add it to the report created in the previous example.


Transformation components
All transformations have the following components:

• Member attributes: This component contains the attributes to which the transformation applies, that is, the different levels to which the rule applies. For example, in the Last Year transformation in the MicroStrategy Tutorial, the member attributes are Year,
Quarter, Month, and Day.

• Member tables: These tables store the data for the member attributes. _ For an expression-based transformation, each member expression is based on a specific table,
generally the lookup table corresponding to the attribute being transformed. _ For a table-based transformation, this is the transformation table defining the relationship. For
example, in the Last Year transformation, the member tables are LU_YEAR, LU_QUARTER, LU_MONTH, and LU_DAY, for the member attributes Year,
Quarter, Month, and Day, respectively. • Member expressions: Each member attribute has a corresponding expression.

_ For an expression-based transformation, this is a mathematical expression. In the most generic case, this expression uses constants, arithmetic operators, mathematical functions, and columns from the warehouse, typically the attribute ID column. For example, you can create a Last Year transformation using Year_ID-1 as the expression.
However, many cases can exist where the data is not conducive to such calculation. For instance, if you store Month as 200001 (January 2000), you cannot subtract one and receive December 1999 as the result. _ For a table-based transformation, this is simply a
column from a specific warehouse table specifically populated with data supporting the transformation. The rule is then not encapsulated in an expression but directly in the data of the column. Since the data defines the rule, this approach provides considerable
flexibility in the transformation definition. It is particularly effective when no straightforward formula can express the rule. In fact, in the case of a many-to-many transformation, a separate table is required. For example, in the Last Year transformation, the member expressions are LY_DAY_DATE, LY_MONTH_ID, LY_QUARTER_ID, and PREV_YEAR_ID. These are all columns from the lookup tables set in the Member tables field.

• Mapping type: This component determines how the transformation is created based on the nature of the data. The mapping can be one of the following: _ One-to-one: A typical one-to-one relationship is “last year to this year.” One day or month this year maps
exactly to one day or month from last year. _ Many-to-many: A typical many-to-many relationship is year-to-date. For one date, many other dates are included in the year-to-date calculation.

tables

Logical tables are representations of the tables that are available in the data warehouse. In the MicroStrategy environment, there are three types of logical tables:
• Logical table: Created for each physical table that is imported into the project using the Warehouse Catalog. This type of logical table maps directly to a physical table in the data warehouse.
• Table alias: Created outside of the Warehouse Catalog and points directly to a physical table. A table alias can have a different name from the physical table. One physical table can have more than one table alias. Table aliases can be created for the regular logical tables described above as well as the logical views described below.
• Logical view: Does not point directly to a physical table and is defined using a SQL query against the warehouse. Once created, the logical view can be used in the same way as any other logical table, based on which schema objects, such as attributes and facts, can be defined.
When the Engine generates SQL, it instantiates the logical view by creating a common table expression or derived table expression. The SQL Engine then refers to that expression as if it were a table in the warehouse. This gives the Architect a great deal of power and flexibility to model any physical database structure in such a way that the SQL Engine is able to take advantage of it.
Creating logical tables
A logical view is one type of logical tables. When you create a logical view, you define an SQL query against a data warehouse. Once the logical view is created, you can treat it the same way as any other logical table, based on which schema objects, such as attributes and, can be defined. You can also create table aliases for logical views.
Creating logical tables (logical views) based on SQL queries allows you to model a MicroStrategy schema that cannot be supported with just the physical database structures in the warehouse. There are many common modeling scenarios that are easier to manage by using logical views, such as slowly-changing dimensions, attribute form expressions from multiple tables, consolidated dimension tables, recursive hierarchies, and so on.
1. On Desktop, from the File menu, select New and then Logical Table. Alternatively, right-click anywhere in the right panel on Desktop, select New and then Logical Table.
2. The Table Editor is displayed with the Physical View tab selected by default.
3. In the SQL Statement panel, type in your SQL statement (you can drag and drop column names from the Object Browser to insert into the statement). Notes:
It is recommended that you use derived tables to define logical views, although common table expressions (CTEs) are also supported for some databases. Please check your database for best usage.
You can insert Tab characters into the SQL statement by typing CTRL-Tab. Tab characters can make the logical view SQL easier to read when you view the report SQL.
4. Click Add to map columns returned by the SQL statement.
5. Type in the column name under Column Object. This creates a new column. Alternatively, you can also drag and drop column names from the Object Browser to the Column Object cell. By doing this, you map an existing column to the logical view. Notes:
The names of the columns must match exactly the column aliases defined in the SQL statement.
The order of the columns does not have to match the order in which the column aliases appear in the SQL statement.
6. Select a Data Type for the column by using the drop-down list.
Note: If you used an existing column in the mapping in Step 5, you inherited the data type of that column. Keep in mind that if you change the data type, that change will affect all the tables with that column.
7. Modify the Precision and Scale of the column, if applicable.
8. Use the Remove, Move Up, or Move Down options for the columns, if needed.
9. Save and Close the logical table.
10. From the schema menu, select Update Schema to ensure that the new logical table is loaded into the schema.

Setting logical table sizes
Architect assigns a size to every table in the project when you initially add them to the project and stores these size assignments in the metadata. It makes its size assignments based on the columns in the tables and the attributes to which those columns correspond. Because Architect uses the conceptual or logical attribute definitions to assign a size to each table in the project, this measurement is referred to as logical table size.
It is often desirable to reassign the logical table size to a number of tables. Architect initially assigns logical table sizes based on an algorithm that takes into account the number of attribute columns and the various levels at which they exist in their respective hierarchies.
Suppose the base fact table contains millions of rows of transaction-level detail. The other tables, on the other hand, have only higher-level or summary information. Because the levels of the attributes are lower in the base fact table, the table as a whole is assigned a higher value for the logical table size than are the summary tables with higher-level attributes.
Logically, a table with a higher-level attribute should be smaller in size. Of course, this will not always be the case in a true warehouse. As such, you may want to alter the logical table sizes of the tables based on what you know about their true relative sizes. To alter or lock the size of a single table, use the Table Editor. To alter or lock the size of one or more tables while comparing it against the sizes of all tables in the warehouse, use the Logical Size Editor.
Viewing table structures
Viewing table structures in the Table Viewer allows you to see the structure of your application. You can see an overview of the entire schema. In the logical view, the attributes and facts of each table are displayed. In the physical view, the names of the warehouse tables and their columns are shown. You can then drill into the schema details by accessing the Table Editor and the Attribute Editor.
You can also create a custom view of the tables. This does not alter the table or schema structure, but allows you to view the data in a way that is meaningful to you. You can copy the view as a metafile to import it into a different program, such as Microsoft Word or Excel.
The Table Viewer allows you to view the project schema in two views:
• The logical view displays the tables, with the attributes and facts contained in each.
• The physical view shows warehouse tables, with their columns.
Accessing the Table Viewer
You can open the Table Editor from the MicroStrategy Desktop Schema menu, pointing to Graphical View, and then choosing Tables.
Table Viewer layout
The Table Viewer initially opens in the physical view, but you can switch to the logical view. Click one of the following topics for more information on the view.
• Physical view
• Logical view
In both the physical and logical views, the color of the table denotes the table type:

• gray for lookup, or relation, tables
• blue for fact tables
• teal for transformation, aggregation, and other tables
In the default view, the lookup tables are at the top, since they define the relationships between the tables. Next are the fact tables, and at the bottom are the remaining tables. You can rearrange the view of the tables by dragging and dropping. If you save your custom view, it is displayed the next time you access the Table Viewer. To return to the default view, click Auto arrange in the toolbar.
Note: Rearranging the tables does not modify the table or schema structure.
Once you have created a diagram that suits your purposes, you can copy it as metafile to use in another program, such as Microsoft Word or Excel. To do this, select Copy as Metafile from the File menu.
The Table Viewer provides the large-scale view of the tables. To drill into the details, right-click a table and select Edit. The Table Editor opens. To access even more specific information, right-click an attribute or fact in the Table Editor and select Edit. The Attribute Editor or Fact Editor opens.
You can also see an overview of the tables in a separate window called Aerial perspective. Its decreased scale allows you to view and navigate through the entire project. For more information, refer to Aerial perspective.
You can also zoom in and out using the mode buttons on the toolbar (Normal, Zoom In, Zoom Out). The toolbar also provides a Go to function, allowing you to select an object to view. This is particularly helpful with a large or complicated project schema.
You can access the Search for Objects dialog box to find an object by right-clicking and selecting Search for objects or Search for dependents.
To view tables related to a selected table, right-click and choose Select related tables.

Partition mapping

Partition mapping is powerful and easy to use. With the Partition Mapping Editor you can create or modify metadata partitions, or view warehouse partitions.
Accessing the Partition Mapping Editor
You can access the Partition Editor from the MicroStrategy Desktop File menu by choosing New then Partition.
Note: Before you can access any editor, you must first open and log in to the Desktop interface. Next, expand the project source so that the project name is viewable. Select the project name. The New option in the File menu is inaccessible until you select the project name.
Partition Mapping Editor layout
The Partition Mapping Editor has the following pages:
Metadata Partition Mapping
From this page you can add tables to the partition mapping by clicking Add. You can set the partition mapping logical size by clicking the arrows at the bottom of the page. The table's logical size is used by the engine to pick the best, or smallest, table when accessing data.
The Data slice definition box lists the data slices already defined for the partition mapping, if any exist. After adding partition tables, click Define to open the Data Slice Editor, where you can add new data slices. You must select tables before you can create a data slice. If the Define button is not available, you must select tables.
The Partition level attributes box lists the lowest level attributes that represent the partition. These are the attributes you select when creating the data slice in using the Define button. To make any changes to attributes in this box, right-click and select Edit Attribute from the menu.
Partition Tables Selection
You can open this page by clicking Add in the Tables box of the Metadata Partition Mapping page. On the Partition Tables Selection page, select the tables you want included in the partition mapping and click the arrow to move them into the partition mapping. Click OK to save the tables, and the page closes. The selected tables are displayed on the Metadata Partition Mapping page.
Data Slice Editor
After selecting partition tables, you must create a data slice for each table. You can access the Data Slice Editor when you click Define on the Define Metadata Partition Mapping page. Use the Object browser to select attributes for the data slice definition. The Location list allows you to access attributes saved in different folders.
You can drag an attribute to the Data Slice Definition box or double-click the definition, which opens the Attribute Qualification box. Make selections from the lists described below:

• Choose an attribute: Select or change the attribute.
• Qualify on: Select the ID, Desc, or Element for qualifying on the attribute. The default is Elements.
Note: Once you have decided which attribute you want to qualify on, you must choose the attribute form to qualify on. If you choose to qualify on the elements, the Object browser defaults to displaying the selected attributes elements. If you select a different form, such as ID or Desc, the Object browser defaults to displaying hierarchies.
• Operator: Depending on whether you are qualifying on ID, Desc, or Element, you see different options in the Operator list. If you are qualifying on elements, the selections In List or Not in List display. If you are qualifying on ID or Desc, the Operator lists options for comparison operators (for example, greater than, less than). You may choose to compare Values or Expressions, and to enter or select the actual value or expression.
• Element list: This option displays only when you are qualifying on elements. Click Add to include specific elements to the qualification. These are form elements for qualifying on the selected attribute.
Creating and modifying additional schema objects
The basic schema objects you create when you first set up a project are intended to serve as part of the project initiation and testing. Beyond these, MicroStrategy provides a palette of schema objects that allow you to tailor the warehouse and metadata information to display on reports. These objects and their capabilities are discussed in the topics listed below.
• Attributes
• Facts
• Hierarchies
• Partitions
• Tables
• Transformations
• OLAP cubes (importing and mapping)
• Schema (exporting to Microsoft Excel)
Other partition-related topics
Partitions divide large logical tables into smaller physical tables, improving query performance by minimizing the number of tables -- and the number of records within a table – necessary to satisfy queries issued against the warehouse. Partitioning data according to time, for example, allows queries to be made according to specific time slices, which, in a large database, can result in significant performance gains.
Note: MicroStrategy allows table partitioning along any number of attributes.
There are two types of partition mapping: metadata and warehouse:
• Metadata partition mapping is also known as application-level mapping since it is defined in the application. It is defined by creating a partition mapping table that contains partition tables from the project.
• Warehouse partition mapping is defined by a partition mapping table in the warehouse database. This table matches table names with the information each table contains.
Partition Mapping Editor procedures
• Add a data slice to a metadata partition
• Create a new metadata partition
• Define a data slice
• Set the partition mapping logical size

Hierarchies

Hierarchies are groupings of attributes that can be displayed, either ordered or unordered, to reflect the relationships that exist between the attributes in a project.
how to use hierarchies to group related attributes in practical business areas. For example, you can include a Time hierarchy in your model that consists of Day, Week, Month, and Year attributes.
hierarchies as they exist in the MicroStrategy environment and provides information on the
two different types of hierarchies in MicroStrategy. These types of hierarchies include the system hierarchy and the user hierarchy. The system hierarchy is automatically created
when you create a project and is maintained by the relationships that exist among the project’s schema objects.
The user hierarchy is a hierarchy which you create specifically for your report designers.
In MicroStrategy Desktop, you create user hierarchies using the Hierarchy Editor

Types of hierarchies
The two types of hierarchies that exist in MicroStrategy include:

• System hierarchy: The system hierarchy is created according to the relationships defined between the attributes in your project. You do not need to create the system hierarchy; it is automatically created in Desktop when you create a project. Although the system hierarchy
specifies an ordered set of all attributes in the project, it does not define ordering or grouping among attributes. The ordering and grouping of attributes, among other configurations, is defined in user hierarchies.
• User hierarchy: User hierarchies are named sets of attributes and their relationships to each other, arranged in specific ways that make sense to a business organization. They are user-defined and do not need to follow the logical data model. As the structure of your business intelligence evolves, you can easily change the design of a user hierarchy to include additional attributes or limit user access to certain attributes. This type of hierarchy is created to provide flexibility in element browsing and report drilling.

The system hierarchy is the default hierarchy that MicroStrategy sets up for you each time you create a project. It contains all of the attributes in the project and is actually part of the schema definition. When you first create a project, the only hierarchy it contains is the system hierarchy.
The system hierarchy holds information on the relationships between attributes in the project. The system hierarchy cannot be edited but is updated every time you add or remove attribute children or parents in the Attribute Editor, or when you define attribute children in the Project Creation Assistant. The system hierarchy is useful in determining relationships between all objects in the project. Attributes from the system hierarchy do not need to be part of an explicitly-defined user hierarchy. Any attributes that are not assigned to a user hierarchy remain available to the system as report objects, filter conditions, and components of consolidations

User hierarchies are sets of attributes and their relationships, arranged in specific sequences for a logical business organization. You create user hierarchies to define the browse and drill relationships between attributes. For example, you can create a Time hierarchy that contains the Year, Quarter, Month, and Day attributes. When you browse the attributes in the Data Explorer, you can double-click Year to get to Quarter and double-click Quarter to get to Month, and so on. Whereas browsing occurs through the Data Explorer, in drilling the user actually chooses to move to higher or lower levels on a report or move across to levels within different
hierarchies. For example, if the user drills on the Quarter attribute in a report, he or she can drill down to Month, up to Year, or across to an attribute within another hierarchy. You can create user hierarchies in the Hierarchy Editor using one or more attributes from the system hierarchy.
A user hierarchy is the only type of hierarchy you can define, and you can create any number of user hierarchies for each project. You should define user hierarchies that correspond to specific areas of your company business model and data warehouse schema.

Hierarchy structure
While both a system hierarchy and user hierarchy allow you to navigate attributes in your project, only the user hierarchy allows you to logically define and order groups of attributes.
The rest of this chapter discusses user hierarchies and how to create and configure them in your project. When you group attributes together into user hierarchies, you are developing a working design of the display and browse functions of the attributes. In the example below,
there are two instances of the Region hierarchy. One hierarchy demonstrates Region having multiple States and the States having multiple Stores. This hierarchy allows you to create drilling and browsing options to the lower levels to view Region, State, and Store on a report. However, if you only include Store in the Region hierarchy, as in the second example, then the only options for drilling or browsing are the Region and Store levels.



The Hierarchy Editor allows you to modify hierarchies by adding and removing attributes, as well as setting specific options.
Accessing the Hierarchy Editor
You can access the Hierarchy Editor from the MicroStrategy Desktop File menu by choosing New then Hierarchy. The Hierarchy Editor opens with the Select Attributes dialog box displayed on top of it. It is recommended that you begin with the Select Attributes dialog box.
Notes:
• Before you can access any editor, you must first open and log in to the Desktop interface. Next, expand the project source so that the project name is viewable. Select the project name. The New option in the File menu is inaccessible until you select the project name.

• If you do not want to see these additional dialog boxes, clear the Load dependent editor dialogs when creating new schema objects check box on the General tab of Desktop Preferences. This setting also affects the Fact Editor and Attribute Editor.
Hierarchy Editor Contents
The Hierarchy Editor has
Hierarchy Editor main screen
The Hierarchy Editor allows you to add or remove an attribute and its related properties. You can choose to show details, which displays the attribute name, entry point, element display property, and the associated filter for each attribute.
• Attribute name: The name of the attribute.
• Element display: If the field is set to Locked, the elements do not display in the Data Explorer. If the display has been set to a limit, the field shows the limit such as Limit 9 or Limit 55. The limit means that the number of elements displayed at one time is restricted to the number entered. The element display can be changed from the right-click menu.
• Entry point: You can choose to use the attribute as an entry point in the browse sequence. The Data Explorer uses entry points to begin browsing the hierarchy. This can be changed from the right-click menu. An attribute used as an entry point has a green check in its icon.
• Filter: Indicates a filter has been added to the attribute. This can be changed from the right-click menu.
From this screen you can choose the attributes to add to the hierarchy. Right-click and select Add Attributes to open the Select Objects dialog box and select the attributes from the Available objects box.
You can also remove attributes. Right-click the attribute and select Remove.
Note: While you can move attributes in the Hierarchy Editor by dragging and dropping, only the graphical representation is changed. The browse order is not altered. To change the browse order of attributes within a hierarchy, right-click and select Add Attributes, then use the arrows to move attributes up or down to change their order.
You can access the Attribute Editor to edit an attribute by right-clicking the attribute and selecting Edit Attribute.
Hierarchy Editor Toolbar:
The Hierarchy Editor toolbar offers the most frequently used functions in a convenient toolbar. The Hierarchy Editor-specific toolbar functions are explained below.

• Add a new attribute to the new hierarchy opens the Select Attributes dialog box to add an attribute to the hierarchy. This is also where you can define the order in which attributes appear, for example, in a prompt question that includes all attributes from a given hierarchy.
• Add browse attributes to the current attribute, which is available only if an attribute is selected, opens the Browse Attributes dialog box to add browse attributes to the selected attribute.
• Define attribute filters, which is available only if an attribute is selected, opens the Select Filters dialog box to add attribute filters to the selected attribute.
• Hide details hides the details displayed under each attribute.
• Show details displays the element display status, entry point, and filters for each attribute.
Select Attributes dialog box
The Select Attributes dialog box defines the attributes included in the hierarchy. You can browse the attribute folders in the project to find attributes to add. You can delete attributes already in the hierarchy. You can order attributes appropriately; this order will mirror the order in which attributes appear to any user presented with a prompt that contains this hierarchy.
Browse Attributes dialog box
The Browse Attributes dialog box defines the attribute children that are associated with a hierarchy attribute. For example, if Week and Month are the browse attributes defined for the hierarchy attribute Year, then from the Data Explorer you can navigate to Week and Month from Year. If only Month is designated as a browse attribute, you can navigate only to Month from Year and cannot view the Week directly from Year. As each browse attribute is added, it is automatically placed in the hierarchy in the same order as the system hierarchy.
Note: The browse order can be changed once you return to the main window of the Hierarchy Editor.
You must select an attribute on the Hierarchy Editor before you can use this dialog box. The browse attributes you select are added to the highlighted attribute.
Element Display
Element Display is accessed from the right-click menu. The settings define how attribute elements are displayed when browsing attributes using the user hierarchies of a project.
You must highlight an attribute on the Hierarchy Editor before you can use this option. Right-click the attribute and select either locked, unlocked, or limit for the selected attribute. If Limit is selected, you must type in a number to restrict the number of returns for that selected attribute:

• Locked: No elements of the attribute are shown within the user hierarchies in the Data Explorer. For example, if the attribute Year is locked, no elements for Year display in the Data Explorer when Year is expanded from its user hierarchy.
• Unlocked: All elements of the attribute are shown with the user hierarchies in the Data Explorer. For example, if the attribute Year is unlocked, all elements of Year (such as 2001, 2002, and 2003) display in the Data Explorer when Year is expanded from its user hierarchy.
• Limit: Incrementally retrieves the number of elements set for the attribute. For example, if the limit for the attribute Year is set to one, the years 2001, 2002, and 2003 are retrieved incrementally as they are requested.
These element display settings only control the display of attribute elements when browsing attributes using an attribute’s user hierarchy in the Data Explorer. The display of attribute elements when using the System Hierarchy is controlled by the settings in the Attribute Editor.
The display of attribute elements in user hierarchies is controlled by two things: the attribute within a hierarchy with the options listed above and the project. If the element display settings are different for the attribute within a hierarchy and the project, the more restrictive of the two settings is used when the attribute is displayed. For example, if you set the project limit to display five attribute elements for each attribute, and you set a specific attribute’s display within a hierarchy to be locked, the display of elements for that attribute in its user hierarchy is locked. This is because the attribute setting is more restrictive than the project setting. Conversely, with the same project set to display only five elements, if you set an attribute to display seven elements within a hierarchy, only five elements are displayed in the System Hierarchy for the attribute because the more restrictive setting for the project is enforced.
Select Filters dialog box
The Select Filters dialog box allows you to add filters that define the conditions or criteria for determining the attribute result set. For example, you can filter on 2003 for Year, or Stores in the Northeast for Stores.
You must highlight an attribute on the Hierarchy Editor before you can use this dialog box. When you select the filter, it is added to the highlighted attribute.
Note: The filters added or associated to an attribute need to make sense for that attribute; otherwise, no data is retrieved when you browse the attribute. This is your responsibility as the project designer because you are the only one that knows the nature of the data.
Creating and modifying additional schema objects
The basic schema objects you create when you first set up a project are intended to serve as part of the project initiation and testing. Beyond these, MicroStrategy provides a palette of schema objects that allow you to tailor the warehouse and metadata information to display on reports. These objects and their capabilities are discussed in the topics listed below.

• Attributes
• Facts
• Hierarchies
• Partitions
• Tables
• Transformations
• OLAP cubes (importing and mapping)
• Schema (exporting to Microsoft Excel)
Hierarchy Editor procedures
• Add a user hierarchy to the schema
• Add attributes to a hierarchy
• Add browsing attributes to a hierarchy
• Add hierarchy attribute filters
• Create a hierarchy
• Define attribute relationships within a hierarchy
• Delete a hierarchy
• Remove attribute filters from a hierarchy
• Remove attributes from a hierarchy
• Set hierarchy display properties
• Set hierarchy for drilling
• Set the hierarchy display in the Data Explorer
• Unlock the display of hierarchical elements
• View hierarchy properties

Facts

Facts are one of the essential elements within the business data model. They relate numeric data values from the data warehouse to the MicroStrategy reporting environment. Facts generally represent the answers to the business questions on which users want to report.

In the MicroStrategy environment, facts are schema objects created by and shared between MicroStrategy users. The facts you create in MicroStrategy allow users to access data stored in the data warehouse. Facts form the basis for metrics, which are used in the majority of analyses and reports that users can create with MicroStrategy.

Facts and attributes are necessary to define projects. In a MicroStrategy project, facts are numeric data and attributes are contextual data for the facts. For example, you want to analyze the amount of sales at a certain store during January. In this case, the amount of sales represents the fact, and the store and month represent attributes. As the project designer, you must create projects that contain facts and attributes. Users can then use these facts and attributes as building blocks for metrics and reports.

The Fact Editor allows you to create a new fact or modify existing fact properties. To create more complex fact definitions—facts with level extensions, multiple or heterogeneous column mappings, and so on—you must use the Fact Editor.
For example, you can identify the column where the fact is stored, or you can view a set of attributes that represent the lowest level of detail at which the fact exists in the warehouse. In addition, you can assign a level extension so you can report a fact at a different level from which it is stored.
Once you finish the initial fact creation, you can use the Fact Editor to add complexity to the existing facts or to add new facts as your project evolves.
Accessing the Fact Editor:
You can access the Fact Editor from the MicroStrategy Desktop File menu by choosing New then Fact. The Fact Editor opens, with the Create New Fact Expression dialog box displayed on top of it. This allows you to begin the first step of fact creation, which is creating a new fact expression. It is recommended that you begin with the Create New Fact Expression dialog box.
Notes:
• Before you can access any editor, you must first open and log in to the Desktop interface. Next, expand the project source so that the project name is viewable. Select the project name. The New option in the File menu is inaccessible until you select the project name.
• If you do not want to see the additional dialog boxes, clear the Load dependent editor dialogs when creating new schema objects check box on the General tab of Desktop Preferences. This setting also affects the Attribute Editor and Hierarchy Editor.
Fact Editor Contents:
The Fact Editor consists of three sections, each identified by a tab. Click a tab name for details on that tab.
Definition:
The Definition tab allows you to add, modify, or delete a fact expression. In addition, you can map the expression to the selected tables.
The formulas and mapping methods that define the fact calculations appear in the Fact expressions box. Select an expression and the source tables containing that fact expression are listed in the Source tables box. Clear a table's check box to disconnect the table from the expression. You can
• click New to access the Create New Fact Expression dialog box, which allows you to add an expression to the fact definition
• click Delete to delete the selected fact expression

• click Modify to access the Modify Fact Expression dialog box, which allows you to modify the selected fact expression
Note: When you create a new fact, the Create New Fact Expression dialog box is automatically launched, on top of the Fact Editor. Once you create a fact expression and click OK, you are returned to the Fact Editor.
Column alias:
Use the Column Alias tab to create an alias for the column used in the temporary table, accessed during SQL generation. This tab also lists the properties for the selected column, including Name and Data type.
From this tab you can also access the Column Editor - Column Selection dialog box by clicking Modify. Use the Column Selection dialog box to change the column used for temporary table SQL generation and to change column properties.
Note: There is no advantage in defining the fact column data type as Big Decimal.
Extensions:
The Extensions tab lets you create, modify, or delete the fact extensions defined for a particular fact. Clicking New opens the Level Extension Wizard that helps you quickly create a new fact extension.
The name of the fact extension and the text that describes the fact extension in terms of its purpose and usage appear under the Extension name and Extension description. When you highlight an extension name, the extension properties and extended fact level attributes change. Highlight an extension name and click Modify to edit the selected fact extension through the Level Extension Wizard.

When you initially access the Extensions tab, the only option available to you is the New button. Click New to open the Level Extension Wizard. Once you navigate through the wizard, the tab is populated and all buttons are fully functional.
Fact Editor Toolbar and menu options
The following Fact Editor-specific toolbar and menu options are available:
Edit menu
• New Fact Expression opens the Create New Fact Expression dialog box.

• New Fact Extension opens the Level Extension Wizard.
• Modify opens the Modify Fact Expression dialog box or the Level Extension Wizard, depending on whether the selected object is an expression or an extension.
Tools menu
• Show Fact Entry Level opens the Fact Level dialog box, which lists the fact entry levels.
Fact Editor Procedures
Through the Fact Editor, you can
• Add an expression to a fact
• Create a fact level extension
• Create a formula for a fact expression
• Create a fact and fact expression
• Define a column alias for the column used for temporary table SQL generation
• Delete a fact extension
• Disallow a fact entry level
• Extend a fact from its current level to a new level
• Lower a fact entry level
• Manually map a fact expression to source tables
• Modify a column alias
• Modify a fact expression
• Modify a fact extension
• Modify the mapping method for a fact expression
• Remove an expression map from a source table
• Select the tables to be mapped to the expression
• View fact properties