Advanced Metric Creation Tool

MicroStrategy provided a simple little tool in 9.0.1 to generate these metrics at a time.

Eg : suppose you have a metric "Revenue", obviously we need some more metrics in our real time like last years revenue, last quarters revenue...so to creating all these metrics manually it is tedious process, Advanced metric tool will help us here. 

Steps :

1. Right click on a particular metric (Revenue) & select advanced metric. 
2. select which filters do you want
3. select which transformations do you want
4. Run it.

you will get bulk of metrics with the combinations for the metric "Revenue".  This is some what better rather than creating all these metrics manually. 

Refreshing I-Cubes & Dynamic Sourcing

Refreshing I-Cubes :  

we can refresh the I-cubes in 2 ways.

1.     scheduling I-cube 
2.     Creating incremental refresh report for the particular I-Cube

Scheduling I-Cube :  we can specify when I-cube should refresh based on time based trigger / event based trigger.
Incremental refresh I-cube :  It is available from Mstr version 9.2.x, using this we can specify the when I-cube should be refresh based on the things..

Insert
Delete
Update
Update Only.

we can update it manually also , right click on the I-cube and select run (updating).

Dynamic Sourcing :

Dynamic sourcing is available from 9.2.x, we can enable / disable this feature in project configuration wizard. once we enable the dynamic sourcing the reports might be get the data from I-cubes without we link the report to particular I-Cube.

No need to link the reports, Small example :

I-Cube1 Objects are :  Attributes : A,B,C,J & Metrics : E,F,K
I-Cube2 Objects are :  Attributes : A,B,C,D,Y,Z & Metrics : E,F,G,H,I

