Skip to main content

MSTR The key specified is the true key for the warehouse table

The key specified is the true key for the warehouse table

 Edit 0 1…
The screen shot below illustrates the setting "The key specified is the true key for the warehouse table" in the logical view of a warehouse table:
external image rtaImage?eid=ka1440000009HBu&feoid=00N44000006TPNo&refid=0EM440000002FzU
This setting is only applicable when the attributes on the template are comprised of all the attributes listed as table keys in the logical view of the fact table. Therefore, when checked, this setting forces the Structured Query Language (SQL) to not aggregate the data and to pull only from the fact tables directly.The following SQL demonstrates the difference between two identical reports when this setting is checked and when it is not. The report SQL is provided below the grid view.The first report was run against the fact table City_Subcateg_Sls without the "The key specified is the true key for the warehouse table" checked. It demonstrates aggregation by the metric:
select a11.CUST_CITY_ID CUST_CITY_ID,

max(a12.CUST_CITY_NAME) CUST_CITY_NAME,

a11.SUBCAT_ID SUBCAT_ID,

max(a13.SUBCAT_DESC) SUBCAT_DESC,

sum(a11.TOT_COST) COST

from CITY_SUBCATEG_SLS a11

join LU_CUST_CITY a12

on (a11.CUST_CITY_ID = a12.CUST_CITY_ID)

join LU_SUBCATEG a13

on (a11.SUBCAT_ID = a13.SUBCAT_ID)

group by a11.CUST_CITY_ID,

a11.SUBCAT_ID

The following SQL demonstrates that by having all the keys from the fact table on the report template, the setting "The key specified is the true key for the warehouse table" allows the metric to return the values straight from the fact table without aggregation. This optimizes the SQL generated considerably:
select a11.CUST_CITY_ID CUST_CITY_ID,

a12.CUST_CITY_NAME CUST_CITY_NAME,

a11.SUBCAT_ID SUBCAT_ID,

a13.SUBCAT_DESC SUBCAT_DESC,

a11.TOT_COST COST

from CITY_SUBCATEG_SLS a11

join LU_CUST_CITY a12

on (a11.CUST_CITY_ID = a12.CUST_CITY_ID)

join LU_SUBCATEG a13

on (a11.SUBCAT_ID = a13.SUBCAT_ID)

Comments

  1. AnonymousApril 17, 2022 at 9:26 AM

    Mstr The Key Specified Is The True Key For The Warehouse Table >>>>> Download Now

    >>>>> Download Full

    Mstr The Key Specified Is The True Key For The Warehouse Table >>>>> Download LINK

    >>>>> Download Now

    Mstr The Key Specified Is The True Key For The Warehouse Table >>>>> Download Full

    >>>>> Download LINK 0B

    ReplyDelete

Post a Comment

Popular posts from this blog

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

Sort by metric subtotals and attribute elements together in Microstrategy

Sort by metric subtotals and attribute elements together in Microstrategy Users may observer that when creating a report that contains advance sorting with a metric that contains subtotals the report results appear to be only sorted by the metric values specified. Even if a sort is specified for the attribute elements on the report, the results in the report appear as if the attribute sort was not defined. In the screenshot below, the results for a report are shown where the Advance Sorting option 'Sort metrics hierarchically using: Total' is selected. For this report, a second sort is defined on the Customer Gender - 'DESC' form, users would notice that the ordering of the this attribute is not consistent: The sort definition for the report is shown below: CAUSE: When the option to 'Sort metrics hierarchically using: Total' option is selected, the MicroStrategy Engine first sorts the results based on the Total values, and then sorts th
1 comment
Read more

Sending an email in MSTR where the results of a report are in the email body as HTML content and a different report/document is an attachment to the same email in MicroStrategy

Is it possible to send an email using Distribution Services where the results of a report are in the email body as HTML content and a different report/document in MSTR? ANSWER: It is currently not possible to send an email using Distribution Services where the results of a report are in the email body as HTML content and a different report/document is an attachment to the same email in MicroStrategy 9.x. An enhancement request has been logged for this feature. ACTION: Contact Microstrategy Technical Support for an update on the enhancement, I have contacted but nobody knows where the request is  
2 comments
Read more

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

