Skip to main content

MSTR Transformation Metric with no aggreagation

Transformation Metric with no aggreagation in MSTR

 Edit 0 1…

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_DATEMTD_DAY_DATE
1/1/20061/1/2006
1/2/20061/1/2006
1/2/20061/2/2006
1/3/20061/1/2006
1/3/20061/2/2006
1/3/20061/3/2006
......












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

DayMTD_metric
1/1/20061/1/2006 metric
1/2/20061/1/2006 + 1/2/2006 metric
1/3/20061/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:

DayMTDDay_metricMTD_metric
1/1/20061/1/20061/1/2006 metric1/1/2006 metric
1/2/20061/1/20061/2/2006 metric1/1/2006 metric

1/2/20061/2/2006 metric1/2/2006 metric
1/3/20061/1/20061/3/2006 metric1/1/2006 metric

1/2/20061/3/2006 metric1/2/2006 metric

1/3/20061/3/2006 metric1/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




  1. 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

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

    • Add the 'Day' attribute as parent and set the relationship as 'Many to Many'

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

    • Update the schema
  2. 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
  3. Create a metric 'MTD Revenue' with fact 'Revenue'
    • Add the 'Month to Date' transformation

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

    • 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...
  4. Create a filter to select 'Day' element at the runtime
  5. Create a report with attributes 'Day', 'MTD' and metrics 'Day Revenue', 'MTD Revenue', also adding filter into the report
  6. 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

Post a Comment

Popular posts from this blog

Microstrategy Dossiers explained

Microstrategy  Dossiers With the release of MicroStrategy 10.9, we’ve taken a leap forward in our dashboarding capabilities by simplifying the user experience, adding storytelling, and collaboration.MSTR has  evolved dashboards to the point that they are more than dashboards - they are  interactive, collaborative analytic stories . Ultimately, it was time to go beyond dashboards, both in concept and in name, and so  the've  renamed VI dashboards to  ‘ dossiers ’.  Dossiers can be created by using the new Desktop product or Workstation or simply from the Web interface which replaces Visual Insights. All the existing visual Insights dashboards will be converted to Dossiers   With MicroStrategy 10.9, there was an active focus on making it easier to build dashboards for the widest audience of end users. To achieve this, some key new capabilities were added that make it easier to author, read, interact and collaborate on dashboards ...
7 comments
Read more

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...
3 comments
Read more

Email Subscription in Microstrategy

