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 ragged/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 for the Parent01 element p4 (ID=4) in the Lookup table for attribute Child01. Placing both attributes on a report will result in the following SQL and data returned. Parent01's element p4 will not be returned. This is known as a ragged hierarchy and is not supported in MicroStrategy.

SQL	Data Returned
select a12.parent01_ID parent01_ID, a13.parent01_DESC parent01_DESC, a12.child01_ID child01_ID, a12.child01_ID child01_ID0 from child01 a12 join parent01 a13 on (a12.parent01_ID = a13.parent01_ID)

To overcome this issue, create a Logical View with the following SQL. Note the Left Outer Join.

SQL	Logical View Definition
select a12.parent01_ID parent01_ID, a12.parent01_DESC parent01_DESC, a11.child01_ID child01_ID, a11.child01_DESC child01_DESC from parent01 a12 left outer join child01 a11 on (a11.parent01_ID = a12.parent01_ID)

Modify the attribute Child01 to include the new Logical View as a source table and redefine a relationship to attribute Parent01 using the new Logical View.

Add Source Table	Add Relationship

Executing the same report will now return all elements for Attribute Parent01.

SQL	Data Returned
select /* RRR01 / a12.parent01_ID parent01_ID, a12.child01_ID child01_ID from (select a12.parent01_ID parent01_ID, a12.parent01_DESC parent01_DESC, a11.child01_ID child01_ID, a11.child01_DESC child01_DESC from parent01 a12 left outer join* child01 a11 on (a11.parent01_ID = a12.parent01_ID)) a12

Comments

Allow a Visualization to Update the Data in Another Visualization in Dossier

Allow a Visualization to Update the Data in Another Visualization After adding multiple visualizations to a dossier, you can select values in one visualization (that is, the source) to automatically update data in another visualization (that is, the target). This is done by creating a filter on a visualization that targets other visualizations. To Add a Target Visualization to Your Dossier: Open the dossier with the visualization. Click Insert Visualization . A blank visualization appears in the dossier. From the Visualizations panel, select Grid . Drag an attribute from the Datasets panel to the Rows area of the Editor panel to add attributes to the rows. Drag an attribute from the Datasets panel to the Columns area of the Editor panel to add attributes to the columns. Drag a metric from the Datasets panel to the Metrics area of the Editor panel, to add a metric to the grid. The Metric Names attribute automatically appears i...

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

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

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, cli...

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

"Table structure cannot read or update" in the warehouse catalog error while updating a table structure in Microstrategy

"Table structure cannot read or update" in the warehouse catalog error while updating a table structure while using Warehouse Catalog in MicroStrategy Developer This issue could be due to the incorrect prefix. To fix the issue. 1. Open the Warehouse Catalog. 2. Select the table and assign the correct Prefix update the structure. 3. Save and close the warehouse catalog.

User request is completed. (Ran out of memory)

Unable to Run/Edit particular MicroStrategy reports ue to the following error: User request is completed. (Ran out of memory) User request is completed. (Ran out of memory) The above issue appeared in MSTR Web Universal version 10.5 We tried the below options without any luck: 1. i-server restart 2. Web server restart 3. clear document cache/dataset cache 4. Web server cache clear as below: The correct option is to increase the contract memory settings: Using the Memory Contract Manager The MCM settings are in the Intelligence Server Configuration Editor, in the Governing Rules: Default: Memory Settings category. The Enable single memory allocation governing option lets you specify how much memory can be reserved for a single Intelligence Server operation at a time. When this option is enabled, each memory request is compared to the Maximum single allocation size (MBytes) setting. If the request ...

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

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

No 'Alert' option appear when trying to create an alert-based subscription in MicroStrategy Distribution Services

The 'Alert' option does not appear when attempting to create an alert-based subscription in MicroStrategy Distribution Services In MicroStrategy Distribution Service 9.x and 10.x, and 11.x versions it is possible to create an alert-based subscription. When right-clicking the metric header of a report in MicroStrategy Web 9.0.x, the 'Alerts' option does not appear: Cause : This issue occurs because the user attempting to create the alert does not have all of the necessary privileges on alerts. Fix : In order to create an alert-based subscription, the following privileges are required: In order ti get permissions to create alerts the user should be given the following privileges by the admin: New Version of Microstrategy 11.x: Server- Distribution: Older Versions of Microstrategy 9.x, 10.x etc..: Web Reporter > Web user Web Analyst > Web create alert ...

Microstrategy Analytics

Search This Blog