Apply or Pass-through functions in Microstrategy

Ap ply (Pass-Through) functions MSTR Apply functions provide access to functions or syntactic constructs that are not standard in MicroStrategy but are provided by various RDBMS systems.. Syntax common to Apply functions Apply Function Name   ("expression with placeholders", Arg1, Arg2, Arg3, …ArgN) where: Apply Function Name  – is a generic name used for the predefined pass-through functions described above expression with placeholders  – is the string describing the actual expression or syntax that the engine uses while generating the SQL and which is sent to the RDBMS. The placeholders are represented by #0, #1, and so on. "#" is a reserved character for MicroStrategy. Arg  – is an argument that replaces the parameter markers in the pattern. Arg1 replaces #0, Arg2 replaces #1, and so on. There are   five  pre-defined Apply functions to replace regular, predefined functions of the same type. For more details, click the link co
6 comments
Read more

Control the display of null and zero metric values

Show   Control the display of null and zero metric values in a grid report You can determine how to display or hide rows and columns in a grid report that consist only of null or zero metric values. You can have MicroStrategy hide the rows and columns in the following ways: Hide rows and columns that consist only of null metric values Hide rows and columns that consist only of zero metric values Hide rows and columns that consist only of null or zero metric values (default) Once you have defined how MicroStrategy hides null and zero metric values in the grid, you can quickly show or hide the grid using the Hide Nulls/Zeros option in the Data menu, as described below, or by clicking the  Hide Nulls/Zeros  icon  in the Data toolbar. To determine how null and zero metric values are displayed or hidden in a grid report Open the report in Edit mode. From the  Tools  menu, select  Report Options . The Report Options dialog box opens. To determine how
Post a Comment
Read more

Components of the MicroStrategy Engine

Components of the MicroStrategy Engine The MicroStrategy Engine consists of three engines:  • SQL Engine  • Query Engine  • Analytical Engine  These individual engines work together to fulfill report requests submitted by MicroStrategy that can be resolved by pure SQL alone.  The SQL Engine is responsible for generating optimized SQL and producing result sets that can be resolved by pure SQL alone. The Query Engine is responsible for executing the SQL generated by the SQL Engine.  The Analytical Engine is responsible for performing any calculation that cannot be resolved with SQL alone.
1 comment
Read more

Microstrategy Document Autotext macros:

Autotext  code/macros in  Microstrategy Document/dashboard This is a list of the available auto text macros that the Report Services Document engine recognizes. The following auto text codes allow you to add  document variable information to your document. These auto text codes are automatically replaced by information about the document. Auto text codes for MSTR document/dashboard:  AUTOTEXT DESCRIPTION   {&PAGE}  Display the current page.  {&NPAGES}  Display the total number of pages.  {&DATETIME}  Display the current date and time.  {&USER}  Display the user name that is executing the Report Services Document.  {&DOCUMENT}  Display the document name.  {&DOCUMENTID}  Display the document ID.  {&DESCRIPTION}  Display the document description.  {&PROJECT}  Display the project name.  {&EXECUTIONTIME}  Display the execution time.  {&SERVERNAME}  Display the Intelligence Server name  {& REPORT :FILTERDETAILS}  Dis
2 comments
Read more

Create a Visualization Filter in a Dossier

Create a Visualization Filter You can select attribute values to filter data that appears in a visualization by creating an attribute filter. Create a visualization filter by dragging attributes and metrics from the Datasets panel. To create an attribute filter Open the dossier  you want to modify. Select the chapter that contains the visualization to filter. Click  Filter  . An empty filter appears in the dossier. Drag the attributes and metrics for filtering from the Datasets panel to the empty filter. Use the Ctrl key to select multiple attributes and metrics. In the filter, click  Select Target . Click on target visualizations or visualization filters to select them. You can select targets across chapters and pages, as long as they are all on the same page. Click  Apply . Click  More   in the filter and select options for modifying the filter. The available options are based on the objects you select.
Post a Comment
Read more

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://webserver/Mi
2 comments
Read more