Data Mart Report in MicroStrategy (II)

Some time ago I published an entry in relation to the creation of Datamart Report in Microstrategy, which you can find at this link:

http://bipostit.com/2015/02/21/data-mart-report-in-microstrategy/

Now I will devote a few lines to speak further about configuration and characteristics of this functionality.

As I said in the previous post, once you press the button “Configure Datamart”, you have a menu where you can customize the creation / insertion options :

DataMart_Report

General Tab:

DAtamart_report2

  • In the dropdown menu, you can choose the appropriate database instance
  • We choose a table name. This table name must be supportable for the naming convention of the server database.
  • Choose between 2 options:
    – Create a new table: With this option, each time the report runs replaces the existing table
    – Append to an existing table: Each time you run the report, resulting data are appended to the table.

At this point I wanted to clarify that this report always uses the creation statement (“CREATE TABLE”) although you choose the option “Append to an existing table.”
In this case,if we visualize the generated SQL we will see a message like this:

(Note: Table existed. Ignore the failure.)

and then the “Insert” statement.

Advanced Tab.

Datamart_report_5

In this tab you can configure:

Control

  • Maximum execution time (seconds)
  • Maximum number of rows (Analytical Engine)

Table creation properties:

  • Table Qualifier: String to be placed before the “TABLE” word in the “CREATE TABLE” statement.
  • Table Descriptor: Sstring to be placed after the “TABLE” keyword in the “CREATE TABLE” statement.
  • Table Option: String to be placed after the table name in “CREATE TABLE” statements.
  • Create Table Post String: Append a string after the “CREATE TABLE” statement.
  • Table Space: Append a string at the end of the “CREATE TABLE” statement, but before any index definitions.
  • Table Prefix: Prefix for a table name in “CREATE TABLE” statements.
  • Table type: Set whether the table is permanent or true temporary.

Example, create table “TABLE TEST”:

drop table |Table Prefix|TABLE_TEST

create |Table Qualifier| table |Table Descriptor||Table Prefix|TABLE_TEST |Table Option|
|Tablespace| as
select distinct

…………….

…………….                           

Sentences SQL

DAtamart_report3

Is possible to use custom SQL sentences:

  • before the creation of the table
  • After the creation of the table
  • before data insertion

Ejemple in SQL view:

drop table TABLE_TEST

|SQL before the creation of the table|

|SQL before data insertion|

create table TABLE_TEST  as

select         distinct

…………….

……………..

|SQL After the creation of the table|

 

These SQL statements are not validated before running the report, so it must have the proper syntax to the purpose we need.

SQL statements must be separated by semicolon “; ”

MicroStrategy provides us a number of variables that are dynamically replaced considering the SQL conditions under which the report runs. Here you have some of the most useful:

  • !u -> User name
  • !d -> Date on which the table was created
  • !o -> Report name
  • ??? -> Temporary table names
  • !!! -> All column names
  • !a -> Attribute column names
  • !! -> Exclamation (!)
  • !!= -> Does not equal (!=)
  • !j -> Job ID
  • !r -> Report GUID
  • !t -> Timestamp
  • !p -> Project Name
  • !z -> Project GUID
  • !s -> User session GUID

For example, if we add the query:

GRANT ALL PRIVILEGES TO !u

in the field “before the table creation”, the result of the execution will be:

drop table TABLE_TEST

GRANT ALL PRIVILEGES TO usuario

create table TABLE_TEST  as

select

….

….

Being “user” the user who launched the Datamart report.

In this post I also wanted to explain a problem that I have recently found. I wanted to create a Datamart report in which only appear as columns the fields DESC of the attributes, NOT the ID fields.
Technically, I have seen that in Datamart report should always be included the ID of the participant attributes, because they are elements which MicroStrategy uses to generate the appropriate SQL.

To overcome this disadvantage I present three alternatives that maybe you can help:

1 – Define new attributes which associates to IDs the DESC values.

2 – Use Post SQL (VLDB Properties) to change the result table, erasing the ID columns of the table generated.

3 – Working with the generated table by the Datamart report as a auxiliar table and use SQL Post (VLDB Properties) for insert into a new table the only the fields which we want.

Thank you for your time. I hope you find it usefull

Leave a Reply

Your email address will not be published. Required fields are marked *