BI Pubilsher - SQL (OBIEE) and Excel file data mashup

As promised, in BI Publisher - Excel data source Shared (Server) and Local, this post will deal with data mashup. This example will join an excel file with data from OBIEE data model in a Publisher report.

Actually things can be much more interesting, for example from OBIEE Samples in the data model "Multi-Source DM", located in "05.Published Reporting\c.Integration\Reports\Data Models\" there is an example of data model including Excel, DB SQL, OBIEE Data Model SQL and OLAP:

We will do a simple example:

As you remember from previous post, I have an excel file "few_tables.xls" that includes a table of remarks for each year: 

Next I'll create a data model:

Add SQL Query:

Based on OBIEE:

Run Query Builder, select relevant tables from Sample Sales and mark the columns I want (no need for joins, the are in the model):

Check in Results all is working and save:

Next I'll Import the Years tables from Excel:

Create a link between the tables, from the Year column:

And select the join column in the SQL data source:

All I have left is to see the Data, Save it as Sample Data and Save the Model:

Now I can create my Report:

And maybe, make it slightly more impressive:

By the way, to the best of my knowledge, while we can use an Analysis as data source, we can't join it to other data sources. Pity, that could be nice. 

Of course, there is a workaround to it. Instead using the Oracle BI Analysis as data source, we can use the logical SQL of the analysis and copy paste it from the "advanced tab" of the analysis to the SQL Query with OBIEE data source:

Go to advanced tab, and press "New Analysis":

Copy the SQL:

Paste it as SQL Query in data model:

join the excel:

It's working: