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

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

Microstrategy document/dashboard applying selections as filters or slices

Applying selections as filters or slices In a Microstrategy Document the selections a user makes in a selector can either filter or slice the data in the target: Filtering means that the data for the current selection is calculated only when it is requested by the user. The selections are used to filter the underlying dataset before the metric values are aggregated at the level of the Grid/Graph that is displayed in the document. If the source attribute is not included in the Grid/Graph, the metric values from all the selected elements are aggregated and shown at the level specified in the Grid/Graph. All metric condition selectors (which filter metric values or ranks) and selectors that target other selectors filter data by default. You cannot change them to slicing selectors. Slicing means that the data for each available item in the selector is calculated in advance when the document is first displayed. Selections made while ...
2 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

Like / Contains operator in Tableau

Contains (Like) operator in Tableau SInce there is no like operator in Tableau it provides CONTAINS operator.  Little different syntax is there but not too complex. Syntax for contains is:  Below is the example for using CONTAINS operator in a calculated field in Tableau. IF (CONTAINS([campaign_name],"Intent")) OR (CONTAINS([campaign_name_derived],"Intent")) THEN "Yes"   ELSE "No" END  
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

Prompt-in-prompt(Nested Prompts) in Microstrategy

Prompt-in-prompt(Nested Prompts) in  Microstrategy Nested prompts allows you to create one prompt based on the other and other bases on another, nested prompts allows us to prompt the highest level(Like year) to middle level(like Quarter, then to the low level(like Month). Here you can see how to  create a 3-level deep nested prompt that will prompt the user to select a year, then a quarter within that year, then a month within that quarter. Prompt-in-prompt is a feature in which the answer to one prompt is used to define another prompt. This feature is only implemented for element list prompts . The following procedure describes how to achieve this: Create the highest level filter. This is a filter which contains a prompt on an attribute element list. Create a filter on the attribute "Year." Click "prompt on attribute element list" and click "Next" through the rest of the screens to accept the default values. Do not set any additio...
3 comments
Read more

Prompt-in-prompt (Nested prompt) feature in a FreeForm SQL Report

Prompt-in-prompt (Nested prompt) feature in a FreeForm SQL Report In some business scenarios, it is required to implement prompt-in-prompt (nested prompt) feature to use the answer of one prompt to qualify on the elements of another prompt in Freefrom SQL reports in MicroStrategy Developer 9.4.x-10.x. The following procedure describes how to achieve prompt-in-prompt in a Freeform SQL report in MicroStrategy Tutorial project: Create a new filter, select Add an Attribute qualification and choose the highest level attribute Country. Make sure to Qualify On: Elements. Click Prompt as the image shown below. Accept all the default values without any changes in the popped up window after clicking on "Prompt" as shown below. Save the filter as "Country Filter". Click "Prompt", choose "Use a filter to reduce the number of elements" option and select Country Filter created in last step, as shown below. Save the filter as "Region Filter...
Post a Comment
Read more

HyperIntelligence Training Videos

HyperIntelligence  Training Videos           Design and build hyper cards Optimizing Datasets for HyperIntelligence Using the HyperIntelligence for Office Outlook Add-In Building HyperIntelligence Cards Using HyperIntelligence for Mobile on Android Deploying HyperIntelligence for Outlook Insights On-The-Go: HyperIntelligence for Mobile Building HyperIntelligence Profile Cards Designing Custom HyperIntelligence Cards Using the Calendar with HyperIntelligence for Mobile
4 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 . ...
4 comments
Read more
Star schemas and aggregate (or summary) fact tables Aggregate tables can further improve query performance by reducing the number of rows over which higher-level metrics must be aggregated.  However, the use of aggregate tables with dimension tables is not a valid physical modeling strategy. Whenever aggregation is performed over fact data, it is a general requirement that tables joined to the fact table must be at the same attribute level or at a higher level. If the auxiliary table is at a lower level, fact rows will be replicated prior to aggregation and this will result in inflated metric values (also known as "multiple counting"). With the above Time dimension table, a fact table at the level of Day functions correctly because there is exactly one row in DIM_TIME for each day. To aggregate the facts to the level of Quarter, it is valid to join the fact table to the dimension table and group by the quarter ID from the dimension table. Sql select DT...
1 comment
Read more