suppose we run the report1 having the  attributes  A,B,C,J & Metrics : E,F,K obviously it gets the data from I-Cube1.  (Note : we didn't link this report to I-cube, due to dynamic sourcing feature it is getting the data from I-cube rather than database)

suppose we run the report2 having the attributes A,Y,Z & Metrics : E,G,H obviously it gets the data from I-Cube2.

suppose we run the report3 having the attributes A,Y,Z,L & Metrics : E,G,H,O obviously it gets the data from Database because same attributes / metrics are not published yet in I-Cube. 

Multi Sourcing in Mstr

MultiSource reporting design

MicroStrategy SQL Generation Engine 9.x introduces a MultiSource reporting option, enabling a single report to access information from multiple databases.

SQL generation in MicroStrategy 8.x and 9.x is focused on metric data. Unless the report contains no metrics at all, the first step in generating SQL is to choose the fact table(s) required to calculate a given metric, taking into account attributes in the template, the metric's dimensionality, and report or metric filters. Once the fact table is known, lookup tables are chosen to support required attribute forms not present in the fact table.

Therefore, the primary purpose of the MultiSource option is to allow metrics in the same report to be calculated against different databases. Integration of lookup table data from multiple sources takes place as needed to support metric calculation.

Implications for attribute data modeling

1. Attribute ID forms must be mapped into all databases where the attribute is relevant. Users should not create, for instance, one Region attribute for one database and a second Region attribute for another. The MicroStrategy SQL Generation Engine will treat such attributes as separate entities and will not join their data.
   
   
   It is not necessary to have a lookup table copy in every database to which the attribute is mapped. At minimum, the attribute's ID form should be mapped into relevant fact tables. Replicating lookup tables in secondary databases is a performance optimization to avoid transferring lookup data between databases, but it is not a requirement for multisource reporting.
   
   
   Users might wish to have a set of reports that run against the primary warehouse, and a another set that uses only a secondary warehouse. In that case, each warehouse could have its own set of schema objects with no cross-mapping.
   
   
2. Attribute element IDs must be consistent across databases. It is not possible to join tables, even within the same database, without common key data between them. Therefore, for example, it is not supported to have one set of Region IDs in one database and a different set of IDs for the same attribute in another database.
   
   
3. Lookup tables in different databases for the same attribute should have the same set of attribute elements. Every attribute in MicroStrategy 9.x declares a primary lookup table. The primary lookup table should be complete, containing all of the attribute's elements. Auxiliary lookup tables in other databases should contain the same set of attribute elements with the same ID values. There is no provision in MicroStrategy 9.0 to create an intermediate table containing a union of attribute elements from different sources. Thus, it is not supported to have a partial list of attribute elements in each database.
   
   
   The analogous scenario in MicroStrategy 8.x, with no multisource reporting, would be a partitioned lookup table. MicroStrategy 9.x does not introduce support for partitioned lookup tables for single-warehouse reporting, and without that logic, it cannot be supported in multisource reporting. (Database-side partitioning schemes may be used, as long as they are transparent in SQL.)
   
   
4. If lookup tables share the same name across databases, their table structures should match. The MicroStrategy metadata maintains a single list of available columns per table name. Thus, for a given table name, columns and data types should match across all database instances hosting the table. For the structures to match, the tables must have the same column names and the data types for each column must be "compatible" across databases. The MicroStrategy 9 Project Design Guide (PDF manual) lists compatible data types in Chapter 9, "Optimizing and Maintaining Your Project" > Accessing multiple data sources in a project > Providing attribute information in multiple data sources.
    

   Datatype	Compatible types
   Integer	Decimal or Numeric with scale 0
   Decimal	Numeric
   Double, Float, Real	Double, Float, Real (all are mutually compatible with each other)
   Date	Timestamp
   Time	Timestamp
   Char	VarChar (but not NChar or NVarChar)
   NChar	NVarChar (but not Char or VarChar)

   
   
   If table structures are different between databases, two scenarios are possible:
   
   
   • A table imported from a secondary database is missing columns that exist in other databases. The MicroStrategy SQL Generation Engine could end up writing SQL for columns that are absent in the database executing the pass.
   • A table imported from a secondary database includes columns not present in the primary warehouse. The new columns could not be added to the column list, or incorrect SQL could be generated against the primary database.
   
   
   The column list for a table is set by the first data source from which the table is imported. After that, it would not be valid to import a table with the same name missing some columns; it would be necessary to remove columns from the list, which would break schema objects already mapped to those columns. It is possible to import a table containing extra columns not already imported; however, the extra columns will not be added to the column list because they cannot be supported by other databases.

Note: The MicroStrategy 9 Project Design Guide states that lookup tables from secondary databases can share the same name as tables in the primary warehouse and include additional columns that will be available for use in schema objects. This statement is in error. When attempting to import the table, the following message is shown in the Warehouse Catalog:

Table LU_REGION is already being used in the project associated with a different DB Instance. This table has additional columns that are not present in the existing table. These columns will not be imported into the project.

If the user proceeds with the table import, additional columns are not shown when trying to create a new attribute form based on the extra column (in this case, ALT_DESC would appear at the top of the list, but is absent).

However, a lookup table in a secondary data source may have a different structure from the primary lookup table, provided the table has a different name. Thus, a workaround is to create a database view selecting the lookup table contents under a different name. The view may be imported into the project as an independent table, after which attribute forms may be mapped to it.

Establishing Connection B/W Mstr & DB

1. We have to create DNS. We can create DNS using Mstr connectivity      wizard (or) odbc data sources.
2. We need login for the database , login credentials must have executable permissions on the particular     database.
3. We have to create database instance, we can create this using database instance wizard / right click on the folder    administration -> configuration managers - > database instances folder
4. Once we have completed, then configure a project and select database instances and add the created database instance wizard    then we will get the tables and data from those connected database.
5. Once we created the DB instance, now we have to specify the DB instance to the particular project. Now right click on any    project and select configuration of the project.
6. Select SQL data sources and add the newly created db instance, so now we are able to use the particular db tables and data .

Difference B/W Report filter, View filter & report limit

Report Filter is applied on objects before report execution.It appears in WHERE CLAUSE & it effects the SQL

View Filter is applied on objects after report execution. It retrieves the results subset of original report, it can be viewed & designed in view mode not in design mode of the report.It does not hit the dwh / db. This is a OLAP feature.

Report limit is a pre-fetch technique applied on db level, When we use the Report limit the HAVING CLAUSE will be generated.report limit specifies a set of  criteria used to restrict the data returned in the report data set after the report metrics are caluculated.