Sunday, December 28, 2014

BI Publisher - Excel data source Shared (Server) and Local

We plan a local Oracle BI Self Service forum in couple of weeks, so I'm writing few relevant posts. This one will cover the connection to Excel data source in BI Publisher data model using both local and shared (server based) excel files. The next one will involve the Excel files in data mashup with other sources.

When creating a new data model in BI Publisher






I can select Microsoft Excel file as data source. It has to be Excel 1997 - 2003 format (xls):






 


The file can be shared or local:

There is a major deference between them. 
In the Shared case we point to the file while in the local case we upload the file. It influences the refresh, the shared case will show the latest version of the excel (excluding cashing, as any other data source). In the local case, for refresh we need to reload the local file.
There is reason here. The local case is designed to enable a user to create a report using local file and share with other Publisher users that don't have the same local file.

In both cases the Excel file can have multiple sheets. If we have more than one table in a sheet, we need to name the table (right click - Name a Range) in Excel with "BIP_" prefix so BI Publisher recognizes it. We don't have to use names for a single table in a sheet.

 
For example I have 2 excel files:
  1. few_tables.xls: With 3 tables, one BIP_Table1, the others, BIP_Years and Years (no BIP_ prefix).         
  2. One_table.xls: With only the first table and no names.  











The last preparation step for this experiment will explain how do I create a shared file location. 








Under Administrator/Manage BI Publisher


















 We can define data Sources. Select "File".

If we want a new location on the server, we should press "Add Data Source" and specify the location of the files on the server. They can be of various types such as XML, XLS....
 We can always use an existing location.




The existing location - demo_files, is located at    ${xdo.server.config.dir}/repository/DemoFiles.
The ${xdo.server.config.dir} translates to :
MWHOME/user_projects/domains/bifoundation_domain1/config/bipublisher/
I used a full path to server location and named it "files". One can also create a virtual drive for shared location.

Now we are all set, I will import an Excel file in both methods and create a basic report with the combined model, just to show I can work with both types.

I created a new data model and in the "Data Sets" use the pull down menu and selected "Microsoft Excel File". I have to give the new data set a name. I selected a Data Source and pressed the magnifier glass for a list of Excel files. I'll select one_table.xls.

Since there is only default sheet in this file and no names definition there is very little to select here.
If you wonder about the parameter section; To use parameter, you need to create them in another part of the data model. Excel parameters are limited to one value.




Next I will import a table from the other Excel. In this case lets do it "locally".



 I selected Local -> File Name -> Browse and selected a file. After I pressed Upload, it was uploaded to my user (weblogic).




I can select one of 2 tables in the sheet. You might remember I created 3 names in this sheet, but only 2 of them have BIP prefix. Those are the only tables I see.

I selected the BIP_Years table. I have 2 data sources. In the Data tab, I can see data from each. But I actually want to join them, so I can have the year description in with the table data.
To join the data sources, I clicked on "Per_Name_Year", selected "Create Link". 

Next selected Year from the other data source.

The result is:

In Data tab I see they are joined.


I might want to select better column names in the Structure tab. Next I can create the report:


Last step: I'll update both excel files (change 2007 to 2008 and the description for the year 2008) and refresh. As expected, only the change in the shared excel is seen in the report.


In case I insist on updating the local file data, I have to:

Return to data model and refresh the local excel file.
I'm required to select the file again, but I don't have to select the table or sheet in the Excel.
 Now the report is updated with both changes:




Few more issues:

if your version is 11.1.1.7.0 and above, you might have noticed the "Manage Private Data Source" option:

With it, private connections for OLAP, JDBC, Web Service, and HTTP data sources are supported in BI Publisher and can be created by users with data model creation privileges.(described here). So it's interesting, but not relevant to our excel case.



When creating a new report there is a "Upload Spreadsheet" option. For simplicity, it creates a data model, with Local Excel, just without the table selection option. 



Sunday, December 14, 2014

IL Self Service BI Forum - 6-Jan-2015

פורום BI הקרוב יתקיים ב6 בינואר 2015, בנושא Self Service BI.



Oracle BI Forum

בית אורקל הרצליה
רח' משכית 25, קומה 3 חדר פיקאסו 

יום ג', 6 לינואר 2015
9:00 – 14:00



פתרונות BI קלאסיים מעניקים לנו תשתיות המאפשרות לבנות דוחות ולוחות מכוונים מול עולמות תוכן מוכנים, אך רובנו יודעים שלעיתים יש מקורות מידע ארעיים אותם צריך לנתח במהירות ללא צורך בעזרה של מפתחי המערכות.
נציג כיצד משתמשי הקצה יכולים בצורה פשוטה ומהירה לבנות דוחות מתוחכמים על בסיס מידע היושב להם על המכונה הפרטית.

במהלך המפגש, נציג מספר דוגמאות איך לבצע באמצעות כלים שונים של אורקל:
  1. BICS – BI Cloud services
  2. BI publisher – self service
  3. Endeca information discovery – self service
  4. OBIEE logical SQL
  5. OBIEE Physical SQL
ההשתתפות אינה כרוכה בתשלום אך מותנת בהרשמה מראש.
מספר המקומות מוגבל.




Thursday, December 11, 2014

Simple OMM4OBI use cases with OBIEE

After the Installation and Configuration of the OMM4OBI (Oracle Metadata Management for OBI) lets do some work with it.

First of all, this is a nice documentation utility for the RPD:



But it's much more.

I can simply browse the content or Search it:



I can click any object and drill to it content:

 One of the features that impressed me the most was the was the "Trace Data Impact",
I selected the Company column (Sample App lite -> Offices) and started with textual display:

I immediately got a list where the Company column participated:

I can see in each object where the company column is used. 

Now I can export it to excel, see in a graphical view (in a moment) or select one object to see in Metadata Browser, where I can see list of details about it.

In graphical mode it looks nice as well:
I'll do some drilling on the BI Repository and Order Details analysis:

Now I see Company column is also part of H2 Offices hierarchy, bu much more interesting, it's in the Criteria, the Filter and Layout table of the Order Details analysis. Lets check it's true in OBIEE:









There are many other options in the tool, including the important Search.
 After I pressed Search

I can limit the search:

It is a smart Search that understands the various components of OBIEE!
I'll just search for Order Details. Apparently i have quite a lot of objects with Order Details in the name, but that's because the search is done on all combination of the phrase.

I'll search for "Order Details" instead, I will have only 7 results:

I can click on "Order Details" Answers report and examine it's components, for example criteria:
Select the Trace Lineage option and parameters:
 And see the entire structure till the physical layer:
 
I can drill into the criteria, select a specific column and highlight its sources:





I would like to test one more thing; The ability to create a model based on Answers, that means a model per specific analysis.

I'll create a new model and Select Import from Answers:
 The XML file that needs to be provided is the copy / paste of the XML in advanced tab of the analysis. I'll do it with the same Order Detail analysis:

Now I can explore it 

and even create data flow overview:

 

This is only very basic use case with OBIEE. The real power of the tool comes when you combine database, ETL and UI tools in one all covering solution.