Oracle Big Data Discovery (BDD) Webinar on 5-Feb-2015

You are working with Hadoop and believe it can be faster and easier?

1. See this video https://www.youtube.com/watch?v=tnAgdLeFCQY (I know I already published this link; I like the video).

2. On 5-Feb-2015 you can join a webinar about "Introducing Oracle Big Data Discovery: The Visual Face of Hadoop". 

The link is:http://www.dbta.com/Webinars/Details.aspx?EventID=707.

 

OBIEE BUNDLE PATCH 11.1.1.7.150120 AKA 11.1.1.7.10 (?) is available

Please note OBIEE Bundle Patch 11.1.1.7.151020 is available. You might prefer it.

OBIEE 11.1.1.7 new general bundle patch (number 10?), 11.1.1.7.150120 is available in in Patch 20124371can be installed on any previous 11.1.1.7 release.

This patch is probably the last bundle patch of 11.1.1.7. Unlike most of the other, it is recommended for security reasons as well.

I had no problems downloading the patch this time. If you have problems, try an add-on to Firefox "DownloadThemAll".

The Patch is platform specific and is available for:

• HP-UX Itanium
• IBM AIX on POWER Systems (64-bit)
• Linux x86
• Linux x86-64
• MS Windows (32-bit)
• MS Windows (64-bit)
• Oracle Solaris on SPARC (64-bit)
• Oracle Solaris on x86-64 (64-bit)

You can read the readme file here.

