ORACLE HFM to PowerBI Integration

[rakeshsaini]

Hello PowerBI Team ,

We Need to Create a PowerBI Reports using 'ORACLE HFM' Data Source. So for this We Need to get Data from 'ORACLE HFM' Data Source and Used this Data in to PowerBI Desktop.

How Can i Create a connection between 'ORACLE HFM' and PowerBI ?

[MerlinXL]

MerlinXL  (merlinxl.com) has an HFM to PowerBI connector.  
1. Install the MerlinXL webservice on an HFM webserver. 
2. Create an HFM webform in HFM Workspace documents

3. Enter the query to the MerlinXL webservice in a PowerBI query specifying the HFM webform name to act as your dataset.  A webservice query template is provided, just change the url, application, and form name.
4. Build your Power BI Dashboard on the returned HFM dataset.

No complex SQL queries or understanding the HFM SQL schema, just a simple HFM dataform is all that is required. (although it does support more complex data queries if required)

The same MerlinXL PowerBI query also works in an Excel power query.

[SandeepKomminen]

Interesting HFM. It would be great if you can provide more details about it. Thanks you

[BPOptimizer]

I think the proposed way is not the best option, because you would have to rebuilt the whole HFM data model from the the DB. The HFM data model ist quite complex as HFM is ROLAP with predefined metadata including n-dimensions with n-hierarchies and attributes.

The best solution is, as long as PowerBI does not support HFM API or ADM drivers, run extended analytics extracts from HFM into a SQL db.

It will offer all the hierarchy model (star schema) and DW extracts. Oracle HFM Extended Analytics

You just have to think about security then, because financial data...companies...group adjustments...users... etc...

[menatalla]

Are there currently alternatives or the proposed solutions , still hold ?

[Azharuddin]

Hyperion can do all the groundwork with consolidation, integrate with your ERP, and currency conversion, but when it comes to visualizing data, or integrating Financial Reporting data with other data sources, Power BI is the answer. The benefits of using Power BI are obvious.

 If Orcale Hyperion Financial Management System used Apis . than is it Possible to Integrate API in PowerBI.

[SandeepKomminen]

In order to connect to an Oracle ORACLE HFM database with Power BI Desktop, the correct Oracle client software must be installed on the computer running Power BI Desktop. Which Oracle client software you use depends on which version of Power BI Desktop you have installed - the 32-bit version or the 64-bit version.

Supported versions: Oracle 9 and later, Oracle client software 8.1.7 and later. To determine which version of Power BI Desktop is installed, select File > About then check the Version: line. 

Once the matching Oracle client driver is installed, you can connect to an Oracle database. Take the following steps to make the connection.

1. From the Get Data window, select Database > Oracle Database

2. In the Oracle Database dialog that appears, provide the name of the server, and select Connect. If a SID is required, you can specify that using the format: ServerName/SID.
3. If you want to import data using a native database query, you can put your query in the SQL Statement box, available by expanding the Advanced options section of the Oracle Database dialog.

4. Once your Oracle database information is entered into the Oracle Database dialog (including any optional information such as a SID or a native database query), select OK to connect.

5. If the Oracle database requires database user credentials, input those credentials in the dialog when prompted. For More Information Visit : (Tekslate) https://goo.gl/vVec5j 

[sroux]

Hi, we do distribute and implement a third party solution to interface HFM directly with Power BI. No need to redesign the model or to design export queries. The solution provides real time connectivity but also query automation toward an operational data store, data mart or datawarehouse.

Feel free to contact for further information.

Good day

Sebastien Roux

[SandeepKomminen]

In order to connect to an Oracle ORACLE HFM database with Power BI Desktop, the correct Oracle client software must be installed on the computer running Power BI Desktop. Which Oracle client software you use depends on which version of Power BI Desktop you have installed - the 32-bit version or the 64-bit version.

Supported versions: Oracle 9 and later, Oracle client software 8.1.7 and later. To determine which version of Power BI Desktop is installed, select File > About then check the Version: line. 

Once the matching Oracle client driver is installed, you can connect to an Oracle database. Take the following steps to make the connection.

1. From the Get Data window, select Database > Oracle Database

2. In the Oracle Database dialog that appears, provide the name of the server, and select Connect. If a SID is required, you can specify that using the format: ServerName/SID.
3. If you want to import data using a native database query, you can put your query in the SQL Statement box, available by expanding the Advanced options section of the Oracle Database dialog.

4. Once your Oracle database information is entered into the Oracle Database dialog (including any optional information such as a SID or a native database query), select OK to connect.

5. If the Oracle database requires database user credentials, input those credentials in the dialog when prompted. For More Information Visit : (Tekslate) https://goo.gl/vVec5j 

[Eric_Zhang]

---

@rakeshsaini wrote:

Hello PowerBI Team ,

We Need to Create a PowerBI Reports using 'ORACLE HFM' Data Source. So for this We Need to get Data from 'ORACLE HFM' Data Source and Used this Data in to PowerBI Desktop.

How Can i Create a connection between 'ORACLE HFM' and PowerBI ?

---

@rakeshsaini

Based on my research, ORACLE HFM seems also a reporting and analysis system. So speaking strictly, it is not a data source, as it neither seems not providing any data feed API nor a database. So to use the data that is used in 'ORACLE HFM', you just connect to the same datasources(Oracle,MySQL etc) that are applied in 'ORACLE HFM',

[rakeshsaini]

Thanks @Eric_Zhang . 'ORACLE HFM' an Reporting System. So  Orcale HFM also used an Data Source . So we can used that Data Source with Power-BI.

I am not Clear , So I have both Options . I mean as you say  'ORCALE HFM' a Reporting and Analysis System than this can be used APIs and Data-Base . 

So , if Orcale HFM System used Apis . than is it Possible to Integrate API in PowerBI ?

[Eric_Zhang]

---

@rakeshsaini wrote:

Thanks @Eric_Zhang . 'ORACLE HFM' an Reporting System. So  Orcale HFM also used an Data Source . So we can used that Data Source with Power-BI.

I am not Clear , So I have both Options . I mean as you say  'ORCALE HFM' a Reporting and Analysis System than this can be used APIs and Data-Base . 

So , if Orcale HFM System used Apis . than is it Possible to Integrate API in PowerBI ?

---

@rakeshsaini

Usually, Power BI can most probably use the same APIs and datasource as HFM does.

[rakeshsaini]

@Eric_Zhang , please Clarify , i am not able to catch !!

[Eric_Zhang]

@rakeshsaini

May I know what APIs and datasource are used in your Oracle HFM then?

[rakeshsaini]

Actually @Eric_Zhang , right now i do not have any APIs and Data-Source name . but i can have one of them . So i am asking that if I have api than it is Possible to Integrate API  in Power BI . Beacuse as you mention , we can not used ORCALE HFM as Data-Source and API , and it used an Data Source . So we Can Connect this Source with PowerBI . but if ORCALE HFM used APIs that How can we do ?

[Eric_Zhang]

@rakeshsaini

Power BI also supports REST APIs, not sure what exact your API is.

Check a sample Get Data from Twitter API with Power Query, we can use Power Query in Power BI to do the same thing.

[rakeshsaini]

I found a soluition link ,but i think this is not right way to connect Oracle HFM to PowerBI .

http://poweronbi.com/2016/04/from-hyperion-planning-or-hyperion-financial-management-to-power-bi/

[rakeshsaini]

is it Possible to Integrate 'Oracle HFM' Data with PowerBI ??