Create Email Subscription in Microstrategy Creates an email subscription. Do not include any leading or trailing spaces in the ANSWER parameters. This causes a SQL error and prevents the command from executing. Ex: CREATE EMAILSUBSCRIPTION "New Multi Users" FOR OWNER "administrator" SCHEDULE "Books Closed" CONTACTGROUP "TEST"  CONTENT "Electronics Revenue by Region" IN FOLDER "\Public Objects\REPORTS\SUBJECT Areas\Sales and Profitability Analysis" IN PROJECT "MicroStrategy Tutorial" DELIVERYFORMAT HTML  EXPIRATIONDATE NEVER EXPRIED FILENAME "file_name"   SUBJECT  "Test REPORT" MESSAGE "Please Test"; CREATE EMAILSUBSCRIPTION [ subscription_name ] [FOR OWNER login_name ] SCHEDULE schedule_name  (ADDRESS address_name | USER user_name  | CONTACT contact_name [ADDRESS contact_ address_name ] | CONTACTGROUP contact_group_name ) CONTENT ( report_or_document_name IN FOLDER   loc...
1 comment
Read more
Microstrategy Release Types Platform release Interval:  Annually every twelve (12) months in December Who:  Entire customer base What:  Focus on production level security, stability, and performance defect fixes for all customers. Expectation:  Customer has chosen platform path and wants product stability without new enhancements. Support:  Three (3) years, patches for approved P1 defects, and regular hotfix cadence addresses critical defects. Feature Release Interval:  Quarterly every three (3) months Who:  Customers with specific feature requirements. What:  New functionality developed in close collaboration with customers and customer council. Expectation:  Customer has chosen feature path, will consume further feature releases. Support:  Six (6) months patch support for approved P1 defects and (eighteen) 18 months troubleshooting. Customers upgrade to next feature release for roll-up f...
1 comment
Read more

Replace object names in bulk using MicroStrategy Repository Translation Wizard

Replace object names in bulk using MicroStrategy Repository Translation Wizard Users may need to replace  object names  in bulk.  This can be done using MicroStrategy Repository Translation Wizard in MicroStrategy Developer 9.4.x - 10.x.  Follow the steps below for an example of how to do this.  Create an empty MD shell in Microsoft Access.  Run Repository Translation Wizard from the Start Menu -> Programs -> MicroStrategy-> Object Manager In the "Metadata Repository" screen, select the "Project Source Name" and check the "Export Translations" option, as shown in the following screen shot: In the "Languages" screen, select a project, a translation reference language and a language (choose English as default), as shown in the following screen shot: In the "Select objects" screen, if one needs to select a certain type of object, check the option  Use the results of a search object .  Then, click 'New' to...
Post a Comment
Read more

Logical Views to specify an outer join between two attribute lookup tables when only attributes are on a report

Logical Views to specify an outer join between two attribute lookup tables when only attributes are on a report Apart from using the VLDB properties to create the left outer join,  article describes how to use the Logical View to specify an outer join between two attribute lookup tables when only attributes are on a report. This method exists as attribute only outer joins will not be generated on their own by the MicroStrategy SQL engine. This is because they are only necessary with r agged/unbalanced hierarchies which are not supported as null attribute IDs are not supported (parent elements with no child elements or child elements with no parents).  Brief instructions are provided using the example below. Consider, two attributes: Parent01 and Child01 have a parent-child relationship. Their Lookup tables are defined, as follows Parent01 Child01 Note that although there are 4 ID values for the attribute Parent01, there is no defined relationship ...
Post a Comment
Read more

Multi-Select Drop Down Selector with Apply Button

Multi-Select Drop Down Selector with  OK Button  When creating a Report Services Document, you may want to create a drop down selector to save space, but also would like the drop down to be a multi-selector with OK/Apply button. Below are instructions to achieve this in a Report Services Document.  Steps to Create: 1. Right click on your drop down selector and choose Properties and Formatting 2. Navigate to the Layout Tab 3. In the layout tab, click "Allow multiple selections". It will  not  indicate that the check box is enabled, but this step is mandatory.  3. Next, navigate to the Theme tab. Choose the  L ight Theme  from the drop down menu and click the Apply button. 4) Next, change the Theme back to  None  and click Apply. 4. When you return to the Layout tab, you will see the checkbox for  Allow multiple selections  is now checked.  
Post a Comment
Read more

Microstrategy "Error type: Odbc error. Odbc operation attempted

 "Error type: Odbc error. Odbc operation attempted: SQLExecDirect. [HYT00:0: on SQLHANDLE] [MicroStrategy][ODBC Oracle Wire Protocol driver]Timeout expired" is shown when executing reports from Web When users are trying to execute some reports in MicroStrategy web in particular, they may receive the Error “SQL Generation Complete Index out of range” and “Timeout expired” error as shown below: Possible Causes: One possible cause is that the MicroStrategy Intelligence Server using a cached database connection that was already dropped by the RDBMS. To resolve this: Admin should delete the database connection caches and create a new DSNs in case they are sharing DSNs to connect to different databases. In addition, change the settings for the ‘Connection lifetime’ and the ‘Connection idle time out’.  Follow the steps below to perform the mentioned changes and verify the report after each step and some of the settings require i-server r...
4 comments
Read more

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...
1 comment
Read more

Recursive Hierarchies in Microstrategy

Recursive Hierarchies in Microstrategy Recursive Hierarchies: Explanation A recursive hierarchy or recursive dimension, usually consists of elements that point to other elements pertaining to the same attribute with a parent-child relationship. A classic example is an organization chart: This information can be stored in a recursive fashion using only one table with two ID columns: Problem Recursive hierarchies are not supported by MicroStrategy Recommendation Explode the schema from recursive to dimensional Workaround / Solution The recursive hierarchy table has to be split into several tables, one for each level in the hierarchy (turning it into a traditional snowflake schema). A physical snapshot of the solution is: Each attribute has a 1-M relationship with its child.
Post a Comment
Read more