MicroStrategy ONE
About Defining Column Data Types for MDX Cube Data
You can define the column data type that is applied to a column of MDX cube data mapped to an attribute. This allows the MDX cube data to be correctly represented in MicroStrategy and facilitates the following:
- Group related attributes, from MDX cubes as well the data warehouse, as the same attribute in a Report Services document. This is discussed in more detail in this section.
- Use value prompts to qualify on your MDX cube data. If you plan to use a value prompt (date, numeric, text, or big decimal) to qualify on an attribute form imported from an MDX cube source, you must define the attribute form with a data type that matches the value prompt. For example, an Integer column data type can be qualified on using a numeric value prompt. See Define Column Data Types for MDX Cube Data for details.
- Support date data from your MDX cube source in MicroStrategy. This enables you to filter and qualify on your MDX cube source date data using static and dynamic date qualifications. See Supporting MDX Cube Source Date Data in MicroStrategy for details.
When MDX cube data is mapped to MicroStrategy objects, MicroStrategy retrieves the column data type through MDX. In the case of MDX cube data that is mapped to attributes, the columns are often returned as a string of characters. This can be the case even with ID columns of data that are commonly of a numeric type such as integer.
The column of data that is automatically mapped to attribute ID columns in MDX cubes is returned as a character string. The data type of this column cannot be defined to anything other than the default data type because it is not well suited for other data types. If you want to map a column with a numeric or other data type to the ID form of an attribute, you should use a different form such as the key form.
MDX cube data that is mapped to MicroStrategy metrics is automatically converted to a numeric data type and thus does not need its column data type to be manually set.
For example, you have two MDX cubes that map data to a Category attribute in MicroStrategy. The ID attribute form for Category is returned as a string by default. However, you know that its associated MDX cube column is of type integer and set the data type accordingly in each MDX cube. You can then create MDX cube reports for these MDX cubes. By setting the Category ID attribute form to read the MDX cube data as an integer, you can then include the two MDX cube reports as datasets of a Report Services document and group the Category attribute data. You can also add a standard report, drawing data from a data warehouse, as a dataset of the document to combine its data on the same display.
Notice that the Category ID form is defined as the same data type (Integer) in each data source. Without this setup, the Category data from each data source cannot be displayed on a single document.
In addition to displaying data from different data sources on the same document, defining data types lets you perform calculations on metrics from different data sources. In the document shown above, the Profit metric is calculated by creating a calculated expression in the document that subtracts the Cost metric of MDX Cube 1 from the Revenue metric of MDX Cube 2.
The image shows a scenario of displaying both MDX cube data and data from a data warehouse on one document. If you only include MDX cube data on a document, you can use the default attributes created by MicroStrategy when importing your MDX cubes. However, to include MDX cube data and data from the data warehouse together on a document, you must map your MDX cube data to attributes that are part of the project's relational schema. See Mapping MDX Cube Data to Project Attributes for details.
For more information about defining and modifying the column types, see:
After defining the column data types, you can create MDX cube reports that can be displayed on a document with other MDX cube reports and standard reports. For information on how to create a document that displays data from multiple data sources, and information on creating calculated expressions see the Document Creation Help.