Data Modeling

No PostgreSQL specific customizations were made for this feature.

Data modeling is the structuring of an organization's data assets into an applicable logical model. Modeling is creating information that describes another set of data. In a BI platform the logical data model is designed and built using current user requirements.

There are two approaches to modeling in MicroStrategy:

• Create project models from MicroStrategy Developer 
• Create ad-hoc models from MicroStrategy Web

Workflow

1. In MicroStrategy Developer, open Architect.
2. Connect to a database instance and drags tables into the canvas.
3. Select the attributes and facts from that table.

4. Create the relationships and hierarchies. 

   

5. Click Save and Update Schema.
6. Use the individual editors inside Developer to create and edit various model objects.

   For example, edit the attributes.

   

Use Modeling to Create Ad Hoc Schemas

You can also use modeling to create ad hoc schemas through the multi-table data import interface.

1. Connect to a data source and fetch the requisite tables. For example, PostgreSQL.

   

2. Fetch the requisite data via tables in the query builder.

    

3. Bring in tables from different sources and create the models.

   

4. Change relationships for the tables.
5. Publish the cube.

A detailed configuration is introduced in the following sections: Logical Data Model, Attribute, Attribute Forms, Facts, Filters, and Hierarchies.

Querying XML Data in PostgreSQL

The following data modeling best practice is available when integrating with XML data in PostgreSQL.

PostgreSQL supports storing, managing, and querying XML data.  PostgreSQL has a built-in XML data type so that users can store XML documents in columns in relational tables. PostgreSQL also provides methods that allow users to manipulate and query XML data from within SQL statements.

MicroStrategy can take advantage of these methods to access XML data stored in PostgreSQL.

Example Data

The following examples use XML documents like the one below to represent information about books. Instead of storing title, author, and so on in separate relational columns, the book information is stored in an XML column.

<book pages="110">
       <title>History of Cats</title>
        <author>
 <fn>John</fn>
 <ln>Doe</ln>
        </author>
        <author>
 <fn>Jane</fn>
 <mi>E.</mi>
 <ln>Doe</ln>
        </author>
        <chapter pages="10">Beginning</chapter>
        <chapter pages="80">Middle</chapter>
        <chapter pages="20">End</chapter>
</book>

The following examples are built on a simple MicroStrategy project using the sample tables below.

The following MicroStrategy objects are then defined:

Attributes

• Book: @ID = BOOK_ID, @XML = BOOK_XML
• Year: @ID = YEAR_ID
• Month: @ID = MONTH_ID; Parent = Year
• Region: @ID = REGION_ID, @DESC = REGION_NAME
• Store: @ID = STORE_ID, @DESC = STORE_NAME; Parent = Region

Facts

• Dollars = DOLLARS
• Units = UNITS

Metrics

• SumDollars=Sum(Dollars)
• SumUnits=Sum(Units)

The Book attribute does not have a DESC form, but rather has a form called XML that maps to the XML column in the LU_BOOK table. The XML form should not be used as a default display form. In fact, the XML form should not appear in the grid at all.

When importing XML columns via the Warehouse Catalog Editor, the Table Structure dialog displays the XML data type as Unknown.  This is not an issue, but there are a few implications:

• XML data is likely to be unreadable if an XML column is displayed in a grid.  Attribute forms or facts mapped to XML columns should be wrapped in appropriate functions (for example, CAST) that return strings or other basic data types.
• The XML data type is not created by engine-generated SQL when creating a data mart table or temp table that requires the XML column.  However, if an XML column is not used as the ID form of an attribute or directly as a metric column, there should be no need to create a temp table using the XML data type.

Filtering on XML data using EXIST

MicroStrategy SQL can generate the EXIST syntax via a filter using ApplyComparison.

For example:

ApplyComparison("#0.exist('/book/author[ln=""Doe""]') = 1", Book@XML)

Sample SQL

select a11.BOOK_ID  BOOK_ID,
       sum(a11.DOLLARS)  WJXBFS1
from BOOK_SALES    a11
       join   LU_BOOK       a12
        on (a11.BOOK_ID = a12.BOOK_ID)
where a12.BOOK_XML.exist('/book/author[ln="Doe"]') = 1
group by a11.BOOK_ID

Displaying elements from XML data using QUERY and VALUE

The QUERY method can be called in attribute forms or metric expressions using ApplySimple.

• Users may need to introduce CAST into the expression to avoid a string data, right truncation error message during report execution.

  • Attribute Form example:

    ApplySimple("cast(#0.query('/book/title') as nvarchar(250))", Book@XML)

  • Metric expression example:

    Max(ApplySimple("cast(#0.query('/book/title') as nvarchar(250))", Book@XML)) {~}

• QUERY returns XML, while VALUE returns a specified SQL data type.

The following example illustrates how to call QUERY in an attribute form using ApplySimple.

Sample attribute from using Apply Simple and QUERY

Sample SQL

select a11.BOOK_ID  BOOK_ID,
        cast(a11.BOOK_XML.query('/book/title') as nvarchar(250)) CustCol_1
 from LU_BOOK  a11

