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

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

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

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

mstrio – Python and R wrappers for the MicroStrategy

mstrio – Python and R wrappers for the MicroStrategy REST APIs Connecting to MicroStrategy Create a connection to the Intelligence Server using Connection() and  connect() in Python and R, respectively. Required arguments for the   Connection()  function are the URL for the MicroStrategy REST API server, MicroStrategy Intelligence Server username and password, as well as the MicroStrategy project name. By default, the  connect()  function anticipates your MicroStrategy Intelligence Server username and password. LDAP authentication is also supported. Use the optional argument  login_mode=16  in the  connect()  function for LDAP authentication. Extract data from cubes and reports To extract data from MicroStrategy cubes and reports, use the get_cube()  and  get_report() functions. Use...

The logical table size calculation in Microstrategy

The logical table size calculation in Microstrategy The logical table size is an integer number that represents the granularity or level of aggregation of a particular table. It is called 'logical' because it is not related to the physical size of the tables (number of rows). It is calculated according to the attribute IDs that are present in the table and their level in the system hierarchy. Even though, the number does not reveal the actual number of rows in the table, it is an accurate way of measuring a table size without having to access its contents. MicroStrategy Engine utilizes an algorithm based on attribute keys to calculate the Logical Table Size (LTS): Given the following tables: The algorithm that calculates the table sizes performs the following steps: Calculate the number of levels per hierarchy: Hierarchy 1: 3 Hierarchy 2: 4 Calculate each attribute individual weight according to the level in the hierarchy (level in hierarchy/number of ...

Update the data on an Intelligent Cube without having to republish the entire cube in MicroStrategy

Update the data on an Intelligent Cube without having to republish the entire cube in MicroStrategy MicroStrategy has introduced a feature known as, Incremental Refresh Options, which allow Intelligent Cubes to be updated based on one or more attributes, by setting up incremental refresh settings to update the Intelligent Cube with only new data. This can reduce the time and system resources necessary to update the Intelligent Cube periodically versus a full republish. For example, if a user has an Intelligent Cube that contains weekly sales data, the user may want this Intelligent Cube to be updated at the end of every week with the sales data for that week. By setting up incremental refresh settings, he can make it so that only data for one week is added to the Intelligent Cube, without affecting the existing data and without having to reload all existing data. Users can select t...

Conversion failed when converting the varchar value 'xxxx' Microstartegy

Error "Conversion failed Error "Conversion failed when converting the varchar value 'xxxx' to data type int" happens when displaying Picture type attribute form using ApplySimple in expression against SQL Server 2012 in MicroStrategy The attribute form is in Picture type and defined with the following ApplySimple function with Int type column [ID_BARANG] as the input parameter against SQL Server 2012. Solutions is to use Concat("Images/demo/s", [BARANG_ID_INT], ".png") ApplySimple("'images/demo/'&#0&'.png'", [ID_BARANG]) However, when running reports with attribute to show the picture form in Web, error message happens in both Web and Developer. Conversion failed when converting the varchar value 'images/demo/s' to data type int. STEPS TO REPRODUCE: SQL Server 2012 database should be used as the warehouse. Create an attribute form as type Picture and us custom expressi...

MicroStrategy Connection File

Creating a Connection File in Microstrategy Workstation A MicroStrategy connection file (also know as the .mstrc) is a text file that contains all of the information needed to connect Workstation to a MicroStrategy environment. With a .mstrc file, users can simply import the file and enter their credentials, and then they will be connected to a MicroStrategy On-Premises Environment. Requisites MicroStrategy Workstation URL for your Environment MicroStrategy Login credentials How to create the connection file With the integrated option Connect to New Environment: Open Workstation window. Go to Environments located under Manage on the left side. Click on the plus symbol with the label of Connect to a New Environment . Assign a friendly environment name. Provide the Environment URL and click Continue . Note: If it is a default MicroStrategy installation of MicroStrategy Library, the Environment U...

Microstrategy Analytics

Search This Blog