MSTR Transformation Metric with no aggreagation

Content
A transformation table is a type of relation table, normally used to create a transformation metric. Refer to the following MicroStrategy Knowledge Base technical note for more information on transformation metrics:

TN 7238 "Creating transformation, rank and percent to total metrics in MicroStrategy Desktop 8.x"

Take the following Month to Day table as an example:

Transformation table 'MTD_DAY':

DAY_DATE	MTD_DAY_DATE
1/1/2006	1/1/2006
1/2/2006	1/1/2006
1/2/2006	1/2/2006
1/3/2006	1/1/2006
1/3/2006	1/2/2006
1/3/2006	1/3/2006
...	...

By default the transformation metric with transformation 'Month to Date' will display like this

Day	MTD_metric
1/1/2006	1/1/2006 metric
1/2/2006	1/1/2006 + 1/2/2006 metric
1/3/2006	1/1/2006 + 1/2/2006 + 1/3/2006 metric

But sometimes, users may want to compare with Day level metric and it's corresponding transformation metric as shown in the following chart:

Day	MTD	Day_metric	MTD_metric
1/1/2006	1/1/2006	1/1/2006 metric	1/1/2006 metric
1/2/2006	1/1/2006	1/2/2006 metric	1/1/2006 metric
	1/2/2006	1/2/2006 metric	1/2/2006 metric
1/3/2006	1/1/2006	1/3/2006 metric	1/1/2006 metric
	1/2/2006	1/3/2006 metric	1/2/2006 metric
	1/3/2006	1/3/2006 metric	1/3/2006 metric

How to display transformation metric without aggregation:

The following steps may be used as a reference. This example uses the MicroStrategy Tutorial project:

external image rtaImage?eid=ka1440000009GYm&feoid=00N44000006TPNo&refid=0EM440000002EFq

external image rtaImage?eid=ka1440000009GYm&feoid=00N44000006TPNo&refid=0EM440000002EFq

In MicroStrategy Developer 9.x, create a new attribute 'MTD'
- Map column 'MTD_DAY_DATE' in the transformation table 'MTD_DAY' and the column 'ORDER_DATE' in the 'ORDER_FACT' and 'ORDER_DETAIL' fact tables
- Add the 'Day' attribute as parent and set the relationship as 'Many to Many'
- Update the schema
Create a metric 'Day Revenue' with the 'Revenue' fact
- Add the 'Day' attribute as level with Filtering and Grouping set to 'Standard' and remove the default 'Report Level' target
Create a metric 'MTD Revenue' with fact 'Revenue'
- Add the 'Month to Date' transformation
- Change the metric join type to 'Outer'
  - Metric Editor -> Tools -> Metric Joint Type...
- Change the formula join type to 'Outer'
  - Metric Editor -> Advanced Settings -> Fomula Joint Type...
Create a filter to select 'Day' element at the runtime
Create a report with attributes 'Day', 'MTD' and metrics 'Day Revenue', 'MTD Revenue', also adding filter into the report
Execute the report with grid view, the report displays like this with the prompt answer '1/5/2006'

external image rtaImage?eid=ka1440000009GYm&feoid=00N44000006TPNo&refid=0EM440000002EFm

https://community.microstrategy.com/s/article/KB31853-How-to-create-a-transformation-metric-with-no

Comments

LavanyaJanuary 13, 2022 at 6:25 PM
An awesome blog for the freshers. Thanks for posting this information.
Microstrategy Online Training Hyderabad
Microstrategy Online Training india

ReplyDelete
Replies

Add comment

Microstrategy Custom number formatting symbols

Custom number formatting symbols If none of the built-in number formats meet your needs, you can create your own custom format in the Number tab of the Format Cells dialog box. Select Custom as the Category and create the format using the number format symbols listed in the table below. Each custom format can have up to four optional sections, one each for: Positive numbers Negative numbers Zeros Text Each section is optional. Separate the sections by semicolons, as shown in the example below: #,###;(#,###);0;"Error: Entry must be numeric" For more examples, see Custom number formatting examples . To jump to a section of the formatting symbol table, click one of the following: Numeric symbols Character/text symbols Date and time symbols Text color symbols Currency symbols Conditional symbols Numeric symbols For details on how numeric symbols apply to the Big Decimal data type, refer to the Project Design Guide . ...

MicroStrategy URL API Parameters

MicroStrategy URL Structure The following table summarizes the root URL structure used for every request to MicroStrategy Web. Environment Main Application URL Administration URL J2EE http://webserver/MicroStrategy/servlet/mstrWeb http://webserver/MicroStrategy/servlet/mstrWebAdmin .NET http://webserver/MicroStrategy/asp/Main.aspx http://webserver/MicroStrategy/asp/Admin.aspx Every request sent to MicroStrategy Web calls a central controller. Parameters are appended to Main.aspx or mstrWeb (in a .NET and J2EE environment, respectively) to indicate to the controller how the request should be internally forwarded and handled. The following examples show a URL for accessing a MicroStrategy folder when the user does not have an existing session. The URL contains not only the parameters needed to connect to MicroStrategy Web, but also the parameters needed to log on and create a session. J2EE environment: <a href="http:...

Case functions Microstrategy