Sample result from report using attribute form using QUERY

The VALUE method can also be called in attribute forms or metric expressions using ApplySimple.

• Attribute form example:

  ApplySimple("#0.value('/book[1]/title[1]', 'varchar(100)')", [BOOK_XML])

• Metric expression example:

  Max(ApplySimple("#0.value('/book[1]/title[1]', 'varchar(100)')", Book@XML)) {~}

• The first argument to VALUE must be a single element. If the XQuery expression results in multiple elements, an error occurs.

The following example illustrates how to call VALUE in a metric definition using ApplySimple.

Sample metric definition using ApplySimple and VALUE

Sample SQL

Select a11.BOOK_ID  BOOK_ID,
       a11.BOOK_XML.value('/book[1]/title[1]', 'varchar(100)')  WJXBFS1
from LU_BOOK       a11

Sample result from report using metric using VALUE

Modeling XML data as a Logical Table using VALUE and NODES

The NODES and VALUE methods can be used to return a result table from the evaluation of XQuery expressions against data in an XML column.  You can use MicroStrategy logical views to make a column of XML look like a table to the SQL Engine. The data can then be accessed like any other table modeled in the MicroStrategy schema.

For example, define a new logical table called LV_CHAPTER using the SQL statement below.

select B.book_id,
        C.value('../title[1]', 'varchar(50)') as Title,
        C.value('../author[1]/fn[1]', 'varchar(50)') as AuthorFN,
        C.value('../author[1]/mi[1]', 'varchar(50)') as AuthorMI,
        C.value('../author[1]/ln[1]', 'varchar(50)') as AuthorLN,
        C.value('.', 'varchar(50)') as Chapter,
        C.value('@pages', 'varchar(50)') as ChapterLength
 from LU_BOOK B
cross apply book_xml.nodes('/book/chapter') as resultTable(C)

Logical Table Editor

The NODES method takes the XML column LU_BOOK.BOOK_XML and returns a row for every element matching /book/chapter (i.e., one row for every chapter).
Each call to the VALUE method maps the result of an XQuery expression to a column in the resulting table.  The user must be aware of a few rules for these mappings:

• If the path expression returns no elements, then the column value is NULL for the current row.
• If the path expression returns exactly one element, then that element will be mapped to the data type specified.
• If the path expression returns more than one element, the function will return an error.

The SELECT statement above returns results like the following.

Using the new logical table as a source table, define a new attribute and a new fact:

• Attribute Chapter: @ID = Chapter, Parent = Book
• Fact ChapterLength: ChapterLength
• Ensure that LV_CHAPTER is a source table for Book@ID. You may need to edit the Book attribute in order to this change to be recognized by the product. 

During report execution the logical view syntax will be included in SQL generated by the engine.

Attribute using Logical Table

Attribute Editor and Parent Mapping

Fact using Logical Table

Report example using logical table and NODES and VALUE syntax:

• Attributes: Chapter

• Metrics:

  • Book Units Sold: sum(Units) {Book*, ~+}
  • Number of Pages: sum(ChapterLength) {~+}

select a11.BOOK_ID  BOOK_ID,
        sum(a11.UNITS)  WJXBFS1
 into ##ZZMD00
 from   BOOK_SALES    a11
 group by      a11.BOOK_ID 
 
 select a11.CHAPTER  CHAPTER,
        a11.CHAPTERLENGTH  WJXBFS1
 into ##ZZMD01
 from   (select       B.book_id,
       C.value('../title[1]', 'varchar(50)') as Title,
       C.value('../author[1]/fn[1]', 'varchar(50)') as AuthorFN,
       C.value('../author[1]/mi[1]', 'varchar(50)') as AuthorMI,
       C.value('../author[1]/ln[1]', 'varchar(50)') as AuthorLN,
       C.value('.', 'varchar(50)') as Chapter,
       C.value('@pages', 'varchar(50)') as ChapterLength
from LU_BOOK B
cross apply book_xml.nodes('/book/chapter') as resultTable(C)
 )      a11 
  
 select pa11.CHAPTER  CHAPTER,
        pa13.WJXBFS1  WJXBFS1,
        pa11.WJXBFS1  WJXBFS2
 from   ##ZZMD01      pa11
        join   (select       B.book_id,
        C.value('../title[1]', 'varchar(50)') as Title,
        C.value('../author[1]/fn[1]', 'varchar(50)') as AuthorFN,
        C.value('../author[1]/mi[1]', 'varchar(50)') as AuthorMI,
        C.value('../author[1]/ln[1]', 'varchar(50)') as AuthorLN,
        C.value('.', 'varchar(50)') as Chapter,
        C.value('@pages', 'varchar(50)') as ChapterLength
 from LU_BOOK B
 cross apply book_xml.nodes('/book/chapter') as resultTable(C)
 )      a12
          on   (pa11.CHAPTER = a12.CHAPTER)
        join   ##ZZMD00      pa13
          on   (a12.BOOK_ID = pa13.BOOK_ID)
  
 drop table ##ZZMD00
  
 drop table ##ZZMD01