OBIEE with Microsoft Analysis Services (and Tabular) - Real Life Guide

If you search the net, you might find lots of posts about setting OBIEE to work with Microsoft Analysis Services as data source (here, here, here... The first one, by Suman Busireddy from KPI Partners is the most recent and very nice) . Unfortunately most of them seem to be based on small cases or personal trials of the writer. When you actually work with it, there are few additional things to be aware of. This is the main focus of this post.

The first part is to set up the IIS to work with your relevant version of Microsoft Analysis Services. You need to follow the Microsoft document Configure HTTP Access to Analysis Services on Internet Information Services (IIS) 7.0 (the default version at the moment is SQL Server 2014, you might need the ""Other Versions" option for 2012 or 2008). The above mentioned posts did something very similar, but in real life you need the local System Administrator, to be involved, since in different companies, you will find various approaches to security. (Most of the problems are with the "Identity" definition and the "Connect By" option, that sometimes requires a specific user defined). 

Once you are done with the IIS setup you have a MSMDPUMP.INI file, you edited in Step 4 of that document. NEVER leave it as Microsoft recommends in the note. The default is to support very small amount of parallel connections. As a result, with any Dashboard with over 4 analysis you will see random errors.

The solution is to make additional modifications to the ini file. This is how I like it:

instead of Microsoft recommendation:

<ConfigurationSettings>
    <ServerName>localhost</ServerName>
    <SessionTimeout>3600</SessionTimeout>
    <ConnectionPoolSize>100</ConnectionPoolSize>
</ConfigurationSettings>

I prefer:

<ConfigurationSettings>

    <ServerName>SQL_SERVER_2012</ServerName>

    <SessionTimeout>3600</SessionTimeout>

    <ConnectionPoolSize>200</ConnectionPoolSize>

    <MinThreadPoolSize>200</MinThreadPoolSize>

    <MaxThreadPoolSize>500</MaxThreadPoolSize>

    <MaxThreadsPerClient>100</MaxThreadsPerClient>

</ConfigurationSettings>

The server name might not be localhost. That is why the specific server name.It depends on the location of the DB and the IIS.

 It is best to test the connection ( http://localhost/OLAP/msmdpump.dll   or http://YOUR_Server_NAME/OLAP/msmdpump.dll) with SQL Server Management Studio, as advised by Microsoft.

Now you can import in OBIEE Administration the Metadata of the connection type XMLA with the relevant URL  (http://localhost/OLAP/msmdpump.dll   or http://YOUR_Server_NAME/OLAP/msmdpump.dll).

Few additional Points

After the import, it is very common to see many similar hierarchies in the each Dimension.

You have can follow the advise of Suman Busireddy from KPI Partners here and delete some. Since every Hierarchy in Microsoft creates columns in OBIEE you might have several columns that seems like the same one (day, day#1...) It is very important to use the one that is your main Hierarchy in Database, otherwise you might create unnecessary "joins" in the query (If you use day from hierarchy A and Month from Hierarchy B from the same dimension, for example).

You might also see many KEYxx and MEMBER_VALUExx redundant columns, you might want to remove them from the presentation layer. There are rare case you might want to use them.  

In Physical layer the default definition of Measures is External Aggregation. There are few very rare cases you might want to change. Remember it will always have performance price, since the OBIEE server will be forced to make the calculations locally.

 

In Physical Layer, you might want to modify date columns do be defined as Date and not the typial Varchar(10).

If you create a cube from scratch, it might be a good idea to create unique names in different columns and hierarchies.


Most columns in OBIEE have descriptor ID and as a result use it rather than the value you see in the column. To see the actual descriptor id, you can always create a filter on a value in analysis and select "convert to SQL".
After you discover the actual format, you can often mimic it when you try to navigate from cube to relational data.


One last but critical topic (that is relevant to Essbase users as well): In Connection Pool, mark the "use session" option. If you don't, when you cancel a request, the cancellation is not passed to the database. Since we usually cancel the most problematic queries, we are tired waiting for. It is important to cancel those queries on database as well.

Next time I will talk about specific use cases of Evaluate, to improve performance.