This is the 3rd and final part of the BI EE and HFR integration. In the prior blog entries here and here, i had shown the integration between a relational BI EE report and an HFR report. But there are cases wherein one would need integration between a BI EE Essbase Report and an HFR report. In such cases, it is not possible from BI EE directly to generate the dynamic URL as specified before. One of the main reasons for that is, the concatenation of custom HTML strings are not supported in MDX queries. So, in order to achieve such integration we would have to bring in a relational source somehow. Lets understand this through an example. Consider a sample report in BI EE shown below
As you see, its a very simple report based on the Demo->Basic cube. Now our intention is to have a custom hyperlink column called as Drill which would be used for drilling to a HFR report. And while drilling, the corresponding row member names for the Gen3, Year and Gen3, Market need to be passed to the target report. The target HFR report is given below
Now, what we had done here was, we used the capability of BI EE to generate a dynamic URL from answers itself. But if we do that on a pure Essbase sourced answers report, it would throw an error as normal string functions are not supported on a MOLAP source. So, in order to overcome this, we shall use the horizontal federation approach. For this we would need two conforming dimension tables as shown below (these dimension tables is where we would pass the values to the URL)
Now, lets setup horizontal federation for the 2 conforming Year and Market dimensions by importing these 2 tables into the physical layer. Also create a custom physical alias table in the physical layer as shown below
SELECT YEAR, QUARTER, MONTH, MARKET, REGION, CITY, '<a href=http://localhost:19000/workspace/index.jsp?module=tools.relatedcontent&repository_path=/BIEE%20Integration&elementType=2&repository_name=BIEE%20Integration&repository_format_id=html&run=1&sso_username=admin1&sso_password=welcome1&attribute=Product.id.Product&attribute=Market.id.'||City||'&attribute=Year.id.'||Month||'&attribute=Accounts.id.Profit&attribute=Scenario.id.Actual >HFR Drill</a>' CustomURL FROM MARKET, YEAR
As you see, what this alias does is, it creates a cartestian join between the Year and Market tables. This would be used to produce our dynamic URL. Also, this would be used as a fact placeholder to generate the URL. Be careful while using this cartesian join approach on large dimensions. If you have very big dimensions, i would recommend using a complete fact view with only the necessary dimension values. The below screenshots shows how to setup the federation. The first step is to add another provider LTS for the Year dimension for all its columns.
Similarly set the same for the Market dimension. Once that is done, include the cartesian alias that we created into the BMM. Our idea is to use the CustomURL (in the above query) column as the column that generates the dynamic URL. Since we are including this as a fact column and since we are using the conforming dimensions approach, we need to ensure that the measures of Essbase and this CustomURL are of the same grain. So, make sure to set First or Last as the aggregation property of the CustomURL column.
Now, lets create the answers report as shown below
As you see, we get the URL in our report along with the Actual and Budget measures. Now, lets convert the URL column type into HTML. This will convert it into a drillable column as shown below
Now, if you click on any one of the links, it would take you to the HFR report. Also, it would pass Year and the Market dimension values as a parameter as well.
There are other possible approaches as well. But i wanted to use this approach in order to demonstrate the kind of flexibility BI EE offers from an integration standpoint. To make this even more flexibile instead of hardcoding the values, just create a function in the database and use EVALUATE to generate the URL.