Author: Meenu Pall Published 12/05/2012
Category :For MicroStrategy BI environments configured to use database level security, publishing Intelligent Cubes could allow users to have visibility to data they should not see. To prevent this from happening, create the Intelligent Cubes by database connection. This will allow for your end-users to take full advantage of the in-memory performance of Intelligent Cubes. Below are steps to implement this in your environment.
For a MicroStrategy environment configured to use Database level security using MSTR connection mapping, Intelligent Cubes can still be utilized. To accomplish this, the first step is to turn ON this feature by selecting "Create Intelligent Cubes by database connection" in the Project Configuration editor (screenshot below).
Without selecting to create Intelligent Cubes by database connection, users may have visibility to data they should not see. By default, this check box is cleared.
Screenshot = Project Configuration editor
Configuration of the MSTR User and Connection Map via Command Manager
For each connection map, a corresponding MSTR DB Connection and DB Login must be assigned.
Sample Scripts below:
--------------------
mstr User = firm1
DBLOGIN = dblogin_firm1
DB Connection = DBConnect-Firm1
---------
/* ==== Create Firm User =======*/
CREATE USER "firm1" PASSWORD "firm1" FULLNAME "Firm1" IN GROUP "Firm1";
/* ==== Create DB Login, DB Connection and Connection Map and map them all together =======*/
CREATE DBLOGIN "dblogin_firm1" LOGIN "xxx" PASSWORD "xxx";
CREATE DBCONNECTION "DBConnect-Firm1" ODBCDSN "DSN NAME" DEFAULTLOGIN "dblogin_firm1";
CREATE CONNECTION MAP FOR USER "firm1" DBINSTANCE "<DB Instance Name>" DBCONNECTION "DBConnect-Firm1" DBLOGIN "dblogin_firm1" ON PROJECT "<Project Name>";
APPLY RUNTIME SETTINGS; /* ==== This step will apply changes w/o the need to restart the I-Server =======*/
Setting up the Intelligent Cube Cache Update via Command Manager
For each MSTR user to utilize the same Intelligent Cube, but maintain its own Cache, three configuration Objects will be required:
Sample Script Names:
--------------------
1. EVENT = Firm 1 Cube Refresh Event
2. SCHEDULE = Firm 1 Cube Refresh
3. CACHE UPDATE SUBSCRIPTION = Firm1 Cube Refresh
/* =====================================
Create three MSTR Configuration Objects - Event(1), Schedule(2) and Subscription(3) for each New Firm (This in only run once, when a new Firm(mstr User) is onboarded.
=====================================*/
CREATE EVENT "Firm 1 Cube Refresh Event" DESCRIPTION "Trigger for Firm 1 Cube Refresh";
CREATE SCHEDULE "Firm 1 Cube Refresh" STARTDATE 09/10/2010 ENDDATE NEVER TYPE EVENTTRIGGERED EVENTNAME "Firm 1 Cube Refresh Event";
CREATE CACHEUPDATESUBSCRIPTION "Firm1 Cube Refresh" SCHEDULE "Firm 1 Cube Refresh" USER "firm1" CONTENT "<cube name>" IN FOLDER "<cube location>" IN PROJECT "<Project Name>" DELIVERYFORMAT HTML SENDNOTIFICATION FALSE;
/* =====================================
Update I-Cube - This CM script will be called from batch file; the batch file will be called from ETL Job.
=====================================*/
TRIGGER EVENT "Firm 1 Cube Refresh Event";
Author: Meenu Pall
: Data VisualizationI’ve had the opportunity to speak about my profession as a designer in a data-intensive field and I've always tried to give credit where credit was due. By that I mean visualization should always be about the data and the story it needs to tell. Design and aesthetics should aid the core message, not the other way around.
It’s easy for a designer to make something look better and receive all the credit, but just because something looks better, doesn’t mean it is better. In fact, it’s usually the opposite and that something that is aesthetically pleasing may be masking the real issues with a visualization or dashboard. Besides all of that, what makes something “look better” is a completely subjective thought. People have different tastes, backgrounds, and cultural biases and will naturally favor different things. I believe the way to please all of these people is for you to rely on the data, not the decorative elements.
What is a “decorative element?”
A decorative element is an object that is visual in nature and serves no useful purpose in a visualization or dashboard. There are useful visual elements that I encourage people to take advantage of. These elements are meant to aid the end-user in the consumption of information. Once these elements become distracting, they are decorative and are no longer useful.
Examples of Decorative Elements
Design and aesthetics are subjective and always will be. Ultimately the design choices you make are going to depend on requirements, nuances of the technology you're using and the tastes of your customer or business sponsor. Understanding and embracing these principles will assist your visualizations and dashboards in becoming adopted and embraced. As you communicate and illustrate these principles remember to justify and validate the purpose of every graphical element. Sometimes your designs will be applauded, sometimes they will not, but at least you tried and your arguments came from a place of substance and not self-opinion.
Look for the next blog in the series: The Basics of Color Theory
Author: Meenu Pall
A project I was working on encountered an issue where we tried to pass several prompt answers through parameterized links from one cube-based dashboard to another. We were able to get the normal methods to work, however, when the “all” option was selected, it didn’t function as desired. By not enabling, we would be sacrificing the end user experience; something we were determined not to do.
For example: using the link editor and selecting the “answer prompts dynamically” option worked, but if a user had a selector set to “all” it would not answer that prompt. We tried numerous approaches to fix this and have the ability to pass “all” as a prompt answer, including, using value prompts, setting up our prompts directly in the report, but the method outlined below was the solution that worked for us in the end.
Step 1 - Set Up Your Prompts
Step 2 - Add your prompts to your report for the Child Dashboard
c. Use Ctrl+F to help find the proper area in the source code. Type in the attribute name of the selector. You’re looking for the “<span id=”###”” that belongs to each selector. Write down the number you find for each for. For example in this source code the Node Number for Brand is K72.
d. Repeat for all selectors.
b. Copy and paste the link into a text editor.
c. Remove everything before "main.aspx?...."
d. Also remove “Server=____” This will prevent the link from going back to the wrong server after migration.
You link should now look something like this:
Main.aspx?&Project=MicroStrategy+Tutorial&port=0&evt=2048001&src=Main.aspx.2048001&visMode=0&documentID=039EDS36587CV6S8E4S68AZ354¤tViewMedia=8
e. Add “&elementsPromptAnswers=” to the end of your link
f. Using your nodes construction the rest of your link using the following method:
{&Attribute@GUID};{&CurrentSelectionElementID:Node#&}
For example:
{&Brand@GUID};{&CurrentSelectionElementID:K72&}
Note: If your Attribute name is more than one work surround the ID with square brackets like so:
{&[Brand Family]@GUID};{&CurrentSelectionElementID:K72&}
g. Using comma’s to separate, add all of the ID Syntax’s to the end of your link. For example:
Main.aspx?&Project=MicroStrategy+Tutorial&port=0&evt=2048001&src=Main.aspx.2048001&visMode=0&documentID=039EDS36587CV6S8E4S68AZ354¤tViewMedia=8&elementsPromptAnswers={&Brand@GUID};{&CurrentSelectionElementID:K72&},{&Category@GUID};{&CurrentSelectionElementID:K67&},{&Year@GUID};{&CurrentSelectionElementID:K89&}
Step 6 - Add your link
With this method we were able to give the client their desired ability to pass selector from one dashboard tier to the next. Allowing them to effectively drill down through the data in a linear fashion, without having to reselect filters or answer prompts. Thus, creating a seamless navigation process throughout all the dashboards.
Knowlwdge Neywork Microstrategy
Author Meenu Pall
What is metadata?
The database repository where definitions of all MicroStrategy objects are stored. Metadata could be hosted on most databases. In simple words, Metadata could be considered as the heart of MicroStrategy environment.
MicroStrategy metadata contains datawarehouse connection information, project settings and microstrategy object definitions.
What are the tasks that you can perform with the MicroStrategy Architect?Initially populate the metadata with project definition and parameters, schema objects and create schema objects
What is difference between 2 ,3 , 4 tier connection?
In 2 tier architecture, the MicroStrategy Desktop itself queries aganist the Data warehouse and the Metadata with out the Intermediate tier of the Intelligence server.
The 3 Tier architecture comprises a Intelligence server between MicroStrategy Desktop and the data Warehouse and the Metadata.
The 4 tier architecture is same as 3 tier except it has a additional component of MicroStratey Web.
Intelligence Server is the architectural foundation of the MicroStrategy platform. It serves as a central point for the MicroStrategy metadata so you can manage thousands of end user requests.
You are very limited in what you can do with a 2-tier architecture. Things like clustering, mobile, distribution services, report services, OLAP services, scheduling, governing, I cubes, project administration are only available via Intelligence Server.
When a change is made in 2 tier – is it reflected in 3 tier?
No it is not reflected. Following are the methods by which we can reflect it in 3 tier.
· Update the schema in 2-tier. Restart the MicroStrategy Intelligence Server. Recommended method by MSTR.
· Update the schema in 2-tier and reload the project from the Project Monitor.
What is heterogeneous mapping?There are no restrictions on the names for the columns used in the expressions of a given attribute form. Heterogeneous mapping allows the engine to perform joins on unlike column names. If the user defines more than one expression for a given form, heterogeneous mapping will automatically take place when tables and column names require it.
For example, because different source systems store Date information in various contexts, a data warehouse may have multiple columns in different tables which all represent the concept of 'Date'. The ID form of the attribute 'Date' may contain two expressions. The DATE_ID column occurs in the LU_DATE table as well as in two other tables. The elements in these columns correspond to elements in the ORDER_DATE column in the order_detail and ORDER_FACT tables.
Heterogeneous Mapping of Attribute Forms.
Why do we go for parent child relationship?While establishing the relationship between attributes one can either look from business hierarchy point of view and the attribute higher in the hierarchy becomes parent of the attribute lower in the hierarchy. Parent and Child follow a one-to-many relationship. Example Time hierarchy Year > Month > Date. Here Year would be parent of Month and Date and Month parent of Date.
We can also identify Parent-Child relationship from database design point of view. Here in a table the Primary Key uniquely identifies the other columns in the table and hence qualifies as child of all the other attributes from the table, in the same ways as a child in real world identifies his father (at least the biological one).
What is a compound attribute?A compound attribute has its value determined by an expression which combines two or more columns in a database to create a new column
What are different kinds of objects in Microstrategy?Configuration Objects: Configuration objects are MicroStrategy objects which can be re used in multiple projects and they appear in the system layer. Ex: Database Instances, Users, Login ID's, Schedules
Schema Objects: The building block of BI. Schema objects are directly mapped to a column or columns in the database. Attributes, Facts, Functions & Operators, Hierarchies, Partition Mappings, Tables & Transformations
Public Objects: Objects that generate analytical data and are built on other schema objects or public objects. Also called as application objects. Ex: Consolidation, Custom Groups, Drill Maps, Reports, Documents, Filters, Prompts, Metrics, Templates and Searches
How to create a conditional attribute in MicroStrategy Desktop
A user may want to create an attribute with an alternating expression depending on a certain condition, a conditional attribute. This condition may be implemented through an ApplySimple statement such as the following:
Types of Attributes
Simple
A simple attribute is made up of one or more expressions. With a simple attribute definition, you can define an attribute as a column, constant, or simple expression.
Implicit Attributes
An implicit attribute is a virtual or constant attribute that does not physically exist in the database because it is created at the application level. The implicit attribute has its own expression.
Derived Attributes
A derived attribute has its value determined by an expression which combines two or more columns in a database to create a new column.
Compound Key Attribute
A compound key attribute is an attribute whose primary key is made up by the combination of two or more columns.
What is a Implicit Attribute?
An implicit attribute is a virtual or constant attribute that does not physically exist in the database because it is created at the application level. The implicit attribute has its own expression.
What is a joint child?
A joint child is Microstrategy way of handling Composite Keys. Composite keys are constituted of two or more columns which together act as unique identifier. To handle this case in Microstrategy we make this set of columns, constituting composite keys, as joint child.
What are attribute roles?
A user defines two attributes that have the same definition but play different roles in the business model. In this example, attribute Origin Airport and Destination Airport are defined using the same Lookup Table and Column (Airport_ID). Both attributes share the same forms, or information about them (Description, Location, etc.). In the fact table, however, a separate column exists for each of their roles (Origin_Airport_ID and Destination_Airport_ID).
When should attribute relationships be modeled as separate attributes in a parent-child relationship and when should they be modeled as forms of the same attribute?
It is preferable to use separate attributes that are related hierarchically (that is, parent-child relationships) for the following reasons:
Attributes that exist in a hierarchical relationship can appear independently of each other on a report. If 'Item' and 'Item Category' are modeled as separate attributes, reports may then be designed to report on individual items or whole categories. If 'Item Category' is considered a description (form) of 'Item', it becomes impossible to report on 'Item Category'.
Attribute forms are not available as metric dimensionality settings. In order to aggregate data at a particular attribute level, that attribute must exist as an attribute. If the attribute is modeled as an attribute form instead, it is possible to aggregate only at the level of the attribute containing the form.
Attribute forms are not appropriate under the following circumstances:
· When the attribute must be used as an aggregation level (metric dimensionality). For example, customer and state: if a user wishes to calculate sales totals by the states in which customers live, state should be a separate attribute as a parent (or grandparent, and so on) of customer.
· When the attributes exist in a one-to-many or many-to-many relationship. For example, customer status: Presumably, each status will apply to several customers. Modeling status as a form of customer makes it always subordinate to customer, which may impose unnecessary limits on reporting options.
How are the drilling options for an attribute decided?Based on relation between attributes, hierarchies and their drilling configuration
What are the two types of Hierarchies?
System hierarchy: It contains all the project attributes and its available browse paths and is based on relation between attributes.
User defined Hierarchy: Custom grouping of attributes and define their browse paths.
FactsWhat is fact extension?
• You can use level extensions to change a fact level, which is a set of attributes that represent the lowest level of detail at which the fact exists in the warehouse.
• Level extensions define how facts can be extended, lowered, or disallowed to other facts across the schema.
What is fact degradation?
When facts exist at a higher level than the report display level, you must specify how the Engine degrades the data to the lower level. When you lower the level at which a fact is reported, you are using degradation.
Types of facts
Simple facts
A simple fact is made up of one or more fact expressions. With a simple fact definition, you can define a fact as a column, constant, or simple expression.
Implicit facts
An implicit fact is a virtual or constant fact that does not physically exist in the database because it is created at the application level.
Derived facts
A derived fact has its value determined by an expression that combines two or more columns in a database to create a new column.
Metrics What are different types of metrics?
• Simple : Simple metrics combine aggregate operators with fact columns or attributes.
• Nested: Metrics that perform multiple aggregations by placing one calculation formula inside another
• Compound : A compound metric is a combination of expressions that, through the use of functions, are themselves metrics.
• Derived
What is Base Formula? Use a simple expression as a base formula to facilitate the creation of more complex metrics.
What is smart metrics?Compound metrics are the ones that are derived by some specific expression involving the different simple metrics. Eg, Total( profit/units Sold). Smart metrics is when the compound metric is calculated with the help of subtotal calculations for every element inside the compound metric. For the above example the smart metric computation can be Total(profit)/Total(Sold).
What is level metric?Level metrics are advanced metrics which are set to be evaluated at a specified attribute level. These are required when in the same report you need to roll up a metric at two different levels side by side. Example is comparison of “Revenue from a Region” to “Revenue from a Country”. Here Region and Country are the two different levels.
The level of a metric, also referred to as dimensionality, allows you to determine the attribute level at which the metric is calculated.
Default – Report Level
The elements needed to specify a level for a metric
Target - Attribute level at which the metric
Grouping - Determines the metric aggregation.
Filtering - governs the relationship between the
report filter and the calculation of the metric.
What is purpose of having conditionality in metrics?
Conditionality associates a filter to the metric calculation. This is an optional component.
What are the different components of metrics?
• The formula defines the data to be used and the calculations to be performed on the data. The outermost formula must be a group function.
• The level, or dimensionality, determines the level at which to perform the metric calculation. For example, you can choose to calculate at the month level or year level.
• Conditionality associates a filter to the metric calculation. This is an optional component.
• The transformation applies offset values, such as “four months ago,” to the selected attributes. This is also an optional component.
What is the purpose of transformation in Metrics? Types of transformation.
It encapsulates a business rule used to compare results of different time periods.Transformations are used in the definition of a metric to alter the behavior of the metrics.
• Expression - based transformations – You implement these transformations using a mathematical formula in Microstrategy Architect.
• Table - based transformations – These transformations are based on a transformation – or relate –table in the warehouse.
What is dynamic Aggregation?Rollup metric values that occurs when an attribute is moved from the report grid to the report objects.
For Eg: The report grid has Quarter and Customer City, Revenue. If we remove Quarter into the report objects then revenue should automatically roll up to Customer City.
How to ensure that a particular fact table will be chosen for a metric
The MSTR operates in such a way that the incoming queries and data retrievals are done from the table which has the least logical size. Thus if we prefer a specific table to be the center of activity then we should try to reduce the logical size of that specific table so that it becomes considerably lesser than the other tables.
In Microstrategy, how can you direct the sql generated to use a specifc table?
Using the Level parameter in the Metric level options
Assuming you have OLAP licence,the easiest way to direct to a particular table is to create a dummy fact on the table,include the fact in metric and put the metric in report objects.
How to hide a particular metric in a report for a specific user?Using Object level security
What is Metric Formula Join Type? How it is different with Metric Join Type?
Metric Formula Join Type is used for Compound Metrics and determines how the different tables used in metric formula are joined.
Whereas the Metric Join Type determines how the metrics are joined to other metrics.
FiltersWhat is filter?Filter is used to restrict data in a report
What is report as filter?In the MicroStrategy when the same filter conditions must be applied to multiple passes, the same where clause appears in each of those passes. This redundant where clause can be expensive if the filter conditions are complicated and thus involve many tables and joins. Ideally, an intermediate table populated with entries could be created to satisfy the complicated filter conditions so that the rest of the SQL statements can use that intermediate table. In that case, the where clause would be executed only once instead of multiple times and SQL performance would be improved. In this case to populate the temporary table we can use report as a filter
What is view filter?View Filters: View Filters are the conditions that come into play before a specific result is presented to the user. Thus the view filters are not part of the SQL statements like report definitions, rather they are the filters applied after the execution of the SQL statements, after the data is retrieved from the data warehouse.
What is filtered prompt?
We can restrict the number of elements in a prompt using a filter.
Difference between report and view filter?Report filters: report filters are the conditions that accompany the report generating SQL statements. The report definitions have the filters as part of their definition.
View Filters: View Filters are the conditions that come into play before a specific result is presented to the user. Thus the view filters are not part of the SQL statements like report definitions, rather they are the filters applied after the execution of the SQL statements, after the data is retrieved from the data warehouse.
Difference between Report Limit and Report filter?A report limit specifies a set of criteria used to restrict the data returned in the report data set after the report metrics are calculated.
Report Filter applies the where condition to the query sent to warehouse to extract the results.
What is the difference between Absolute filtering and standard filtering?When we use the absolute filtering in definition of level metric whatever data we obtain from the filter is goingto be reported as such and the the report filter will be overridden by the absolute filter settings. The standard filtering the report filter interacts with the metric filter in the normal way and what we obtain will be formatted according to the report filter settings.
What is a Joint Element List Filter?
Helps to choose combination of attribute elements from different attributes to filter a report
Enables to create filters with attribute pairs as well as triplets, quadruplets and so on.
This is available in the advanced qualification section of the filters
What is a Security filter?Security filter is used to apply security at the database data level.Whenever a users associated with security filter runs a report, a WHERE clause is always included in the report sql with the condition defined in the Security Filter.
What is a Attribute to Attribute Filtering?This is used to compare values of 2 attributes using their forms.
Eg: Ship Date<Day(ID)+2
PromptsWhat is prompt?
• Used to dynamically modify the contents of a report
• Enormous flexibility for designing reports
• One report can satisfy multiple reporting requirements with prompts
• Allow users to select criteria for reports at run-time
Different types of prompts?
1. Object: Allow users to select objects comprising a report at run-time
Users can select from any objects to dynamically build a report
2. Level: Level prompts enable you to specify the level of aggregation calculation of a metric
3. Value: Allow users to type a value
Used in conjunction with other prompts for filters or metrics
4. Filter Defintion Prompt
a. Choose from all attributes in a hierarchy
b. Qualify on an attribute
c. Choose from an attribute element list
d. Qualify on a metric
What is object prompt?An object prompt allows you to select which MicroStrategy objects to include in a report, such as attributes, metrics, custom groups and so on. Object prompts can either determine the definition of the report template or the report filter.
What is level prompt?
Level prompts enable you to specify the level of aggregation calculation of a metric
Can we use hierarchy in prompt?
Yes we can use.
ReportWhat is the purpose of having thresholds in report?
Used to create conditional formatting for metric values.
Two metric and one attribute – How will the SQL look if the metrics are from same tables and are from different tables?
If the two metrics are sourced from the same table then only one pass will come.
If they are sourced from different fact tables then a pass will be generated for each metric and in the final pass the data will be aggregated,
Difference between drill map and drill path. What is drill to template
Drill maps allow you to create fully customized drill paths that are available to your users while drilling on a report. By default, the paths available are based on the system Hierarchy of the project. You can create custom drill maps that can override these defaults.
Difference between Consolidation and Custom Groups. How will they affect SQL ? Which is more expensive operation.Custom Groups are handled at the database end where as Consolidations are handled at the Analytical Engine end. As a result the Consolidations are not an overhead for the database as there is a single pass in the query. On the other hand Custom Groups are an overhead on the database as they fire a separate SQL pass for every Custom group element.
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.
Difference between page by and outline mode?
Page by enables to select and display subsets of report results as separate pages.
Outline mode enables indented grouping of related attributes This is useful when we want to display the same report at different levels quickly.
How can we enable caching in reports? Different types by which we can implement caching?Caching can be enabled/disabled at project or report level. However the report level setting will override the project level setting.
Using Project Configuration we can enable the caching else in report – using caching options we can enable or disable the caching.
Templates can also be used for caching. Using a common template for developing common reports will help to fetch report data faster.
Can Threshold be applied on attributes? What are the different formatting types?1. Font Type, Color
2. Background
3. Image
4. Text
What are adhoc reports and static reports?
Adhoc reports run in real time based on the input parameters provided by the user at the run time.In Microstrategy, adhoc reports are created using Prompts.
In static reports, users won't be provide any input parameters.These reports are usaully schedule to run overnight and ready to view immediatley in the mornings using cache.
TablesHow to handle table structure change in DB to be reflected in MSTR?
Update the warehouse catalog.
Modify the data type in attribute editor.
When a column name changes in a table how do we handle it in MSTR
1. Remove the association of the column from the respective attributes.
2. Go to warehouse catalog and update the structure
3. Update the schema
4. Associate the attributes to the new column name. Save
5. Update the schema
How to we add tables into a schema1. Warehouse catalog
2. Architect
When you add a physical table from the datawarehouse into the project, MSTR creates a corresponding logical table in the metadata. Physical table stores the actual data whereas the logical data stores information about their corresponding physical tables including column names, data type and schema objects associated with the column names.
There are two views - the physical view and the logical view in the table editor. The logical view shows the attributes and facts mapped whereas the physical view shows the columns and corresponding data types.
What is a logical size of a table and what does it depend on?
Logical size is Microstrategy way of generating the best suitable/optimized SQL to fetch the required data. Microstrategy follows an algorithm to calculate the logical size of a table, which depends on the no of attributes and facts based on the table and also the position of those attributes in the system hierarchy.
What are Logical Views used for?
Logical Views allows application architects to create any desired view using MicroStrategy, without DBA involvement. Once these Logical views are created, they are available to the report designer in a way similar to any other table. This allows developers to model attributes and facts whose expressions span multiple tables
Other QuestionsWhat is the data modelling tool in MSTR called?
Architect
How do we migrate objects across projects?
Object Manager
What is the difference between object manager and project merge?
The portion of the interface that allows users to retrieve the results of previously executed or scheduled reports.
What is difference between purging and deleting cache?
Caching allows for improved performance in response to report queries. Although the use of caching is an advantage, there are instances when caches may expire or become invalid. Some of these instances are:
When there are changes made to the objects in the data warehouse, the existing caches may be configured so that they are no longer valid when hitting certain warehouse tables. Any further report execution will no longer hit the cache.
When the definition of an application object changes (such as a report definition, report, template, metric definition, etc.), the related report cache is marked as invalid.
When there is a need to control the growth of caches on the Microstrategy Intelligence Server, old caches may need to be expired automatically.
Invalidating Report Caches
Invalidation is a preventive measure that renders a cache unusable by nullifying it. Cache invalidation only applies to Matching caches and Matching-History caches. It makes the cache ineligible in the matching process so it is not used to fulfill a report request.
In the case of a Matching cache, invalidating it will automatically result in deleting it.
In the case of a Matching-History cache, invalidation simply converts it to a History cache that is not used in the matching process any more but is still accessible through History List messages that reference it. However, if all these messages are deleted, the converted History cache that is referenced is also deleted.
Expiring Report Caches
Cache expiration is a process that renders a cache unusable by terminating its useful life. It yields the same results as invalidating a cache and applies to Matching caches and Matching-History caches. Cache expiration occurs automatically as per the 'Report cache duration' setting which can be found under Project Configuration > Reports >Advanced > Caching.
When a cache is updated, the current cache lifetime is used to determine the cache expiration date based on the last update time of the cache. This means that changing the Report cache duration setting does not affect the expiration date of the already existing caches. It only affects the new caches that are being or will be processed.
Deleting Report Caches
Cache deletion is a process that deletes the cache from memory as well as disk. Report caches are automatically deleted by MicroStrategy Intelligence Server if cache invalidation and History Lists are performed and maintained properly.
In the case of a History cache, deleting it does not automatically delete the associated History List messages that reference it. After deleting a History cache, when a message that references it is retrieved, the following occurs:
MicroStrategy Web users see an error message, "Execution results not available. Would you like to re-execute?"
MicroStrategy Desktop users do not see the above error message because MicroStrategy Desktop automatically resubmits the report for execution.
On the other hand, a History cache is automatically deleted when all the History List messages that reference it are deleted (when its History List reference count reaches 0).
In the case of a Matching-History cache, when all the History List messages that reference it are deleted, it is simply converted to a Matching cache, while losing its History component.
Caches can be deleted:
Manually - via Cache Monitor and MicroStrategy Command Manager
Scheduled - via MicroStrategy Cache Administration Utility
Scheduled - via MicroStrategy Desktop Scheduled Administrative Task.
Purging Report Caches
Cache Purging is a process whereby all report caches can be deleted in bulk, even the one references by History List messages.
How to apply row level security?Applying Security filters to the user. This provides row level security
When do we go for schema update?
When any of the schema objects are modified – Attributes/Facts/ Transformations/Tables/Hierarchies/Partitions
When New Tables are added to the schema using warehouse catalog.
When you update the structure of existing tables in the warehouse catalog.
What are VLDB properties?VLDB stands for Verly Large Data Base Properties. This is Microstartegy way of handling database specific preferences while generating the report SQL. There are number of them. A few common one are for Attribute or Metric join types, cross join check, type of intermediate table, etc.
At which levels you can set the VLDB properties? Which level has the highest pecedence?VLDB Properties can be set at various levels like Report, Template, Metric, Project, Database Instance and DBMS level. Out of this Report level has the highest priority. It overrides all other levels.
What are the various ways of incorporating security in Microstrategy?
In Microstrategy security can be incorporated using a mix of any of the following ways:
Folder Level
Row Level
How conflicts occur and what the ways to resolve them?
When copying objects across projects with Object Manager, if an object with the same ID as the source object exists anywhere in the destination project, a conflict occurs.There are various ways to resolve depending upon the conditions like use existing, replace, keep both, use newer, use older, update in same path, update in new path and merge privileges.
What is the command manager used for?
The command manger is the one through which you can manage the applications, user accesses, security and databases of the microstrategy. The command manager allows us to save the text commands that can be executed as scripts. Thus it can help in automation of the entire management process.
What are the nulls on microstrategy report and how to mask them.
1)If in this question we want to deal with null in MSTR report than we have some display property in report data option which we can change according to the replacement of null values.
DATA>>REPORT DATA OPTION>>DISPLAY>>NULL VALUES
2)if we want to deal with sql geration of report than there is an option of VLDB property according to you requirment you can deal with sql query of report
Servers in MSTRI-server: MicroStrategy Intelligent server provides jobs management and analytical processing for all MicroStrategy applications. This acts as a central component connecting the metadata, warehouse, desktop, Web server and Narrow cast Server. Few or main features: Reports Services, OLAP Services, Data Mining, Multi Source connection, Caching, Clustering. Latest version supports installing I-server (different packages) on Windows, Sun Solaris, IBM AIX, HP-UX, Linux. Contact MicroStrategy for more information.
Web Server: MicroStrategy web server responds to the requests from browsers. Web server interacts with the I-server to extract the necessary information. Can be installed on most of the major web servers and supports most popular browsers. Contact MicroStrategy for certified products.
Narrow cast Server: Narrowcast sever delivers personalized business insight to emails, cell phones, pagers, file servers and print servers extending the reach of Business Intelligence applications. It offers a comprehensive solution for information delivery integrating a subscription portal with a delivery engine.
Roles in MSTR· Administrator: By default, the role/person will have full access to the environment. In other words this role has full access to all the type of objects mentioned above.
· Architect: By default, access to configuration objects is restricted.
· Developer: By default, no access to configuration objects, use access to schema objects and full access to public objects.
What are Passthrough Functions?
Pass through functions are used to utilize various special functions that specific to databases.Some of the passthrough functions available are Applysimple and Applycomparision.
What is the difference between warehouse and metadata partition mapping in MicroStrategy Desktop
Warehouse Partition Mapping:
Warehouse Partition Mapping tables are used for performance reason.
For example, a fact table in the data warehouse may have two columns: Sales and Years. If a report is run with Sales for the Year = 1999, the query engine will need to search through the entire table for all the years, including 1999, to return the data.
To improve efficiency, Partition Base Tables (PBT) can be created to have Sales for particular years. Assuming that there are 10 years worth of data in the database, 10 different partition base tables need to be created:
- PBT1: Sales for 1991 (Year=1991, Sales=$)
- PBT2: Sales for 1992 (Year=1992, Sales=$)
...
- PBT10: Sales for 2000
A Warehouse Partition Mapping Table (PMT) will then need to be created and will have:
Year
PBTName
1991
PBT1
1992
PBT2
...
2000
PBT10
The above PMT is going to tell the engine which table to grab the data from for a particular year. This means that when the report is run for Sales for 1997, then the query engine will first go to the Partition Mapping Table and then find the correct PBT corresponding to the year 1997.
Metadata Partition Mapping:
Metadata Partition Mapping Tables map the attribute elements to its corresponding PBT and performs the same function as the PMT.
To create a Metadata partitioning, follow the steps below:
Types of Report CachesThere are two categories of report caches, Matching and History
Based on these two categories, the following types of report caches are displayed in the Cache Monitor:
When a report is run in a MicroStrategy 8.x project, with report caching enabled, the Intelligence Server determines for each report request whether it can be served by an already existing cache. If there is no match, it then runs the report on the database and creates a new cache. The type column for this cache on the cache monitor will be 'Matching.'
Matching-History caches
When a report is sent to history directly instead of being executed, the type column in the cache monitor will be 'Matching, History.' Matching-History cache is a Matching cache with at least one History List message referencing it. It is actually one cache with two logical parts: Matching and History.
History caches
The following two circumstances result in the Type column displaying only 'History':
XML Caches
When a report is executed from Web, an XML cache for this report is created in XML format. It is available for reuse on Web later on. It is possible that the XML cache is created at the same time as its corresponding normal report cache. Although just a different format of the same report cache, the XML cache is maintained as a distinct cache and thus counts towards the maximum number of caches as an independent unit. It is automatically removed when the associated report or History cache is removed.
Types of Caches1. Element Cache
Used by attribute element list.
When is it created?
1. Browse attribute elements when browsing a hierarchy
2. Browsing a prompt
3. In Filter Editor
2. Object Cache : when you open the editor of an object
When is it created?
1. Opening a report editor
2. Opening a attribute/fact /metric editor
3. Report Cache : when executing a report
Matching and History - Types
4. Document Cache : when executing a document
How to resolve many to many relationships?To resolve a many-to-many relationship means to convert it into two one-to-many, many-to-one relationships. A new entity comes between the two original entities, and this new entity is referred to as an intersection entity or cross reference entity. It allows for every possible matched occurrence of the two entities. For example, the "many-to-many" relationship of many EMPLOYEEs are assigned many TASKs which can be resolved by creating a new entity named EMPLOYEE_TASK. This resolves the "many-to-many" relationship by creating two separate "one-to-many" relationships. The two "one-to-many" relationships are EMPLOYEE or parent entity which is assigned EMPLOYEE_TASK or child entity and TASK or parent entity is assigned to EMPLOYEE_TASK or child entity. Whilst this may appear complex, the introduction of the EMPLOYEE_TASK child entity reduces data redundancy and improves overall database and application performance.
Major Differences between 8 and 9
1. Distribution Services is new in 9
2. Ability to create prompts and filters in web
3. Drilling in Documents
4. Dashboards with multiple layouts
5. Intelligent Cubes
6. Back and Forward buttons in web
7. Personalised prompt answers
What is evaluation ordering?Determines the order in which Analytical Engine performs different kinds of calculations.
Can be set at: Project, Report, Template
Default Ordering
1. Subtotal
2. Compound Metrics
3. Consolidation
4. Metric Limit
User Defined Ordering
1. Compound Metrics
2. Consolidation
3. Metric Limit
4. Subtotal
What are the VLDB properties?Very Large Scale Database Properties.
Governing: Intermediate Row Limit, Maximum SQL/MDX size, result set row limit
Joins
Attribute to join when key from neither side can be supported by the other side
Possible Values: - Join common key on both sides, Join common attributes (reduced) on both sides
Base Table Join for Template: Controls how fact tables are joined, for a report containing metrics from different fact tables, or for a compound metric which has the base metrics coming from different fact tables. Controls whether temporary tables will be created for each metric or if fact tables will be directly joined.
Downward Outer Join Option: Controls how joins are performed when joining metrics which are calculated at different levels.
Full Outer Join Support: Controls the property which informs the Engine if a full outer join is supported by the Target database.
Preserve All Final Pass Result Elements: Controls how final pass result elements are joined to Lookup/Relationship tables
Preserve all Lookup Table Elements: Provides users the option to control if all lookup table elements should be included in the final results.
Metrics
Metric Join Type: Controls the type of join that is used to join a metric's data with other metric data on a report.
Default to Metric Name
Null Check
Query Optimizations
SQL Global Optimization: Determines if SQL should be optimized by combining multiple passes, and it should be optimized, controls the level of optimization
- Level 0: No optimization.
- Level 1: Remove Unused and Duplicate Passes.
- Level 2: Level 1 + Merge Passes with Different SELECT
WHERE clause driving table: Controls which table the Engine should use to apply the filter (WHERE clause). By default Fact Table is used
Intelligent Cubes
What is an Intelligent Cube?
In-memory version of report data that can be manipulated by the MicroStrategy Analytical Engine?
Types of Cubes Two unique methods to implement Intelligent Cube Technology:
•Personal Intelligent Cubes: You can begin by creating reports in MicroStrategy as usual, and then analyze your reports with OLAP Services features such as view filters, derived metrics, and derived elements. These features are processed on the in-memory copy of data known as a personal Intelligent Cube, rather than processed on the data warehouse.
•Intelligent Cubes: Rather than returning data from the data warehouse for a single report, you can return sets of data from your data warehouse and save them directly to Intelligence Server memory. These sets of data can be shared as a single in-memory copy, to be used by many different reports created by multiple users.
Activities on Cubes1. Dynamic Aggregation
2. Derived Metrics
3. Derived Elements
4. Metric Filters and View Filters
Advantages of Cubes
1. Fast Performance
2. Scheduling the Cube
3. Drilling
4. Data Sharing
Difference between Standard and OLAP reportsIf none of the OLAP features are used then it is a standard report, once any feature is added like view filter, derived metrics then its converted to a OLAP report. A Standard report can be converted to OLAP but not vice versa.
Difference between Personal Intelligent Cube and Intelligent Cube1. In PIC, Full access to re execute data against the warehouse but in IC , in order to re-execute against the warehouse , we have to drill on the data.
2. PIC is linked to a single report whereas multiple reports can access a IC.
3. Both view and report filters can be used in PIC but only view filters can be used in IC
4. In IC, prompts can be used only on objects included in IC but in PIC it can be applied even on objects not in the prompt.
5. Security Filters can be applied on both IC and PIC.
6. Consolidations and Custom Groups cannot be used in reports using IC but this can be achieved by using derived elements
7. Derived elements can be used only on IC not on PIC.
Features not supported in Intelligent Cubes
1. Consolidation and Custom Group
2. OLAP Service Features: View Filters and Derived metrics cannot be used
3. Prompts cannot be used
What is dynamic sourcing?Dynamic sourcing extends the accessibility of Intelligent Cubes by allowing standard reports to access any published Intelligent Cubes that can satisfy the data requirements of the report
How to Unpublish and Intelligent Cube?From the Folder List, expand Administration, then expand System Monitors, then expand Caches, and select Intelligent Cubes. The Intelligent Cube Monitor is displayed. Right-click an Intelligent Cube and select Delete. Unpublish only deletes data in the cube but not the cube itself.
When does the report fails due to the unavailability of Intelligent Cubes?
1. When I Cube is not published
2. When enough space is not there for publishing
3. Cube is in the process of publishing
4. Cube is offline
What are the different types of derived elements?
1. Group Derived: A Group derived element is a combination of attribute elements into a single derived element.
Eg: East Coast: Groups the Mid-Atlantic, Northeast, and Southeast attribute elements.West Coast: Groups the Northwest and Southwest attribute elements
2. Filter Derived: A Filter derived element uses a filter qualification to determine the combination of attribute elements for a derived element.
For Eg: Southern Regions: Returns attribute elements whose name begins with South.
•Northern Regions: Returns attribute elements whose name begins with North.
3. Calculation Derived: A Calculation derived element uses operators and functions to combine attribute elements and derived elements into calculations that define a single derived element
All other derived elements: Collects all attribute elements that are not inclulded in derived elements and includes them as individual attribute elements by default
Report Service Document
What is a document?A document displays your organization’s data in a format that is similar to a PowerPoint presentation, where several grid and graph reports can be viewed at the same time, along with images and text. High-quality, Pixel Perfect™ documents allow you to display your business data in a user-friendly way that is suitable for presentation to management for boardroom-quality material. Examples of documents include scorecards and dashboards, managed metrics documents, production and operational documents, and more
What are the different export formats?Excel, PDF, Flash, HTML
What are the different view modes?Flash View
HTML View
Design View
What is a dataset?
A dataset is a MicroStrategy report that defines the data that the Intelligence Server should retrieve from your data warehouse or from a cache that is available to the document.
What is a hyperlink?
A hyperlink connects text or an image in a document to a web page (the target of the hyperlink). When the document is open in PDF View, as shown below, the cursor changes to a hand when you hover the cursor over text that contains a hyperlink.
What is grouping of data in document?
If the data is grouped by page, drop-down lists are displayed at the top of the screen,
What is a layout?
A multi-layout document contains multiple documents, each in its own layout, creating a “book” of documents. Each layout functions as a separate document, with its own grouping, page setup, and so on, but the layouts are generated into a single PDF document. If a document contains multiple layouts, tabs are displayed at the top of the screen.
What is a dashboard?
A dashboard is commonly only one page long, is intended to be viewed online, and usually provides interactive features that let analysts change how they view the dashboard’s data. By being only one page long, a dashboard makes it easy to view the whole document at one time and see all the information. A dashboard allows interactivity from users, so each user can change how they see the data, within the limits of what the dashboard allows them. You must view a dashboard in Flash View in MicroStrategy Web to be able to interact with its widgets, selectors, and panel stacks
Difference between Dashboard and Scorecard - Click Here
What are the different display modes?
View Mode: view the results
Cannot create a new document.
•Cannot edit an existing document.
•Cannot manipulate any objects on the document, as you can in any of the other display modes.
Interactive Mode
Edit an existing document.
•View the results of the document.
•Use selectors to flip through the panels in a panel stack or display different attribute elements or metrics in a grid or graph report displayed on the document.
•Format grid and graph reports.
•Sort grid reports and pivot report objects on them.
•Add totals.
•Resize rows and columns.
•Create metrics based on report objects already on the grid report.
•Optimized for dashboard viewing.
Cannot create a new document.
•Cannot format the layout and positioning of objects or the entire document.
•Cannot format the Flash properties of widgets.
Flash Mode
Access and interact with features provided by Flash, such as widgets. Widgets are interactive Flash-only graphs, such as gauges and time series slides, that dynamically update when you select a new set of data.
•Format widget Flash properties.
•Edit an existing document.
•View the results of the document.
•Use selectors to flip through the panels in a panel stack or display different attribute elements or metrics in a grid or graph report displayed on the report.
•Sort grid reports and pivot report objects on them
Cannot create a new document.
•Cannot manipulate or format grid or graph reports, except to sort and pivot objects on them.
•If a graph report uses a graph type that is not supported in Flash, the graph is not displayed
What are controls?
Controls are the objects that display the data, images, and shapes in a document; they are the objects shown in the document’s Layout area as you design the document.
1. Text Fields
2. HTML container
3. Line or Rectangle
4. Image
5. Grid/Graph
6. Panel stack, which is a holder for a collection of panels, or layers of data, in a document. A user can navigate or flip through the panels in a panel stack; only one panel is displayed at a time.
7. Selector, which allows users to interact with the document, by flipping through the panels in a panel stack or by displaying different attributes or metrics in a Grid/Graph
8. Widget, which displays the results of a dataset report in Flash in MicroStrategy Web, allowing users to visualize data in different ways than traditional reports displayed as Grid/Graphs do
In documents, Information about the document (such as page numbers) and the dataset reports (such as report names and filter information), what is it called? –
Auto Text Codes
What are the different sections in a document?
1. Page Header and Footer
2. Document Header and Footer
3. Detail Header and Footer
What are the different types of text field in a document?
Static text: This text does not change and is commonly used for labels or descriptions. Examples in the sample document are the words “Employee” and “Revenue”. For directions to add static text, see Adding static text to a document
•Dynamic text: This text is automatically populated by the document or dataset. Dynamic text is always included within braces { }.
There are two types of dynamic text:
Data field
Auto text code
Eg: Date/time: {&DATETIME}
What are the different types of metrics that can be created within a document?Calculated expressions: A calculated expression is a metric that is calculated dynamically, when the document is executed, directly from metrics on a document dataset Eg: Revenue - Cost
Derived metrics: A derived metric is a metric that is obtained dynamically, when a document is executed, directly from metrics on a document dataset. A derived metric is created using at least one of the metrics in the document
Summary metrics.
What are the issues faced when exporting reports/documents to excel?
Choose Excel-compatible colors for objects as well as grid and graph formatting. MicroStrategy Desktop's basic set of 40 colors matches the Excel colors. Color from the Advanced Color Picker will be matched by Microsoft Excel with more or less accuracy.
Use graphs that are supported by Microsoft Excel. Examples of non-supported graphs are gauge graphs and combination graphs.
Lines and rectangles are not supported when exporting to Microsoft Excel. As a workaround, use a text field border to create a line or a rectangle.
A line graph within a Report Services document changes color when exported to excel. [Fixed in 9.3]
When exporting Report Services documents with objects that are overlapped, unexpected behavior is seen as Microsoft Excel does not support this feature and priority might be given to one of the overlapped objects.
Word-wrapping specified for multi-word object names does not take effect in PDF or Excel.
Custom line in a graph report is not carried over when exporting to Microsoft Excel as a Live Chart [Not sure if its fixed now]
The secondary axis on a dual axis graph report is not displayed when exporting the report to Excel format with the option "Export graphs as live Excel charts".Hence don't export a graph report as live chart in Excel format when exporting it.
Banding does not display correctly when exporting reports to Excel 2007 and above from MicroStrategy 9.2.1 Desktop and Web. Change the excel options such that you can export to older versions.
Prompt details are not fully displayed when exporting to Microsoft Excel 2007 from MicroStrategy Web 9.2.1.Change the excel options such that you can export to older versions.
Metric values will still appear despite column width set to 0 when exported to Excel 2007 in MicroStrategy version 9.2.1. Use excel 2003 to workaround.
Stacked Bar graph becomes Clustered Bar graph when exporting a Report Services document to Excel in MicroStrategy Web 9.2.1.
A Report Services document that includes attributes which use the date or datetime datatype. When the document is exported to Microsoft Excel, the attributes are recognized as plain text instead of dates, thereby inhibiting the use of certain pivot functions in Microsoft Excel native to the date format.
Category :For MicroStrategy BI environments configured to use database level security, publishing Intelligent Cubes could allow users to have visibility to data they should not see. To prevent this from happening, create the Intelligent Cubes by database connection. This will allow for your end-users to take full advantage of the in-memory performance of Intelligent Cubes. Below are steps to implement this in your environment.
For a MicroStrategy environment configured to use Database level security using MSTR connection mapping, Intelligent Cubes can still be utilized. To accomplish this, the first step is to turn ON this feature by selecting "Create Intelligent Cubes by database connection" in the Project Configuration editor (screenshot below).
Without selecting to create Intelligent Cubes by database connection, users may have visibility to data they should not see. By default, this check box is cleared.
Screenshot = Project Configuration editor
Configuration of the MSTR User and Connection Map via Command Manager
For each connection map, a corresponding MSTR DB Connection and DB Login must be assigned.
- Database Login - User Object used to store the user ID and password which will be used to connect to the data warehouse
- Database Connection - Specifies the DSN and database login used to access the data source
- Connection Map - Allows the Intelligence Server to map MSTR users and groups to database login IDs using a connection mapping
Sample Scripts below:
--------------------
mstr User = firm1
DBLOGIN = dblogin_firm1
DB Connection = DBConnect-Firm1
---------
/* ==== Create Firm User =======*/
CREATE USER "firm1" PASSWORD "firm1" FULLNAME "Firm1" IN GROUP "Firm1";
/* ==== Create DB Login, DB Connection and Connection Map and map them all together =======*/
CREATE DBLOGIN "dblogin_firm1" LOGIN "xxx" PASSWORD "xxx";
CREATE DBCONNECTION "DBConnect-Firm1" ODBCDSN "DSN NAME" DEFAULTLOGIN "dblogin_firm1";
CREATE CONNECTION MAP FOR USER "firm1" DBINSTANCE "<DB Instance Name>" DBCONNECTION "DBConnect-Firm1" DBLOGIN "dblogin_firm1" ON PROJECT "<Project Name>";
APPLY RUNTIME SETTINGS; /* ==== This step will apply changes w/o the need to restart the I-Server =======*/
Setting up the Intelligent Cube Cache Update via Command Manager
For each MSTR user to utilize the same Intelligent Cube, but maintain its own Cache, three configuration Objects will be required:
- Event - ETL Process will call a batch file to trigger this Event to update the firm's I-Cube Cache. Each firm will require an Event object to allow the ETL process to update the correct I-Cube
- Schedule - A schedule can be time based or event based. These Schedules will use the above Event to trigger the schedule. This one-to-one mapping of Schedule-to- Event will ensure the correct I-cube cache is refreshed
- Subscription - To automate Cache Updates, subscriptions will be created for each firm. This one-to-one Subscription-to-Firm mapping will ensure the correct I-Cube cache is refreshed
Sample Script Names:
--------------------
1. EVENT = Firm 1 Cube Refresh Event
2. SCHEDULE = Firm 1 Cube Refresh
3. CACHE UPDATE SUBSCRIPTION = Firm1 Cube Refresh
/* =====================================
Create three MSTR Configuration Objects - Event(1), Schedule(2) and Subscription(3) for each New Firm (This in only run once, when a new Firm(mstr User) is onboarded.
=====================================*/
CREATE EVENT "Firm 1 Cube Refresh Event" DESCRIPTION "Trigger for Firm 1 Cube Refresh";
CREATE SCHEDULE "Firm 1 Cube Refresh" STARTDATE 09/10/2010 ENDDATE NEVER TYPE EVENTTRIGGERED EVENTNAME "Firm 1 Cube Refresh Event";
CREATE CACHEUPDATESUBSCRIPTION "Firm1 Cube Refresh" SCHEDULE "Firm 1 Cube Refresh" USER "firm1" CONTENT "<cube name>" IN FOLDER "<cube location>" IN PROJECT "<Project Name>" DELIVERYFORMAT HTML SENDNOTIFICATION FALSE;
/* =====================================
Update I-Cube - This CM script will be called from batch file; the batch file will be called from ETL Job.
=====================================*/
TRIGGER EVENT "Firm 1 Cube Refresh Event";
Author: Meenu Pall
: Data VisualizationI’ve had the opportunity to speak about my profession as a designer in a data-intensive field and I've always tried to give credit where credit was due. By that I mean visualization should always be about the data and the story it needs to tell. Design and aesthetics should aid the core message, not the other way around.
It’s easy for a designer to make something look better and receive all the credit, but just because something looks better, doesn’t mean it is better. In fact, it’s usually the opposite and that something that is aesthetically pleasing may be masking the real issues with a visualization or dashboard. Besides all of that, what makes something “look better” is a completely subjective thought. People have different tastes, backgrounds, and cultural biases and will naturally favor different things. I believe the way to please all of these people is for you to rely on the data, not the decorative elements.
What is a “decorative element?”
A decorative element is an object that is visual in nature and serves no useful purpose in a visualization or dashboard. There are useful visual elements that I encourage people to take advantage of. These elements are meant to aid the end-user in the consumption of information. Once these elements become distracting, they are decorative and are no longer useful.
Examples of Decorative Elements
- Company Logos
The most common placement of your company’s logo in the top left-hand corner of your dashboard. This is the 2nd worse place to put a logo, as the most valuable real estate of any information intensive display is going to be the top left-hand portion and should be used to display data or display controls. The only thing worse is placing a logo directly behind a visualization (see #2 below).
If it is necessary to use a company logo, consider putting in the bottom right-hand corner of the display. It meets the requirement and the chance of it being distractive to the consumption of the data is very unlikely.
The company logo occupies the display's prime real estate. The space is better served to display meaningful data or display controls.
Simple rearranging of the layout creates a display that is more impactful for the end-user. It also gives us more space for other visualizations.
- Background Imagery or Graphics
Using background imagery or graphics within visualization is a cardinal sin of information design. In all my years of industry experience, I have not encountered one exception where the visualization was better off or easier to interpret because a background graphic was used.
While not intended, the background graphic distracts the end-user from the information they're trying to interpret.
Clean and simple visualizations will never distract your target audience from getting the information that’s being presented.
- Ornate Graphics
This is related to #2 above, but it is not specific to being in the background of visualization. This example is about using graphics as points of focus instead of secondary visual aids. If the graphic being used does not add to the ease of information consumption or user experience, then it’s not appropriate to use.
The illusion of a shelf on the lower area of the screen may look nice, but it’s ornate in nature. It’s not helping anything the dashboard is trying to accomplish.
It is possible to use graphics without them being useless and distracting. Subtle use of color and shapes will naturally cluster visual elements together. This can enhance the overall user experience.
Design and aesthetics are subjective and always will be. Ultimately the design choices you make are going to depend on requirements, nuances of the technology you're using and the tastes of your customer or business sponsor. Understanding and embracing these principles will assist your visualizations and dashboards in becoming adopted and embraced. As you communicate and illustrate these principles remember to justify and validate the purpose of every graphical element. Sometimes your designs will be applauded, sometimes they will not, but at least you tried and your arguments came from a place of substance and not self-opinion.
Look for the next blog in the series: The Basics of Color Theory
Author: Meenu Pall
A project I was working on encountered an issue where we tried to pass several prompt answers through parameterized links from one cube-based dashboard to another. We were able to get the normal methods to work, however, when the “all” option was selected, it didn’t function as desired. By not enabling, we would be sacrificing the end user experience; something we were determined not to do.
For example: using the link editor and selecting the “answer prompts dynamically” option worked, but if a user had a selector set to “all” it would not answer that prompt. We tried numerous approaches to fix this and have the ability to pass “all” as a prompt answer, including, using value prompts, setting up our prompts directly in the report, but the method outlined below was the solution that worked for us in the end.
Step 1 - Set Up Your Prompts
- Right Click > New > Prompt
- Select "Filter Definition Prompt" and then "Choose from an attribute element list." Click Next.
- Choose your attribute. Click Next.
- Make sure "List all elements (no restriction)" is selected. Click Next.
- Make sure "Prompt answer required" is unchecked. Click Next.
- Do not select any default answers.
- Finish and Save.
Step 2 - Add your prompts to your report for the Child Dashboard
- Right Click on the Report > Edit
- Under the "View Filter" box, click "Click here to start a new qualification"
- Click on "Field" and scroll all the way to the bottom and choose "Select a prompt..."
- Find the prompt you created. Click OK.
- You should now see "Shortcut to: [your prompt name here]"
- Repeat for all necessary prompts. Save and Close.
- On your Parent Document - Right Click > Edit
- Create your Selectors. Make sure "Apply selections as a filter" and "Show option for All" are checked. Also, ensure that "Current state" is set to "Unset (no filter)" and the Selection type is set to "Include".
- Repeat for all necessary selectors. Save.
- In the Panel Stack that your selectors are in, "Create a Panel Stack" to place your link in.
- Place text or an image to be used as your link. Save.
- Find your selector :"nodes"
a. Open the Parent Document in Editable Mode
b. View > Source
c. Use Ctrl+F to help find the proper area in the source code. Type in the attribute name of the selector. You’re looking for the “<span id=”###”” that belongs to each selector. Write down the number you find for each for. For example in this source code the Node Number for Brand is K72.
d. Repeat for all selectors.
- Find the link to your Child Document.
a. In MicroStrategy Web, Right Click on the Child Document > Show Link
b. Copy and paste the link into a text editor.
c. Remove everything before "main.aspx?...."
d. Also remove “Server=____” This will prevent the link from going back to the wrong server after migration.
You link should now look something like this:
Main.aspx?&Project=MicroStrategy+Tutorial&port=0&evt=2048001&src=Main.aspx.2048001&visMode=0&documentID=039EDS36587CV6S8E4S68AZ354¤tViewMedia=8
e. Add “&elementsPromptAnswers=” to the end of your link
f. Using your nodes construction the rest of your link using the following method:
{&Attribute@GUID};{&CurrentSelectionElementID:Node#&}
For example:
{&Brand@GUID};{&CurrentSelectionElementID:K72&}
Note: If your Attribute name is more than one work surround the ID with square brackets like so:
{&[Brand Family]@GUID};{&CurrentSelectionElementID:K72&}
g. Using comma’s to separate, add all of the ID Syntax’s to the end of your link. For example:
Main.aspx?&Project=MicroStrategy+Tutorial&port=0&evt=2048001&src=Main.aspx.2048001&visMode=0&documentID=039EDS36587CV6S8E4S68AZ354¤tViewMedia=8&elementsPromptAnswers={&Brand@GUID};{&CurrentSelectionElementID:K72&},{&Category@GUID};{&CurrentSelectionElementID:K67&},{&Year@GUID};{&CurrentSelectionElementID:K89&}
Step 6 - Add your link
- Open your Parent Document - Right Click > Edit
- Right Click on your link text or image > Properties
- Under the General Tab, check "Is a Hyperlink" and paste your Syntax in the provided box"
- Click OK. Save and Close.
With this method we were able to give the client their desired ability to pass selector from one dashboard tier to the next. Allowing them to effectively drill down through the data in a linear fashion, without having to reselect filters or answer prompts. Thus, creating a seamless navigation process throughout all the dashboards.
Knowlwdge Neywork Microstrategy
Author Meenu Pall
What is metadata?
The database repository where definitions of all MicroStrategy objects are stored. Metadata could be hosted on most databases. In simple words, Metadata could be considered as the heart of MicroStrategy environment.
MicroStrategy metadata contains datawarehouse connection information, project settings and microstrategy object definitions.
What are the tasks that you can perform with the MicroStrategy Architect?Initially populate the metadata with project definition and parameters, schema objects and create schema objects
What is difference between 2 ,3 , 4 tier connection?
In 2 tier architecture, the MicroStrategy Desktop itself queries aganist the Data warehouse and the Metadata with out the Intermediate tier of the Intelligence server.
The 3 Tier architecture comprises a Intelligence server between MicroStrategy Desktop and the data Warehouse and the Metadata.
The 4 tier architecture is same as 3 tier except it has a additional component of MicroStratey Web.
Intelligence Server is the architectural foundation of the MicroStrategy platform. It serves as a central point for the MicroStrategy metadata so you can manage thousands of end user requests.
You are very limited in what you can do with a 2-tier architecture. Things like clustering, mobile, distribution services, report services, OLAP services, scheduling, governing, I cubes, project administration are only available via Intelligence Server.
When a change is made in 2 tier – is it reflected in 3 tier?
No it is not reflected. Following are the methods by which we can reflect it in 3 tier.
· Update the schema in 2-tier. Restart the MicroStrategy Intelligence Server. Recommended method by MSTR.
· Update the schema in 2-tier and reload the project from the Project Monitor.
What is heterogeneous mapping?There are no restrictions on the names for the columns used in the expressions of a given attribute form. Heterogeneous mapping allows the engine to perform joins on unlike column names. If the user defines more than one expression for a given form, heterogeneous mapping will automatically take place when tables and column names require it.
For example, because different source systems store Date information in various contexts, a data warehouse may have multiple columns in different tables which all represent the concept of 'Date'. The ID form of the attribute 'Date' may contain two expressions. The DATE_ID column occurs in the LU_DATE table as well as in two other tables. The elements in these columns correspond to elements in the ORDER_DATE column in the order_detail and ORDER_FACT tables.
Heterogeneous Mapping of Attribute Forms.
Why do we go for parent child relationship?While establishing the relationship between attributes one can either look from business hierarchy point of view and the attribute higher in the hierarchy becomes parent of the attribute lower in the hierarchy. Parent and Child follow a one-to-many relationship. Example Time hierarchy Year > Month > Date. Here Year would be parent of Month and Date and Month parent of Date.
We can also identify Parent-Child relationship from database design point of view. Here in a table the Primary Key uniquely identifies the other columns in the table and hence qualifies as child of all the other attributes from the table, in the same ways as a child in real world identifies his father (at least the biological one).
What is a compound attribute?A compound attribute has its value determined by an expression which combines two or more columns in a database to create a new column
What are different kinds of objects in Microstrategy?Configuration Objects: Configuration objects are MicroStrategy objects which can be re used in multiple projects and they appear in the system layer. Ex: Database Instances, Users, Login ID's, Schedules
Schema Objects: The building block of BI. Schema objects are directly mapped to a column or columns in the database. Attributes, Facts, Functions & Operators, Hierarchies, Partition Mappings, Tables & Transformations
Public Objects: Objects that generate analytical data and are built on other schema objects or public objects. Also called as application objects. Ex: Consolidation, Custom Groups, Drill Maps, Reports, Documents, Filters, Prompts, Metrics, Templates and Searches
How to create a conditional attribute in MicroStrategy Desktop
A user may want to create an attribute with an alternating expression depending on a certain condition, a conditional attribute. This condition may be implemented through an ApplySimple statement such as the following:
Types of Attributes
Simple
A simple attribute is made up of one or more expressions. With a simple attribute definition, you can define an attribute as a column, constant, or simple expression.
Implicit Attributes
An implicit attribute is a virtual or constant attribute that does not physically exist in the database because it is created at the application level. The implicit attribute has its own expression.
Derived Attributes
A derived attribute has its value determined by an expression which combines two or more columns in a database to create a new column.
Compound Key Attribute
A compound key attribute is an attribute whose primary key is made up by the combination of two or more columns.
What is a Implicit Attribute?
An implicit attribute is a virtual or constant attribute that does not physically exist in the database because it is created at the application level. The implicit attribute has its own expression.
What is a joint child?
A joint child is Microstrategy way of handling Composite Keys. Composite keys are constituted of two or more columns which together act as unique identifier. To handle this case in Microstrategy we make this set of columns, constituting composite keys, as joint child.
What are attribute roles?
A user defines two attributes that have the same definition but play different roles in the business model. In this example, attribute Origin Airport and Destination Airport are defined using the same Lookup Table and Column (Airport_ID). Both attributes share the same forms, or information about them (Description, Location, etc.). In the fact table, however, a separate column exists for each of their roles (Origin_Airport_ID and Destination_Airport_ID).
When should attribute relationships be modeled as separate attributes in a parent-child relationship and when should they be modeled as forms of the same attribute?
It is preferable to use separate attributes that are related hierarchically (that is, parent-child relationships) for the following reasons:
Attributes that exist in a hierarchical relationship can appear independently of each other on a report. If 'Item' and 'Item Category' are modeled as separate attributes, reports may then be designed to report on individual items or whole categories. If 'Item Category' is considered a description (form) of 'Item', it becomes impossible to report on 'Item Category'.
Attribute forms are not available as metric dimensionality settings. In order to aggregate data at a particular attribute level, that attribute must exist as an attribute. If the attribute is modeled as an attribute form instead, it is possible to aggregate only at the level of the attribute containing the form.
Attribute forms are not appropriate under the following circumstances:
· When the attribute must be used as an aggregation level (metric dimensionality). For example, customer and state: if a user wishes to calculate sales totals by the states in which customers live, state should be a separate attribute as a parent (or grandparent, and so on) of customer.
· When the attributes exist in a one-to-many or many-to-many relationship. For example, customer status: Presumably, each status will apply to several customers. Modeling status as a form of customer makes it always subordinate to customer, which may impose unnecessary limits on reporting options.
How are the drilling options for an attribute decided?Based on relation between attributes, hierarchies and their drilling configuration
What are the two types of Hierarchies?
System hierarchy: It contains all the project attributes and its available browse paths and is based on relation between attributes.
User defined Hierarchy: Custom grouping of attributes and define their browse paths.
FactsWhat is fact extension?
• You can use level extensions to change a fact level, which is a set of attributes that represent the lowest level of detail at which the fact exists in the warehouse.
• Level extensions define how facts can be extended, lowered, or disallowed to other facts across the schema.
What is fact degradation?
When facts exist at a higher level than the report display level, you must specify how the Engine degrades the data to the lower level. When you lower the level at which a fact is reported, you are using degradation.
Types of facts
Simple facts
A simple fact is made up of one or more fact expressions. With a simple fact definition, you can define a fact as a column, constant, or simple expression.
Implicit facts
An implicit fact is a virtual or constant fact that does not physically exist in the database because it is created at the application level.
Derived facts
A derived fact has its value determined by an expression that combines two or more columns in a database to create a new column.
Metrics What are different types of metrics?
• Simple : Simple metrics combine aggregate operators with fact columns or attributes.
• Nested: Metrics that perform multiple aggregations by placing one calculation formula inside another
• Compound : A compound metric is a combination of expressions that, through the use of functions, are themselves metrics.
• Derived
What is Base Formula? Use a simple expression as a base formula to facilitate the creation of more complex metrics.
What is smart metrics?Compound metrics are the ones that are derived by some specific expression involving the different simple metrics. Eg, Total( profit/units Sold). Smart metrics is when the compound metric is calculated with the help of subtotal calculations for every element inside the compound metric. For the above example the smart metric computation can be Total(profit)/Total(Sold).
What is level metric?Level metrics are advanced metrics which are set to be evaluated at a specified attribute level. These are required when in the same report you need to roll up a metric at two different levels side by side. Example is comparison of “Revenue from a Region” to “Revenue from a Country”. Here Region and Country are the two different levels.
The level of a metric, also referred to as dimensionality, allows you to determine the attribute level at which the metric is calculated.
Default – Report Level
The elements needed to specify a level for a metric
Target - Attribute level at which the metric
Grouping - Determines the metric aggregation.
Filtering - governs the relationship between the
report filter and the calculation of the metric.
What is purpose of having conditionality in metrics?
Conditionality associates a filter to the metric calculation. This is an optional component.
What are the different components of metrics?
• The formula defines the data to be used and the calculations to be performed on the data. The outermost formula must be a group function.
• The level, or dimensionality, determines the level at which to perform the metric calculation. For example, you can choose to calculate at the month level or year level.
• Conditionality associates a filter to the metric calculation. This is an optional component.
• The transformation applies offset values, such as “four months ago,” to the selected attributes. This is also an optional component.
What is the purpose of transformation in Metrics? Types of transformation.
It encapsulates a business rule used to compare results of different time periods.Transformations are used in the definition of a metric to alter the behavior of the metrics.
• Expression - based transformations – You implement these transformations using a mathematical formula in Microstrategy Architect.
• Table - based transformations – These transformations are based on a transformation – or relate –table in the warehouse.
What is dynamic Aggregation?Rollup metric values that occurs when an attribute is moved from the report grid to the report objects.
For Eg: The report grid has Quarter and Customer City, Revenue. If we remove Quarter into the report objects then revenue should automatically roll up to Customer City.
How to ensure that a particular fact table will be chosen for a metric
The MSTR operates in such a way that the incoming queries and data retrievals are done from the table which has the least logical size. Thus if we prefer a specific table to be the center of activity then we should try to reduce the logical size of that specific table so that it becomes considerably lesser than the other tables.
In Microstrategy, how can you direct the sql generated to use a specifc table?
Using the Level parameter in the Metric level options
Assuming you have OLAP licence,the easiest way to direct to a particular table is to create a dummy fact on the table,include the fact in metric and put the metric in report objects.
How to hide a particular metric in a report for a specific user?Using Object level security
What is Metric Formula Join Type? How it is different with Metric Join Type?
Metric Formula Join Type is used for Compound Metrics and determines how the different tables used in metric formula are joined.
Whereas the Metric Join Type determines how the metrics are joined to other metrics.
FiltersWhat is filter?Filter is used to restrict data in a report
What is report as filter?In the MicroStrategy when the same filter conditions must be applied to multiple passes, the same where clause appears in each of those passes. This redundant where clause can be expensive if the filter conditions are complicated and thus involve many tables and joins. Ideally, an intermediate table populated with entries could be created to satisfy the complicated filter conditions so that the rest of the SQL statements can use that intermediate table. In that case, the where clause would be executed only once instead of multiple times and SQL performance would be improved. In this case to populate the temporary table we can use report as a filter
What is view filter?View Filters: View Filters are the conditions that come into play before a specific result is presented to the user. Thus the view filters are not part of the SQL statements like report definitions, rather they are the filters applied after the execution of the SQL statements, after the data is retrieved from the data warehouse.
What is filtered prompt?
We can restrict the number of elements in a prompt using a filter.
Difference between report and view filter?Report filters: report filters are the conditions that accompany the report generating SQL statements. The report definitions have the filters as part of their definition.
View Filters: View Filters are the conditions that come into play before a specific result is presented to the user. Thus the view filters are not part of the SQL statements like report definitions, rather they are the filters applied after the execution of the SQL statements, after the data is retrieved from the data warehouse.
Difference between Report Limit and Report filter?A report limit specifies a set of criteria used to restrict the data returned in the report data set after the report metrics are calculated.
Report Filter applies the where condition to the query sent to warehouse to extract the results.
What is the difference between Absolute filtering and standard filtering?When we use the absolute filtering in definition of level metric whatever data we obtain from the filter is goingto be reported as such and the the report filter will be overridden by the absolute filter settings. The standard filtering the report filter interacts with the metric filter in the normal way and what we obtain will be formatted according to the report filter settings.
What is a Joint Element List Filter?
Helps to choose combination of attribute elements from different attributes to filter a report
Enables to create filters with attribute pairs as well as triplets, quadruplets and so on.
This is available in the advanced qualification section of the filters
What is a Security filter?Security filter is used to apply security at the database data level.Whenever a users associated with security filter runs a report, a WHERE clause is always included in the report sql with the condition defined in the Security Filter.
What is a Attribute to Attribute Filtering?This is used to compare values of 2 attributes using their forms.
Eg: Ship Date<Day(ID)+2
PromptsWhat is prompt?
• Used to dynamically modify the contents of a report
• Enormous flexibility for designing reports
• One report can satisfy multiple reporting requirements with prompts
• Allow users to select criteria for reports at run-time
Different types of prompts?
1. Object: Allow users to select objects comprising a report at run-time
Users can select from any objects to dynamically build a report
2. Level: Level prompts enable you to specify the level of aggregation calculation of a metric
3. Value: Allow users to type a value
Used in conjunction with other prompts for filters or metrics
4. Filter Defintion Prompt
a. Choose from all attributes in a hierarchy
b. Qualify on an attribute
c. Choose from an attribute element list
d. Qualify on a metric
What is object prompt?An object prompt allows you to select which MicroStrategy objects to include in a report, such as attributes, metrics, custom groups and so on. Object prompts can either determine the definition of the report template or the report filter.
What is level prompt?
Level prompts enable you to specify the level of aggregation calculation of a metric
Can we use hierarchy in prompt?
Yes we can use.
ReportWhat is the purpose of having thresholds in report?
Used to create conditional formatting for metric values.
Two metric and one attribute – How will the SQL look if the metrics are from same tables and are from different tables?
If the two metrics are sourced from the same table then only one pass will come.
If they are sourced from different fact tables then a pass will be generated for each metric and in the final pass the data will be aggregated,
Difference between drill map and drill path. What is drill to template
Drill maps allow you to create fully customized drill paths that are available to your users while drilling on a report. By default, the paths available are based on the system Hierarchy of the project. You can create custom drill maps that can override these defaults.
Difference between Consolidation and Custom Groups. How will they affect SQL ? Which is more expensive operation.Custom Groups are handled at the database end where as Consolidations are handled at the Analytical Engine end. As a result the Consolidations are not an overhead for the database as there is a single pass in the query. On the other hand Custom Groups are an overhead on the database as they fire a separate SQL pass for every Custom group element.
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.
Difference between page by and outline mode?
Page by enables to select and display subsets of report results as separate pages.
Outline mode enables indented grouping of related attributes This is useful when we want to display the same report at different levels quickly.
How can we enable caching in reports? Different types by which we can implement caching?Caching can be enabled/disabled at project or report level. However the report level setting will override the project level setting.
Using Project Configuration we can enable the caching else in report – using caching options we can enable or disable the caching.
Templates can also be used for caching. Using a common template for developing common reports will help to fetch report data faster.
Can Threshold be applied on attributes? What are the different formatting types?1. Font Type, Color
2. Background
3. Image
4. Text
What are adhoc reports and static reports?
Adhoc reports run in real time based on the input parameters provided by the user at the run time.In Microstrategy, adhoc reports are created using Prompts.
In static reports, users won't be provide any input parameters.These reports are usaully schedule to run overnight and ready to view immediatley in the mornings using cache.
TablesHow to handle table structure change in DB to be reflected in MSTR?
Update the warehouse catalog.
Modify the data type in attribute editor.
When a column name changes in a table how do we handle it in MSTR
1. Remove the association of the column from the respective attributes.
2. Go to warehouse catalog and update the structure
3. Update the schema
4. Associate the attributes to the new column name. Save
5. Update the schema
How to we add tables into a schema1. Warehouse catalog
2. Architect
When you add a physical table from the datawarehouse into the project, MSTR creates a corresponding logical table in the metadata. Physical table stores the actual data whereas the logical data stores information about their corresponding physical tables including column names, data type and schema objects associated with the column names.
There are two views - the physical view and the logical view in the table editor. The logical view shows the attributes and facts mapped whereas the physical view shows the columns and corresponding data types.
What is a logical size of a table and what does it depend on?
Logical size is Microstrategy way of generating the best suitable/optimized SQL to fetch the required data. Microstrategy follows an algorithm to calculate the logical size of a table, which depends on the no of attributes and facts based on the table and also the position of those attributes in the system hierarchy.
What are Logical Views used for?
Logical Views allows application architects to create any desired view using MicroStrategy, without DBA involvement. Once these Logical views are created, they are available to the report designer in a way similar to any other table. This allows developers to model attributes and facts whose expressions span multiple tables
Other QuestionsWhat is the data modelling tool in MSTR called?
Architect
How do we migrate objects across projects?
Object Manager
What is the difference between object manager and project merge?
- Object Manager can move just a few objects or just the objects in a few folders. Project Merge moves all the objects in a project.
- Object Manager must locate the dependents of the copied objects and then determine their differences before performing the copy operation. Project Merge does not do a dependency search, since all the objects in the project are to be copied.
- Project Merge can be run from the command prompt in Microsoft Windows.
The portion of the interface that allows users to retrieve the results of previously executed or scheduled reports.
What is difference between purging and deleting cache?
Caching allows for improved performance in response to report queries. Although the use of caching is an advantage, there are instances when caches may expire or become invalid. Some of these instances are:
When there are changes made to the objects in the data warehouse, the existing caches may be configured so that they are no longer valid when hitting certain warehouse tables. Any further report execution will no longer hit the cache.
When the definition of an application object changes (such as a report definition, report, template, metric definition, etc.), the related report cache is marked as invalid.
When there is a need to control the growth of caches on the Microstrategy Intelligence Server, old caches may need to be expired automatically.
Invalidating Report Caches
Invalidation is a preventive measure that renders a cache unusable by nullifying it. Cache invalidation only applies to Matching caches and Matching-History caches. It makes the cache ineligible in the matching process so it is not used to fulfill a report request.
In the case of a Matching cache, invalidating it will automatically result in deleting it.
In the case of a Matching-History cache, invalidation simply converts it to a History cache that is not used in the matching process any more but is still accessible through History List messages that reference it. However, if all these messages are deleted, the converted History cache that is referenced is also deleted.
Expiring Report Caches
Cache expiration is a process that renders a cache unusable by terminating its useful life. It yields the same results as invalidating a cache and applies to Matching caches and Matching-History caches. Cache expiration occurs automatically as per the 'Report cache duration' setting which can be found under Project Configuration > Reports >Advanced > Caching.
When a cache is updated, the current cache lifetime is used to determine the cache expiration date based on the last update time of the cache. This means that changing the Report cache duration setting does not affect the expiration date of the already existing caches. It only affects the new caches that are being or will be processed.
Deleting Report Caches
Cache deletion is a process that deletes the cache from memory as well as disk. Report caches are automatically deleted by MicroStrategy Intelligence Server if cache invalidation and History Lists are performed and maintained properly.
In the case of a History cache, deleting it does not automatically delete the associated History List messages that reference it. After deleting a History cache, when a message that references it is retrieved, the following occurs:
MicroStrategy Web users see an error message, "Execution results not available. Would you like to re-execute?"
MicroStrategy Desktop users do not see the above error message because MicroStrategy Desktop automatically resubmits the report for execution.
On the other hand, a History cache is automatically deleted when all the History List messages that reference it are deleted (when its History List reference count reaches 0).
In the case of a Matching-History cache, when all the History List messages that reference it are deleted, it is simply converted to a Matching cache, while losing its History component.
Caches can be deleted:
Manually - via Cache Monitor and MicroStrategy Command Manager
Scheduled - via MicroStrategy Cache Administration Utility
Scheduled - via MicroStrategy Desktop Scheduled Administrative Task.
Purging Report Caches
Cache Purging is a process whereby all report caches can be deleted in bulk, even the one references by History List messages.
How to apply row level security?Applying Security filters to the user. This provides row level security
When do we go for schema update?
When any of the schema objects are modified – Attributes/Facts/ Transformations/Tables/Hierarchies/Partitions
When New Tables are added to the schema using warehouse catalog.
When you update the structure of existing tables in the warehouse catalog.
What are VLDB properties?VLDB stands for Verly Large Data Base Properties. This is Microstartegy way of handling database specific preferences while generating the report SQL. There are number of them. A few common one are for Attribute or Metric join types, cross join check, type of intermediate table, etc.
At which levels you can set the VLDB properties? Which level has the highest pecedence?VLDB Properties can be set at various levels like Report, Template, Metric, Project, Database Instance and DBMS level. Out of this Report level has the highest priority. It overrides all other levels.
What are the various ways of incorporating security in Microstrategy?
In Microstrategy security can be incorporated using a mix of any of the following ways:
- Putting user specific restrictions at the database end and using user specific connection mapping. This is for column level security.
- Applying folder and object level security to restrict access to certain set of reports/objects
- Applying Security filters to the user. This provides row level security.
Folder Level
Row Level
How conflicts occur and what the ways to resolve them?
When copying objects across projects with Object Manager, if an object with the same ID as the source object exists anywhere in the destination project, a conflict occurs.There are various ways to resolve depending upon the conditions like use existing, replace, keep both, use newer, use older, update in same path, update in new path and merge privileges.
What is the command manager used for?
The command manger is the one through which you can manage the applications, user accesses, security and databases of the microstrategy. The command manager allows us to save the text commands that can be executed as scripts. Thus it can help in automation of the entire management process.
What are the nulls on microstrategy report and how to mask them.
1)If in this question we want to deal with null in MSTR report than we have some display property in report data option which we can change according to the replacement of null values.
DATA>>REPORT DATA OPTION>>DISPLAY>>NULL VALUES
2)if we want to deal with sql geration of report than there is an option of VLDB property according to you requirment you can deal with sql query of report
Servers in MSTRI-server: MicroStrategy Intelligent server provides jobs management and analytical processing for all MicroStrategy applications. This acts as a central component connecting the metadata, warehouse, desktop, Web server and Narrow cast Server. Few or main features: Reports Services, OLAP Services, Data Mining, Multi Source connection, Caching, Clustering. Latest version supports installing I-server (different packages) on Windows, Sun Solaris, IBM AIX, HP-UX, Linux. Contact MicroStrategy for more information.
Web Server: MicroStrategy web server responds to the requests from browsers. Web server interacts with the I-server to extract the necessary information. Can be installed on most of the major web servers and supports most popular browsers. Contact MicroStrategy for certified products.
Narrow cast Server: Narrowcast sever delivers personalized business insight to emails, cell phones, pagers, file servers and print servers extending the reach of Business Intelligence applications. It offers a comprehensive solution for information delivery integrating a subscription portal with a delivery engine.
Roles in MSTR· Administrator: By default, the role/person will have full access to the environment. In other words this role has full access to all the type of objects mentioned above.
· Architect: By default, access to configuration objects is restricted.
· Developer: By default, no access to configuration objects, use access to schema objects and full access to public objects.
What are Passthrough Functions?
Pass through functions are used to utilize various special functions that specific to databases.Some of the passthrough functions available are Applysimple and Applycomparision.
What is the difference between warehouse and metadata partition mapping in MicroStrategy Desktop
Warehouse Partition Mapping:
Warehouse Partition Mapping tables are used for performance reason.
For example, a fact table in the data warehouse may have two columns: Sales and Years. If a report is run with Sales for the Year = 1999, the query engine will need to search through the entire table for all the years, including 1999, to return the data.
To improve efficiency, Partition Base Tables (PBT) can be created to have Sales for particular years. Assuming that there are 10 years worth of data in the database, 10 different partition base tables need to be created:
- PBT1: Sales for 1991 (Year=1991, Sales=$)
- PBT2: Sales for 1992 (Year=1992, Sales=$)
...
- PBT10: Sales for 2000
A Warehouse Partition Mapping Table (PMT) will then need to be created and will have:
Year
PBTName
1991
PBT1
1992
PBT2
...
2000
PBT10
The above PMT is going to tell the engine which table to grab the data from for a particular year. This means that when the report is run for Sales for 1997, then the query engine will first go to the Partition Mapping Table and then find the correct PBT corresponding to the year 1997.
Metadata Partition Mapping:
Metadata Partition Mapping Tables map the attribute elements to its corresponding PBT and performs the same function as the PMT.
To create a Metadata partitioning, follow the steps below:
- Go to Schema Objects > Partition, in MicroStrategy Desktop 8.x.
- Right-click and select new > Partition.
- Select the PBT. (i.e., PBT1)
- A data slice can be defined. The data slice will be the attribute (i.e., Year) to join from this metadata PMT to the PBT.
- Save and close.
Types of Report CachesThere are two categories of report caches, Matching and History
Based on these two categories, the following types of report caches are displayed in the Cache Monitor:
- Matching caches
- History caches
- Matching-History caches
- XML caches
When a report is run in a MicroStrategy 8.x project, with report caching enabled, the Intelligence Server determines for each report request whether it can be served by an already existing cache. If there is no match, it then runs the report on the database and creates a new cache. The type column for this cache on the cache monitor will be 'Matching.'
Matching-History caches
When a report is sent to history directly instead of being executed, the type column in the cache monitor will be 'Matching, History.' Matching-History cache is a Matching cache with at least one History List message referencing it. It is actually one cache with two logical parts: Matching and History.
History caches
The following two circumstances result in the Type column displaying only 'History':
- Invalidation of a report cache after is has been sent to History: When a report is sent to History, the Type column in the Cache Monitor for this cache entry, will display 'Matching, History'. The Type column of the cache changes to "History" if the cache is invalidated. The cache is invalidated when report definition is changed and user saves the modified report. Administrator can also invalidate cache by right clicking the cache entry in cache monitor and choosing 'Invalidate Cache'.
- When report caching is not enabled and the report is sent to History: Report caching is not enabled for the project as evidenced by the setting 'Enable report server caching' in Project Configuration > Caching, being unchecked. When a report is run, no cache will be created. Under these circumstances, when a report is sent to History, there will be an entry in the Cache Monitor and its Type column will display only 'History'.
XML Caches
When a report is executed from Web, an XML cache for this report is created in XML format. It is available for reuse on Web later on. It is possible that the XML cache is created at the same time as its corresponding normal report cache. Although just a different format of the same report cache, the XML cache is maintained as a distinct cache and thus counts towards the maximum number of caches as an independent unit. It is automatically removed when the associated report or History cache is removed.
Types of Caches1. Element Cache
Used by attribute element list.
When is it created?
1. Browse attribute elements when browsing a hierarchy
2. Browsing a prompt
3. In Filter Editor
2. Object Cache : when you open the editor of an object
When is it created?
1. Opening a report editor
2. Opening a attribute/fact /metric editor
3. Report Cache : when executing a report
Matching and History - Types
4. Document Cache : when executing a document
How to resolve many to many relationships?To resolve a many-to-many relationship means to convert it into two one-to-many, many-to-one relationships. A new entity comes between the two original entities, and this new entity is referred to as an intersection entity or cross reference entity. It allows for every possible matched occurrence of the two entities. For example, the "many-to-many" relationship of many EMPLOYEEs are assigned many TASKs which can be resolved by creating a new entity named EMPLOYEE_TASK. This resolves the "many-to-many" relationship by creating two separate "one-to-many" relationships. The two "one-to-many" relationships are EMPLOYEE or parent entity which is assigned EMPLOYEE_TASK or child entity and TASK or parent entity is assigned to EMPLOYEE_TASK or child entity. Whilst this may appear complex, the introduction of the EMPLOYEE_TASK child entity reduces data redundancy and improves overall database and application performance.
Major Differences between 8 and 9
1. Distribution Services is new in 9
2. Ability to create prompts and filters in web
3. Drilling in Documents
4. Dashboards with multiple layouts
5. Intelligent Cubes
6. Back and Forward buttons in web
7. Personalised prompt answers
What is evaluation ordering?Determines the order in which Analytical Engine performs different kinds of calculations.
Can be set at: Project, Report, Template
Default Ordering
1. Subtotal
2. Compound Metrics
3. Consolidation
4. Metric Limit
User Defined Ordering
1. Compound Metrics
2. Consolidation
3. Metric Limit
4. Subtotal
What are the VLDB properties?Very Large Scale Database Properties.
Governing: Intermediate Row Limit, Maximum SQL/MDX size, result set row limit
Joins
Attribute to join when key from neither side can be supported by the other side
Possible Values: - Join common key on both sides, Join common attributes (reduced) on both sides
Base Table Join for Template: Controls how fact tables are joined, for a report containing metrics from different fact tables, or for a compound metric which has the base metrics coming from different fact tables. Controls whether temporary tables will be created for each metric or if fact tables will be directly joined.
Downward Outer Join Option: Controls how joins are performed when joining metrics which are calculated at different levels.
Full Outer Join Support: Controls the property which informs the Engine if a full outer join is supported by the Target database.
Preserve All Final Pass Result Elements: Controls how final pass result elements are joined to Lookup/Relationship tables
Preserve all Lookup Table Elements: Provides users the option to control if all lookup table elements should be included in the final results.
Metrics
Metric Join Type: Controls the type of join that is used to join a metric's data with other metric data on a report.
Default to Metric Name
Null Check
Query Optimizations
SQL Global Optimization: Determines if SQL should be optimized by combining multiple passes, and it should be optimized, controls the level of optimization
- Level 0: No optimization.
- Level 1: Remove Unused and Duplicate Passes.
- Level 2: Level 1 + Merge Passes with Different SELECT
WHERE clause driving table: Controls which table the Engine should use to apply the filter (WHERE clause). By default Fact Table is used
Intelligent Cubes
What is an Intelligent Cube?
In-memory version of report data that can be manipulated by the MicroStrategy Analytical Engine?
Types of Cubes Two unique methods to implement Intelligent Cube Technology:
•Personal Intelligent Cubes: You can begin by creating reports in MicroStrategy as usual, and then analyze your reports with OLAP Services features such as view filters, derived metrics, and derived elements. These features are processed on the in-memory copy of data known as a personal Intelligent Cube, rather than processed on the data warehouse.
•Intelligent Cubes: Rather than returning data from the data warehouse for a single report, you can return sets of data from your data warehouse and save them directly to Intelligence Server memory. These sets of data can be shared as a single in-memory copy, to be used by many different reports created by multiple users.
Activities on Cubes1. Dynamic Aggregation
2. Derived Metrics
3. Derived Elements
4. Metric Filters and View Filters
Advantages of Cubes
1. Fast Performance
2. Scheduling the Cube
3. Drilling
4. Data Sharing
Difference between Standard and OLAP reportsIf none of the OLAP features are used then it is a standard report, once any feature is added like view filter, derived metrics then its converted to a OLAP report. A Standard report can be converted to OLAP but not vice versa.
Difference between Personal Intelligent Cube and Intelligent Cube1. In PIC, Full access to re execute data against the warehouse but in IC , in order to re-execute against the warehouse , we have to drill on the data.
2. PIC is linked to a single report whereas multiple reports can access a IC.
3. Both view and report filters can be used in PIC but only view filters can be used in IC
4. In IC, prompts can be used only on objects included in IC but in PIC it can be applied even on objects not in the prompt.
5. Security Filters can be applied on both IC and PIC.
6. Consolidations and Custom Groups cannot be used in reports using IC but this can be achieved by using derived elements
7. Derived elements can be used only on IC not on PIC.
Features not supported in Intelligent Cubes
1. Consolidation and Custom Group
2. OLAP Service Features: View Filters and Derived metrics cannot be used
3. Prompts cannot be used
What is dynamic sourcing?Dynamic sourcing extends the accessibility of Intelligent Cubes by allowing standard reports to access any published Intelligent Cubes that can satisfy the data requirements of the report
How to Unpublish and Intelligent Cube?From the Folder List, expand Administration, then expand System Monitors, then expand Caches, and select Intelligent Cubes. The Intelligent Cube Monitor is displayed. Right-click an Intelligent Cube and select Delete. Unpublish only deletes data in the cube but not the cube itself.
When does the report fails due to the unavailability of Intelligent Cubes?
1. When I Cube is not published
2. When enough space is not there for publishing
3. Cube is in the process of publishing
4. Cube is offline
What are the different types of derived elements?
1. Group Derived: A Group derived element is a combination of attribute elements into a single derived element.
Eg: East Coast: Groups the Mid-Atlantic, Northeast, and Southeast attribute elements.West Coast: Groups the Northwest and Southwest attribute elements
2. Filter Derived: A Filter derived element uses a filter qualification to determine the combination of attribute elements for a derived element.
For Eg: Southern Regions: Returns attribute elements whose name begins with South.
•Northern Regions: Returns attribute elements whose name begins with North.
3. Calculation Derived: A Calculation derived element uses operators and functions to combine attribute elements and derived elements into calculations that define a single derived element
All other derived elements: Collects all attribute elements that are not inclulded in derived elements and includes them as individual attribute elements by default
Report Service Document
What is a document?A document displays your organization’s data in a format that is similar to a PowerPoint presentation, where several grid and graph reports can be viewed at the same time, along with images and text. High-quality, Pixel Perfect™ documents allow you to display your business data in a user-friendly way that is suitable for presentation to management for boardroom-quality material. Examples of documents include scorecards and dashboards, managed metrics documents, production and operational documents, and more
What are the different export formats?Excel, PDF, Flash, HTML
What are the different view modes?Flash View
HTML View
Design View
What is a dataset?
A dataset is a MicroStrategy report that defines the data that the Intelligence Server should retrieve from your data warehouse or from a cache that is available to the document.
What is a hyperlink?
A hyperlink connects text or an image in a document to a web page (the target of the hyperlink). When the document is open in PDF View, as shown below, the cursor changes to a hand when you hover the cursor over text that contains a hyperlink.
What is grouping of data in document?
If the data is grouped by page, drop-down lists are displayed at the top of the screen,
What is a layout?
A multi-layout document contains multiple documents, each in its own layout, creating a “book” of documents. Each layout functions as a separate document, with its own grouping, page setup, and so on, but the layouts are generated into a single PDF document. If a document contains multiple layouts, tabs are displayed at the top of the screen.
What is a dashboard?
A dashboard is commonly only one page long, is intended to be viewed online, and usually provides interactive features that let analysts change how they view the dashboard’s data. By being only one page long, a dashboard makes it easy to view the whole document at one time and see all the information. A dashboard allows interactivity from users, so each user can change how they see the data, within the limits of what the dashboard allows them. You must view a dashboard in Flash View in MicroStrategy Web to be able to interact with its widgets, selectors, and panel stacks
Difference between Dashboard and Scorecard - Click Here
What are the different display modes?
View Mode: view the results
Cannot create a new document.
•Cannot edit an existing document.
•Cannot manipulate any objects on the document, as you can in any of the other display modes.
Interactive Mode
Edit an existing document.
•View the results of the document.
•Use selectors to flip through the panels in a panel stack or display different attribute elements or metrics in a grid or graph report displayed on the document.
•Format grid and graph reports.
•Sort grid reports and pivot report objects on them.
•Add totals.
•Resize rows and columns.
•Create metrics based on report objects already on the grid report.
•Optimized for dashboard viewing.
Cannot create a new document.
•Cannot format the layout and positioning of objects or the entire document.
•Cannot format the Flash properties of widgets.
Flash Mode
Access and interact with features provided by Flash, such as widgets. Widgets are interactive Flash-only graphs, such as gauges and time series slides, that dynamically update when you select a new set of data.
•Format widget Flash properties.
•Edit an existing document.
•View the results of the document.
•Use selectors to flip through the panels in a panel stack or display different attribute elements or metrics in a grid or graph report displayed on the report.
•Sort grid reports and pivot report objects on them
Cannot create a new document.
•Cannot manipulate or format grid or graph reports, except to sort and pivot objects on them.
•If a graph report uses a graph type that is not supported in Flash, the graph is not displayed
What are controls?
Controls are the objects that display the data, images, and shapes in a document; they are the objects shown in the document’s Layout area as you design the document.
1. Text Fields
2. HTML container
3. Line or Rectangle
4. Image
5. Grid/Graph
6. Panel stack, which is a holder for a collection of panels, or layers of data, in a document. A user can navigate or flip through the panels in a panel stack; only one panel is displayed at a time.
7. Selector, which allows users to interact with the document, by flipping through the panels in a panel stack or by displaying different attributes or metrics in a Grid/Graph
8. Widget, which displays the results of a dataset report in Flash in MicroStrategy Web, allowing users to visualize data in different ways than traditional reports displayed as Grid/Graphs do
In documents, Information about the document (such as page numbers) and the dataset reports (such as report names and filter information), what is it called? –
Auto Text Codes
What are the different sections in a document?
1. Page Header and Footer
2. Document Header and Footer
3. Detail Header and Footer
What are the different types of text field in a document?
Static text: This text does not change and is commonly used for labels or descriptions. Examples in the sample document are the words “Employee” and “Revenue”. For directions to add static text, see Adding static text to a document
•Dynamic text: This text is automatically populated by the document or dataset. Dynamic text is always included within braces { }.
There are two types of dynamic text:
Data field
Auto text code
Eg: Date/time: {&DATETIME}
What are the different types of metrics that can be created within a document?Calculated expressions: A calculated expression is a metric that is calculated dynamically, when the document is executed, directly from metrics on a document dataset Eg: Revenue - Cost
Derived metrics: A derived metric is a metric that is obtained dynamically, when a document is executed, directly from metrics on a document dataset. A derived metric is created using at least one of the metrics in the document
Summary metrics.
What are the issues faced when exporting reports/documents to excel?
Choose Excel-compatible colors for objects as well as grid and graph formatting. MicroStrategy Desktop's basic set of 40 colors matches the Excel colors. Color from the Advanced Color Picker will be matched by Microsoft Excel with more or less accuracy.
Use graphs that are supported by Microsoft Excel. Examples of non-supported graphs are gauge graphs and combination graphs.
Lines and rectangles are not supported when exporting to Microsoft Excel. As a workaround, use a text field border to create a line or a rectangle.
A line graph within a Report Services document changes color when exported to excel. [Fixed in 9.3]
When exporting Report Services documents with objects that are overlapped, unexpected behavior is seen as Microsoft Excel does not support this feature and priority might be given to one of the overlapped objects.
Word-wrapping specified for multi-word object names does not take effect in PDF or Excel.
Custom line in a graph report is not carried over when exporting to Microsoft Excel as a Live Chart [Not sure if its fixed now]
The secondary axis on a dual axis graph report is not displayed when exporting the report to Excel format with the option "Export graphs as live Excel charts".Hence don't export a graph report as live chart in Excel format when exporting it.
Banding does not display correctly when exporting reports to Excel 2007 and above from MicroStrategy 9.2.1 Desktop and Web. Change the excel options such that you can export to older versions.
Prompt details are not fully displayed when exporting to Microsoft Excel 2007 from MicroStrategy Web 9.2.1.Change the excel options such that you can export to older versions.
Metric values will still appear despite column width set to 0 when exported to Excel 2007 in MicroStrategy version 9.2.1. Use excel 2003 to workaround.
Stacked Bar graph becomes Clustered Bar graph when exporting a Report Services document to Excel in MicroStrategy Web 9.2.1.
A Report Services document that includes attributes which use the date or datetime datatype. When the document is exported to Microsoft Excel, the attributes are recognized as plain text instead of dates, thereby inhibiting the use of certain pivot functions in Microsoft Excel native to the date format.