Ca se functions Microstrategy Case functions return specified data in a SQL query based on the evaluation of user-defined conditions. In general, a user specifies a list of conditions and corresponding return values. Case This function evaluates multiple expressions until a condition is determined to be true, then returns a corresponding value. If all conditions are false, a default value is returned. Case can be used for categorizing data based on multiple conditions. This is a single-value function. Syntax Case ( Condition1 , ReturnValue1 , Condition2 , ReturnValue2 ,..., DefaultValue ) Example Case(([Total Revenue] < 300000), 0, ([Total Revenue] < 600000), 1, 2) sum(Case (Day@DESC in (“Sat”,”Sun”), Sales, 0) {~+} Sum(Case(Category@DESC In("Books","Electronics"),Revenue,0)){~+} CaseV (case vector) CaseV evaluates a single metric and returns different values according to the results. It can be used to perfo...

Settings for Outer Join between metrics in MicroStrategy

Settings for Outer Join between metrics in MicroStrategy MicroStrategy adopts multi-pass logic to determine the execution plan for a report. This means that every metric is evaluated in separate SQL passes. Outer Joins come into play when MicroStrategy Engine merges the results from all SQL passes into one report. For a multi-pass report, different Outer Join behaviors can give the user completely different results. In addition, report metrics can be of different types which can, in some cases, influence the result of the outer join. In MicroStrategy, there are two settings that users can access to control Outer Join behavior : Formula Join Type and Metric Join Type . Metric Join Type: VLDB Setting at Database Instance Level Report and Template Levels Report Editor > Data > Report Data Options Metric Level Metric editor > Tools > Metric Join Type Control Join between Metrics Formula Join Type: Only at Compound/Split...

Custom Subtotal Displays in MicroStrategy

Defining custom subtotal displays in MicroStrategy By default, when users apply subtotals in a report, the name of the subtotal is displayed in the subtotal line items that appear in the report. Users can use custom subtotals to give more control over the characteristics of a subtotal. Custom subtotals allow users to define custom subtotal line items that appear on the reports U sers can make the subtotal name dynamic by typing special characters in the subtotal name field as listed in the following table. Character Description #A The name of the attribute under which the subtotal appears. #P The name of the attribute to the left of, or above the attribute under which the subtotal appears. #0 All the forms of the parent element. #1 The first form of the parent element reading from left to right or from top to bottom. #2 The second form of the parent element reading from left to right or from top to bottom. #3 The third form of th...

Derived metric based on attribute values

Derived metric based on attribute values Here is how could create and display data correctly on using below simple steps. Create a report with Category, Subcategory and Revenue. Create New Metric in a report or VI. Case((Category@ID = 1), Revenue, 0) Booksand Name it as Revenue for where 2 is Category ID for "Books" Report will display result as below. Result for new metric is blank. Now to fix this create a new Derived metric on Category attribute first with formula as Max(Category) {~ } and calling Books Now Edit the "Revenue for Books metric and Replace Category@ID with this new Books metric formula would looks like this Case((Books = 1), Revenue, 0). Report result would now display as expected as shown below

Fact tables levels tables in Microstrategy explained

Fact tables levels in Microstrategy: Fact tables are used to store fact data. Fact tables should contain attribute Id's and fact values which are measurable. All the descriptive information about the fact tables should stored in Dimension tables either in Star Schema fashion or Snow Flake Schema fashion which is best suited to your reporting solution. Since attributes provide context for fact values, both fact columns and attribute ID columns are included in fact tables. Facts help to link indirectly related attributes using these attribute ID columns. The attribute ID columns included in a fact table represent the level at which the facts in that table are stored. So the level of a fact table in the Fact_Item_Day_Customer can be the attribute Id's which is at Day, Item & Customer Id level. For example, fact tables containing sales and inventory data look like the tables shown in the following diagram: Base fact columns ver...

Error. Engine Attribute Role Limit Exceeded! To get rid off this error: (1) Turn off Engine Attribute Role setting in VLDB dialog

Error. Engine Attribute Role Limit Exceeded! Some times Microstrategy will give the below error when updating the schema after an attribute is created. MicroStrategy Developer --------------------------- Error(s) occurred while loading schema: [DFCSCHEMA] Population Exception: The object shown in the following hierarchy no longer exists in schema: -Table LKUP_TEST_FEE_SHIPMENTS error. Engine Attribute Role Limit Exceeded! To get rid off this error: (1) Turn off Engine Attribute Role setting in VLDB dialog; OR (2) Use Table Alias featureDSSSQLEngine: Schema loading error: Message from GetErrorInfo : Report cache is not found.. Error(s) occurred while loading schema: 63. CAUSE: This error message means that the table shown in the error text needs to be split into a very large number tables. MicroStrategy has a limitation that only allows one table to be split into no more than 100 tables. The error above is shown when this limitation is surpassed....

Order of tables appear in the SQL generated by the MicroStrategy SQL Generation Engine

Order of tables appear in the SQL generated by the MicroStrategy Most latest generation databases provide the same performance regardless of the order in which the tables are placed in the FROM clause SQL query. Their optimizers take care of choosing the most efficient way of performing the joins. Other databases perform differently producing a different order. This means that a database compiler may choose a different join path that can be more or less efficient. By default, the MicroStrategy SQL Generation Engine orders the tables in the following way: Fact Tables Metric Qualification (MQ) Tables Relationship Tables Lookup Tables But this behavior can be changed by using the 'From Clause Order' VLDB property under the 'Joins' folder. This setting has four values: Below are the values that the setting may have: Normal FROM clause order as generated by the engine This is the default value. It uses the foll...

Microstrategy Dossier training videos

Microstrategy Dossier training videos Adding data to a Dossier: Creating a visualization filter in Dossier: Sending Dossier to User libraries: How to format Dossiers: Adding a designer filter to a Dossier: Use Bookmarks in Dossiers: Dynamic Links in Dossier: Recreating documents from Dossiers: Exploring and searching in Dossiers: Exploring sample dossiers in MSTR library: Using Page to Page Targets in Dossiers:

Microstrategy Analytics

Search This Blog