Unlike 11.1.1.6 patches, this patchset is downloaded in a single big file+one bonus patch. So just for the record (since you don't need to download them separately):

Patch	Abstract
16913445	Oracle Business Intelligence Installer (BIINST) - same as previous patches
19822893	Oracle Business Intelligence Publisher (BIP)
19825503	Enterprise Performance Management Components (BIFNDNEPM)
19822857	Oracle Business Intelligence Server (BISERVER)
19822826	Oracle Business Intelligence Presentation Services (BIPS)
19823874	Oracle Real-Time Decisions (RTD)
16997936	Oracle Business Intelligence ADF Components (BIADFCOMPS) - same as previous patches
20022695	Oracle Business Intelligence Platform Client Installers and MapViewer

As usual there is a bonus patch: Patch 16569379  (1111770)- Dynamic Monitoring Service patch. It is platform dependent. If you installed it with 11.1.1.7.1 or later, that's enough. It's the same.

According to "Oracle EPM BI proactive support" here additional patch Patch 18277370 (MERGE REQUEST ON TOP OF 11.1.1.7.0 FOR BUGS 18071063 18176711) is required for IE11 console users.

In the ReadMe here there are special instructions for upgrading from 11.1.1.6.X to 11.1.1.7.150120 at  "Section 3.1: For Customers Moving From 11.1.1.6.x to 11.1.1.7.150120". DON'T upgrade before you read and understand it.

In section 5.3 of the readme there is also advise to consider, in case you have problems similar to bug 16222516 (COMPILATION TIME FOR A SPECIFIC REPORT TAKES TOO LONG) to update opmn.xml (see details here).

Bugs fixes

 Oracle Business Intelligence Publisher (BIP)

20315116        NULL PIOINT EXCEPTION

18089991        Fix For Bug 18089991

17925571        A COMMON WAY TO DISPLAY "TABLE OF CONTENTS" LINE IN TOC PAGE FOR OTHER LANGUAGES

17380366        BI PUBLISHER WEBSERVICE(WSDL) DATASET DONOT SUPPORT HTTP AUTHENTICATION

18829523        CANNOT SCHEDULE NEW JOBS OR PAUSE EXISTING JOBS

19311376        HIDDEN PARAMETERS ARE NOT BEING READ WHEN REPORTS ARE RUN

18957391        Fix for Bug 18957391

18756368        Fix for Bug 18756368

19051563        EXCEL EXPORT SHOWS DIFFERENT DATE FORMAT AFTER UPGRADE  TO 11.1.1.7.14.225

19336314        DYNAMIC HYPERLINK ISSUES

18039992        CORRECT ACTION ABOOUT  MIGRATE EXISTING 10G REPORTS(UPGRADED USING THE UA)TO 11G

17856977        ADDITIONAL 'WITH' CLAUSE

17643570        BI PUBLISHER 11G DELIVERY ERROR FOR SFTP

17284897        BIP 11.1.1.6 OUTPUT COLUMNS DO NOT GET AUTO RE-SIZED IN EXCEL

17190121        PSR:PERF:BIP:FO OBJECTS ARE NOT GENERATED IN SYSTEM TEMPORARY DIRECTORY

17812503        WHEN USING NON-STANDARD SQL THE ROW TAG DOESN'T INCLUDE THE DATASET NAME

18370060        XLSX OUTPUT MISSING BACKGROUND COLOR SPECIFIED IN FO:BLOCK

19437778        CEAL:COLUMN WIDTH IS SO SMALL THAT USER CAN'T READ THE INFORMATION

18756490        PUBLISHING A REPORT VIA WEBDAV FAILS

19279322        REL5:ACCOUNT ANALYSIS REPORTS XLAAARPT INTERMITTENTLY PERFORMS POORLY

16269827        CATALOG DESCRIPTION AND CREATED BY NOT SYNCED BETWEEN NODES IN CLUSTER

19120975        DM EDITOR: SEARCH DOESN'T WORK WHEN ONE OF THE LOV HAS NULL VALUE IN IT.

19120962        DM EDITOR THROWS ERROR ON 'VIEW DATA' WHEN LOV HAS A NULL VALUE IN IT .

17891336        ER: UNABLE TO DEFINE XML ROW TAG FOR NON-STD SQL DATASET

19000814        QA:PARAMETER MENU IS NOT DISPLAYED IF DEFAULT VALUE FOR THE PARAMETER CONTAINS '

Enterprise Performance Management Components Installed from BI Installer 11.1.1.7.0 (BIFNDNEPM)

19905699        OBIEE CONFIGURATION FAILS AFTER APPLYING BIFNDNEPM_11.1.1.7.0BI-FAREL10-BP PTCH

18688157        ESSRFRULESFILE.READ() IS NOT ABLE TO READ RULE FILE WITH > 32K SQL QUERY SIZE

18622798        WRITING RULES FILE WITH SQL QUERY SIZE > 32K DOES NOT GIVE ANY ERRORS/EXCEPTIONS

18603362        GSE:L08D01: UNABLE TO PROCESS RULES FILE ERROR WHILE BUILDING DIMENSION

18665665        ENABLECLEARMODE FALSE CAUSES CPU TO SPIKE

15844420        ESSBASE DOES NOT START UP DUE TO ESSBASE.LCK

18644424        FA:PERIOD_NAME COLUMN OF GL_INTERFACE SHOULD BE POPULATED

18875816        ESSBASE V11.1.2.2.106: MDX QUERY USES UP TO 99% OF RAM

17418844        SECURITY FILTER INTERACTION WITH DB CAUSES ESSBASE HANG IF NO MEMORY SPACE

18406253        UNEXPECTED ERROR 1200467 WHEN EXECUTING MDX STATEMENT ON EMPTY DB

17664071        MDX QUERY TAKES 99% OF RAM

18311581        MDX QUERY CRASHES ESSBASE APPLICATION WITH AN XCP

18164285        ERROR EXECUTING FORMULA FOR [CHARGE_OTHERS]: STATUS CODE [1130001] IN FUNCTION

19411713        UNCLEAR DIAGNOSTIC FOR OCI SQL INTERFACE ERRORS

19410821        DATA RETRIEVING WITH OCI DISPLAYS ONLY 1ST CHARS IN COLUMN TITLES

19350638        OCI DOES NOT ACCEPT QUERY WITH CHINESE CHARS

19327521        SQL IFC WITH OCI RETRIEVES GARBLED NUMBER DATA ON UNICODE APPLICATION

19316030        ODBC DSN RULES FILES IN UNICODE APPLICATION RETRIEVE GARBLED DATA

19241357        CUBE OUTLINE LOAD ERROR: LOADING JUNK

Oracle Business Intelligence Server (BISERVER)

18660935        NQSSERVER DUMPING CORE WHILE RUNNING NQSMODIFYMETADAT

20097298        AIX: ATOMICS CHANGES TO FIX INTERMITTENT CRASH IN NQSSERVER AND SAWSERVER 

18764649        ADD TEST CASE FOR BUG 18282775

19718735        DRAG AND DROP NOT WORKING WITH PRESENTATION HIERARCHY

19918067        Fix for Bug 19918067

18826148        ORDERING OF MULTIPLE HIERACHIES IN A SUBJECT AREA NOT RETAINED IN MUD ENVI

18402905        REL10: DFF EXTENDER NOT JOINING TO THE LOGICAL TABLE

19357585        ESSBASE: [NQSERROR: 46008] WHEN DRILLING INTO DIMENSIONS IN OBIEE ANSWERS QUERY

17407230        EVALUATE DOES NOT WORK WITH ESSBASE TOPCOUNT FUNCTION

18282775        [NQSERROR: 42039] COLUMNS IN BY CLAUSE OF REPORT TOTALLING FUNCTION MUST BE

9471417          ASSERTION ERROR AFTER APPLYING PATCH FOR BUG 8323431

19629472        RUNNING IBOT CAUSES NQSERVER CRASH MOVING UP TO 11.1.1.7.140715 FROM .131017

17796736        ESSBASE ERROR: UNKNOWN MEMBER XXXXXXXXX USED IN QUERY (HY000)

18535648        PSR:PERF:BI:NQSMETDATAPROCESS DURING CSA OPERATION HAS HIGH MEMORY USAGE

19611591        OBIS INTEGRATION WITH CFS SHOULD BE BASED ON JPS JSE CONFIG FILE

18952740        ERROR IN REPORTS AFTER APPLYING THE PATCH 18891283 ON OBIEE11.1.1.7.140415

18321511        OBIEE 11G: ESSBASE DATASOURCE: SORT ON COLUMN FAILS

17632454        USE NUMERIC FOR PHYSICAL COLUMN, FACING WARNING 39064

17583386        STRANGE BEHAVIOUR OF ESSBASE RE-IMPORT

17547728        ESSBASE METADATA RE-IMPORT ISSUE RESULTS IN ERROR 38133

17449036        METADATA DICTIONARY LIMIT ON URL CHARACTERS

16303231        "USE FOR DISPLAY" PROPERTY CHANGED TO  CHECKED WHEN DOING CONSISTENCY CHECK

11810367        CONSISTENCY CHECK - OPTION TO TURN OFF (AND ON) CERTAIN WARNINGS

19150497        ISLEAF CALCULATION SHOULD TAKE BOTH FACT TABLES INTO ACCOUNT

Oracle Business Intelligence Presentation Services (BIPS)

19607805        EXPORT OF RESULTS TO CSV CREATES FILE WITH WRONG NUMBER OF ROWS AND COLUMNS

16870455        KPI TREND, CHANGE AND CHANGE% NO LONGER BEING REPORTED 

19704538        CERNER:THE EXPORT DOES NOT HAPPEN, NEED TO RESTART THE SERVICES

20046059        BIFNDN_MAIN:CRASH IN CSV GENERATION FROM FREED-MEMORY-READ

20035249        BIFNDN_MAIN:CRASH CAUSED BY FREED-MEMORY-READ IN DXE

19862074        BIFNDN_11.1.1.7.0BIBP:CRASH IN SAWSERVER

17856009        NLS:DATETIME DOES NOT CONVERTED DST FOR TIMEZONE MONTHS START WITH SEP. OR OCT.

19012137        INVALID SESSION ID ERROR

19918112        Fix for Bug 19918112

19165715        SES CRAWL CAUSES OBIEE SERVER CRASH

19926247        BIPS SHOULD WRITE OUT PERTITENT INFO ABOUT CRASHING THREAD

18551831        SMART WATCHLIST DOES NOT INDENT CORRECTLY WHEN PRINTED AS HTML WITH IE

18477109        SMART WATCHLIST PRINT OUT TO INCLUDE CUSTOM COLUMNS (HTML, PDF, EXCEL)

18318736        KPI BUILDER STATES TAB LIMITS NOT ALIGNED WITH ITS BOXES

18309295        GENERATEHEAD ACIOA5LN ASSERTION FAILURE DYNAMICSQL.CPP ON KPI CHART W/TRENDING

18147796        SMART WATCHLIST ISSUE WITH CUSTOM COLUMNS RE-ARRANGED

17015797        PRINT/EXPORT OF PAGE WITH KPI NOT SHOWING DOWN TREND IMAGE IN ALL BROWSERS

13808782        CUSTOMIZED COLUMN NAMES ARE NOT USED IN PRINTABLE HTML OR PRINTABLE PDF

19872336        COMPLETE FIX OF BUG#19811134  - USE CASE 3

19672412        FILTER BY CODE COLUMN FIELD IN PROMPT IS NOT SAVED AFTER CHECKED.

16673211        QA:EXPORT TO EXCEL DISPLAYS A BLANK PAGE ON FIRST TRY

18092868        GETDASHBOARDPROMPTSEXPRESSION OUTPUT ISSUE

16394144        QA:AGENT CREATED FROM REPORT'S CATALOG SCHEDULE OPTION  IS WRONG

19308986        DASHBOARD PROMPT DEFAULT VALUES CHANGE TO COLUMN DESCRIPTOR ID

17820136        EDIT FORMULA FILTER... FEATURE GENERATES WRONG SYNTAX FOR VARIABLES

18116375        QA:LINE SPACE NEEDS TO BE RESTRICTED BETWEEN THE VIEWS WITH THE DB PAGE.

19080524        EXPORT DATA TO TAB DELIMITED CSV FILE ADDS A BLANK LINE TO END OF FILE

19132778        DASHBOARD PROMPT DEFAULT SELECTION FOR SPECIFIC VALUE IS NOT GETTING SAVED

19221510        NAVIGATION LINK TO A WEB PAGE IS SHOWN IN WEB ARCHIVE EXPORT (MHT)

19645230        UNRELATED DIMENSION NODES OPEN WHEN DRILLING ON OTHER NODES

19137814        REQUIRED USER FLAG NOT SET AFTER UPGRADING FROM 10G

19055274        ERROR Q4NU7XSN WHEN APPLYING SAVE SELECTION (AFTER MERGE PATCH 18678922)

17037959        QA: DOWNLOAD: DOWNLOAD ANY TITLE ANALYSIS SHOWS UNTITLED ANALYSIS IN EXCEL

18611716        NLS:FR:TR:PERCENTAGE FORMAT IN CHART TOOLTIPS ARE WRONG

16181444        NLS:DECIMAL SEPARATOR IN PIE/100% STACK CHART TOOLTIPS NOT HONOR USER LOCALE

14786876        LOGSCALE NOT SUPPORTED IN WATERFALL VIEW

19682114        COLUMNFILTERPROMPTDEFN'S LABEL OBJECT IS NULL FOR ALL NEW CREATED REPORTS

18061539        FORMAT FOR DISPLAYING DATE/TIME IN EXCEL ARE INCORRECT.

18149118        Fix for Bug 18149118

18149143        Fix for Bug 18149143

18484928        PERCENTAGE VALUES SHOWN INCORRECTLY WHEN ""SHOW DATA LABELS" IS SET TO ALWAYS

18696181        OBIEE 11G: NEXT ROW REPEAT THE LAST DISPLAYED RECORD WITH 'GRAND TOTAL' BEFORE

18280602        WHEN EXPORT FROM PIVOT TABLE WITH SUBTOTAL, SOME DATAS ARE DUPLICATED.

16624115        QA:OBJECTIVE/INITIATIVE ACTION LINK SHOULD PASS DIMENSION BAR POV VALUE AS PARAM

16856432        QA:KPI ACTION LINK NOT PASSING PARAM CORRECTLY FOR COL HAVING DESCRIPTOR_IDOF

18887150        WITH LARGE REPORTS SAWSERVER MEMORY USAGE INCREASES TO  >10G  AND THEN CRASHES

19357001        URL PARAMETERS DO NOT WORK CORRECTLY IF QUOTES ARE OMITTED FROM VALUES

19611592        OBIPS INTEGRATION WITH CFS SHOULD BE BASED ON JPS JSE CONFIG FILE

18300808        SMART WATCHLIST PRINTS (EXCEL, PDF ) WRONG STATUSES AS CONFIGURED

18111632        CUSTOM DATA FORMAT NOT WORKING IN KPI

18109929        SCORECARD SETTINGS DIALOG WITH CHROME NOT USABLE

17923294        CAUSAL LINK ARROW ON STRATEGY MAP AND CAUSE EFFECT MAP DOESNT CHANGE THICKNESS

16490413        INCONSISTENT ACTION FOR EDIT LINK WHICH FROM CATALOG OR FROM RESULTS OPENED

13789142        DASHBOARD PROMPTS AREN'T APPLIED TO SMART WATCHLISTS ON DASHBOARD PAGES

13360801        QA: NEED TO ENABLE PRINT ON DASHBOARD FOR SMART WATCHLIST

18501957        OBIEE 11G - BLANK MERGED ROW AT THE END OF EXCEL 2007 FILE

17844505        ALL COLUMN VALUES VARIABLE SELECTION IN PROMPTS NOT WORKING FOR SQL RESULTS

17583780        OBIEE 11G -  APPLY COLUMN SELECTOR CUSTOMIZATION LOOSING FILTERS FOR NAVIGATION

17830429        NLS:BIDI:SHORT DATETIME FORMAT IS WRONG ON HEBREW LOCALE

16962202        FILTER EXPRESSION INCLUDING ; DOES NOT WORK CORRECTLY

18682606        UNABLE TO MODIFY FORMAT FOR CONDITIONAL FORMATTING WHEN CSS "DISPLAY:NONE" USED

Oracle Real-Time Decisions (RTD)

18698689        Fix for Bug 18698689

Oracle Big Data Discovery video on YouTube

While waiting impatiantly for Oracle BDD (Big Data Discovery), here is a short video http://youtu.be/tnAgdLeFCQY.

There is also a short demo on Warranty Claims + Demographics  as part of the new YouTube BDD channel.

OBIEE- Understanding Selection Steps logic and forcing it to be based on Measures

In this post I will give a reason to use selection steps, explain that Selection Steps logic is base on dimension, or actually, non-measure columns and will describe a way of forcing the Selection Step logic to work on measure values.

I assume you understand the basics of Selection Steps or read the post OBIEE 11g Hierarchical Columns and Selection Steps or something similar.

I will describe the same example described in the post "OBIEE- Understanding Selection Steps logic and forcing them to be based on Measures".

 It's a simple analysis:

With columns: Year, Product_Type and Revenue... The only not totally trivial part is the % of year calculation that is 100*Revenue / (Sum Revenue by Year)   (the formal code is 100*"Base Facts"."Revenue"/sum("Base Facts"."Revenue" by "Time"."Per Name Year")).

This how the result looks like, with totals by Year:

The request was to see the above 4 columns combination, only for Product Type greater than 150,000, but to maintain the correct "% of Year".   

The naive approach would be to add filter Revenue >150,000

But in this case the "% of Year" is totally wrong, since it's based only on the selected data:

 

------------------------------------------------------ a remark --------------------------------------------------
Actually OBIEE is very smart and on a slightly different problem (% of Year > some percentage) is doing great with filters, since in that case, we are doing the filter on the problematic column:

------------------------------------------------------ a remark -------------------------------------------------- 

The natural solution is to use selection steps, since they are executed after the initial select statement. 

But here we have a slight problem. Selection steps are not defined on measures, actually they are defined on non-measure columns. Why do I care? 

Because the naive approach would be to try the following selection step:

Per Product Type, keep only Revenue>150,000.

But the result is not as desired (I color-coded red all the "less than 150,000" cases):

 What happened?
 To understand, lets see the initial analysis with Years excluded:

The result of the Selection Step was to remove "Maintenance" and "Install" Product Type, with no consideration of the Year column. Why? Because of the nature of Selection Steps, it's applying measure values on a specific non-measure / dimension column. The only exception of this rule is the "For" option covered here, but it doesn't help in our case.

How can I solve this problem?

I will create a new dummy column that combines Year and Product Type, hide it, and use the same Selection Step.

(I'm lucky since I don't have to cast year to character, it's character by default):

The formula:

The Selection Step:

Hiding the dummy column:

The result (as desired):

You have more than 2 non-measure columns? Concatenate them all.

Summary:

1. Selection Steps in OBIEE are done on non-measure / dimension columns, using measure values.

2. To force / trick the Selection Step to work on measures values directly, we have to create a dummy column that combines all the non-measure keys, and is used as index of measure values in the analysis.  

 

OBIEE - What the "For" in Selection Steps stands for

As you probably remember, in OBIEE 11 there is a powerful option of selection steps, that enables us to add conditions that are executed after the filter / where condition of the analysis.

I covered the issue few times:

• OBIEE 11g Hierarchical Columns and Selection Steps (just to be clear, selection steps work fine on any non-measure column, not only hierarchical).
• OBIEE - Top 2 of top 10 with Selection Steps.
• OBIEE - Comparing performance to peers. 

While creating a condition with Selection Steps, there is a "For" hiding in the center of the Selection Step Wizard:

 After pressing the plus sign, the following is visible:

A question was asked, what is it used for?

I got a little scared seeing the documentation:

 

For:

"Use this area to qualify all of the dimensions of the analysis other than the dimension whose members you want to select, when creating condition steps of type Exception or Top/Bottom. For each dimension, you select which of its members to include. You can select specific members, or All, which specifies to aggregate the members when creating the condition. For example, suppose that you are qualifying the Region dimension. You can select a specific region, such as East, whose value is used in the condition. If you select All, then the values of all regions are aggregated and used in the condition.

You can use the For area to create a qualified data reference (QDR). A QDR is a qualifier that limits one or more of the dimensions to retrieve a single value for a measure column. A QDR is useful when you want to temporarily reference a measure column value without affecting the current status of the dimensions. The following is an example of a QDR:

Add members of Total Products (Rgd Sk Lvl) where "A - Sample Sales"."Base Facts"."1- Revenue", For: Cust Segments Hier: 'Active Singles', 'Baby Boomers' is greater than "A - Sample Sales"."Base Facts"."1- Revenue", For: Cust Segments"

When you specify a QDR, you can specify multiple members for limiting the dimensions. When you specify multiple members, the measure column value is aggregated using the default aggregation from the Oracle BI Server. For example, suppose that you want to create a condition for displaying those Regions in which Units is greater than 100. Suppose that you create a QDR for the Year dimension that specifies 2000 and 2001 and that the default aggregation is Sum. If the values for 2000 and 2001 for the Central region are 50 and 60 respectively, then both those years are displayed, because their sum exceeds the 100 units that was indicated."

I'll do an example that I hope would make it easier to understand.

I created a simple Analysis:

Year, Product_Type and Revenue (Aggregation Rule - Sum)... The only not totally trivial part is the % of year calculation that is 100*Revenue / (Sum Revenue by Year)   (100*"Base Facts"."Revenue"/sum("Base Facts"."Revenue" by "Time"."Per Name Year")). i will use that column in the next post.

Using regular Filters I can limit the data only to those Product Types that have Revenue greater that 200,000.

But I have a slightly different need: I want to limit the data to the Product Type with Revenue  greater that 200,000 at the Year 2009. Of course I could do it with 2 analysis (using filter "based on results of another analysis"), but it's so easy to do with the For option of selection steps!

The original Analysis was (I color coded the Revenue>200,000):

With 2 values over 200,000 at year 2008, 4 at 2009 and 3 at 2010.

In selection step for Product Type I'll keep only Revenue greater that 200,000 ,

But that's not enough, I need it only for the Year 2009. That is where the "For" option kicks in. 

 Now the result is as requested:

I can see only the Product Types with Revenue over 200,000 in the year 2009.

To summarize it to myself:

By default Selection Step work on specific dimension column, "For" option uses the condition with additional dimension / dimensions.

If I would have selected 2 years, for example, 2009 and 2010, I would have limit the results to (Revenue of 2009) + (Revenue of 2010), greater that 200,000.

The default of "For All" takes all the Years Revenue (for each Product Type) and combines it.

So Keeping Product Types with Revenue >200,000 with no "For" definitions will summarize all values for each Product Type and make sure they are greater than 200,000.

More about it, in the next post.  

As you might have noticed, the manual selection of Year=2009 and of 200,000 as minimal Revenue, can be replaced by Prompt.

מצגות פורום Self Service מ6/1/2015

מצגות מפורום Self Service מ-6/1/2015 ניתן להוריד כאן.
זהו קובץ ZIP המכיל את כל המצגות.

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: