This document summarizes the best practices and the general conventions for MicroStrategy product suite. It serves as the starting point for new developers to get familiar with the MicroStrategy architecture and development framework.
We would like to start with “Baseline Project”.
The purpose of the baseline project is to provide a base project template to the MicroStrategy developers, so that they don’t have to create a new MicroStrategy Project from scratch and so that all the projects are related to each other, which will be beneficial during object migration. The administrator will first make a copy of the baseline project using project duplication and then the developers will add more objects to the new project based on the business requirements.
The baseline project contains attributes and attribute-hierarchies that are common to all projects. The attributes are already mapped to the dimension/ lookup tables in the warehouse and have been validated by the BI Architect. The shared attribute hierarchies will guarantee that the dimensions in different projects conform to each other. Besides attributes, the baseline project also contains other objects that ought to be shared among the projects, such as report auto styles, transformations, dashboard templates and so on. The baseline project doesn’t contain any facts, metrics or reports, because all of these objects are mainly project specific objects. Each project will have its own set of facts, metrics, reports, and so on.
Developers should make a conscious effort to update the baseline project on a regular basis. Thus it is required that when a developer adds new objects (which can be shared across other future projects as well) to their duplicated project, they should add those objects to the baseline project (after obtaining the right approvals from the BI architect), so that the baseline project always contains a complete set of shared attributes, attribute hierarchies (or common dimensions), and other relevant objects. While adding objects from the newly created project to the Baseline project, please make sure to review the attribute and fact mappings and adjust them as appropriate.
Once the baseline project is duplicated as the base for the new project, the developer can add additional tables (mainly fact tables) to the warehouse catalogue of the newly created project, define facts, and additional attributes on those tables as well as add/update the mappings between the attributes and fact tables as required. Once the schema object mapping is done, the developers can go on to define new metrics, reports, dashboards, and so on so forth.
Besides saving the development work and maintaining dimension consistency, another important implication of the baseline project approach is that object migration between the projects is made possible because all MicroStrategy projects are duplicates of a single project which is a key requirement of object migration between (related) projects.
Start by assessing the data model. Devising a model of your business data can help you analyze the structure of the data, how its various parts interact, and can also help you decide what you intend to learn from the data.
Create the following objects (the order of object creation can be largely described as schema objects first followed by application objects)
One of the first things you do when you create a logical data model is to determine the facts. Conceptually, you can think of facts as business measurements, data, or variables that are typically numeric and suitable for aggregation. Create Facts based on the primary fact tables. Look for tables name starting with “F_”. Pay careful attention to the source table mappings, fact expressions and extensions (if applicable).
After the facts are determined, the attributes must be identified. Attributes allow you to answer questions about a fact and provide a context for reporting and analyzing those facts. Create Attributes based on dimension tables. Look for tables name starting with “D_”. Pay careful attention to the source table mappings, attribute forms, types and default sorting, parent-child relations and display settings.
Refer to the Object Locations section for details about storing Attributes in a specific folder structures.
Hierarchies in a logical data model are ordered groupings of attributes arranged to reflect their relationship with other attributes. Usually the best design for a hierarchy is to organize or group attributes into logical business areas. Create Hierarchies based on attributes created. Pay close attention to the drill paths, browse attribute list, entry points, and element display settings.
Metrics are MicroStrategy objects that represent business measures and key performance indicators. From a practical perspective, metrics are the calculations performed on data stored in your database, the results of which are displayed on a report. Metrics are similar to formulas in spreadsheet software. Create Metrics based on the facts created. Pay careful attention to the metric formatting, subtotals/aggregation functions and smart totals (if applicable).
Refer to the Object Locations section for details about storing metrics in a specific folder structure.
A filter sifts the data in your data source to bring back the information that answers exactly what you require. Create filters as applicable. Pay careful attention to the toggle operators. Try and reuse objects across the project rather than creating standalone report specific filters. One of the basic requirements across projects is to create a dynamic filter which will return the latest available day (not in the dimension table, but in the primary fact table). Thus, this filter should not be based on the date attribute. Rather it should be based on the DATEID fact from the primary fact table. Based on this fact you should then create a metric (which returns the max of this fact). Finally this newly created metric can be leveraged inside the filters. At least the following filters should be created by default:
- Max Date – Returns Max Date based on the primary fact table.
- Last 7 Days – Returns Max Date – 6 and thus a total of 7 rows.
- Last 30 Days – Returns Max Date – 29 and thus a total of 30 rows.
- Last 365 Days – Returns Max Date – 364 and thus a total of 365 rows.
A prompt is a question presented to the user who runs the report. Depending on the answers the user provides, the report brings back and displays different data from the data source. Create prompts as applicable. Try and reuse objects across the project rather than creating standalone report specific prompts. Pay careful attention to filtering criteria, default prompt answers (and min/max answers), Prompt titles/instructions and web options.
Custom Groups / Consolidations
A custom group is a set of special filters that can be placed on a template. It is made up of an ordered collection of elements called custom group elements.
Consolidations are used to specify the data you want to view in your report. They allow you to group attribute elements in new ways without changing the metadata and warehouse definitions.
Custom groups are more flexible than consolidations because you do not have to know much about your data to create filters for a custom group. In contrast, consolidations require that you know exactly which attribute elements to select when creating the consolidation. Think about SQL efficiency – For each custom group element, there is at least one SQL pass. Since the Query Engine uses a smart algorithm to combine consolidation elements and determine the minimal number of SQL passes, only one SQL pass may be needed for all the consolidation elements.
For custom groups pay careful attention to element order, formatting, and display and general options. For consolidations focus on element order, formatting, and subtotal options.
Reports / Documents / Dashboards
A report is a MicroStrategy object that represents a request for a specific set of formatted data from your data source. In its most basic form it consists of two parts:
- A report template (usually simply called a template), which is the underlying structure of the report.
- The report-related objects placed on the template, such as attributes, metrics, filters, and prompts.
Pay careful attention to report data options, VLDB properties, grid options, auto-styles and formatting, thresholds, advanced sorting, subtotals, and report caching options. A document/dashboard contains datasets (report results) from one or more reports. This data is positioned and formatted, resulting in a single display of presentation quality. Try to minimize the overall number of reports used as datasets inside a dashboard. This will help us create an efficient caching strategy and minimize the daily loads on the database.
Use the “Generic Dashboard Template” located under [Public Objects > Reports > ProjectName Dashboards] folder as a starting point while creating dashboards. Refer to the Dashboard Design – Best Practices Document (TBA) for additional information on building Dashboards.
Create drill maps as applicable. Try and test out possible drilling scenarios to test report performance and conformity. Remember, that when the drill hierarchies are created, a default drill map is created. If no drill hierarchies are created, the system hierarchy is used to create the default drill map. The drill map determines what options are available to you when you drill on a report object. These different options are referred to as drill paths, which include the destination of the drill. The destination can be an attribute, a consolidation, a hierarchy, or a template.
Other objects as applicable
Create additional objects as applicable. Try and reuse objects across the project rather than creating standalone report specific objects. Leverage some objects like Transformations from the Baseline Project.
Project Name & Description
For the newly duplicated project, the BI Architect needs to provide the project name and project description, and the developer(s) need to constantly and consciously update the description. The project name should not be changed after it is finalized, because the report links (if any) in report services documents and dashboards may contain a hard-coded project name parameter in the URL’s. Thus, if the project name is changed later on, those report links will be invalid.
When building dashboard, all stack panels, grids/graphs and selectors should be name properly in the way that their locations and purpose can be easily identified by other developers. For example, the objects in the Summary panel of the dashboard might be prefixed as “Summary XYZ” so that when looking at the object list, other developers can easily know those objects belong to the Summary panel.
All other objects
All objects should be named consistently across the projects. Please do not use underscores (_) in the object names. Objects should be named normally and attention should be paid to the case.
For example: if you want to name a filter “last 30 days”, please naming it using the following guidelines: Last 30 Days [No underscores: last_30_days | No variable cases: Last 30 days].
All objects should be placed in accordance to the out-of-the-box (default) folder structure. The objects should be grouped into appropriate sub-folders as necessary. For example: All Facts should be placed in the Schema Objects > “Facts” folder. Generally, the Facts from one fact table should be grouped under one sub-folder. Similarly, all Prompts should be placed in the Public Objects > “Prompts” folder. Generally, the Prompts based on the same dimension (example: Time based prompts) should be grouped together in a sub-folder. Use your best judgment in maintaining clear, concise and consistent folder structures across all projects.
Exception to this rule only applies for the following objects in which case you should follow special folder structures as defined in their respective sections below:
- Reports & Dashboards
The reason these objects need to be arranged differently is because they will primarily be used by Power Users in Web. Thus it is important we arrange these objects keeping the end (Web) user experience in mind.