Question

Needed architecture for IFS AND Power BI

  • 14 January 2021
  • 5 replies
  • 2395 views
N
Rank
Badge +2
  • NIB
  • Do Gooder (Customer)
  • 2 replies


Hello IFS community !


I'm looking for information about my following problem: 

My company uses the IFS ERP and would like to be able to connect a dashboard tool like POWERBI and I would like to get some advice on all the elements needed for the architecture. 

SQL SERVER  ?  SSIS ? IFS prepackaged analysis models ? IFS REPORTING & Analysis ? etc.. ?

 

Thanks

Kinds regards 
 

This topic has been closed for comments

5 replies

I
Rank
Userlevel 2
Badge +5

Hi NIB,

 

we actually also just started with some PowerBI testing.

 

During test phase we have used direct ODBC / JDBC connection to IFS Database. You can create a user using the IFS Client and assign the permission only to the data you want to share in PowerBI. Deactivate the user and unlock the Oracle account to save the IFS license. Or just create a user on the DB directly.

After development or testing phase you should have an SQL server were you push the data to. Otherwise PowerBI might produce unwanted load on IFS if the refresh rate of heavy queries is on a high frequency. We are using MS SQL Express, but you could use any DBMS of your choice, and are pushing the data with a scheduler in a Middleware Server from IFS to the SQL Express. PowerBI loads the data from this SQL Express and does not generate additional load on IFS.

Not saying this is best practice, but the structure we have currently running.

 

Edit:

To use the ODBC connection, you need to download the Oracle Instant Client and ODBC drivers supporting your Oracle DB version.

N
Rank
Badge +2

Hi ig_,

Thank you so much for your answer !

In order to have well understood, can you tell me if the architecture below is suitable :
IFS Application - Database
    ↓
    IFS Middleware Server (For planification and extract from IFS Database to SQL Server)
        ↓    
        MS SQL Express (With SSIS package) - It's my ETL for create OLAP Cube
          ↓ 
      POWER BI connected to the SQL Server 

In your opinion, it's ok ?

Moreover, do You know prepackage analysis model in IFS ? according to my research, it could help me a lot in my OLAP Cubes, if you can tell me more that would be great

Thanks

Kinds regards 

I
Rank
Userlevel 2
Badge +5

Hi NIB,

 

please note, that we are also in the very beginning of our tests. So I’m not talking with a lot of experience.

Basically you got my points. We are doing it in the same way, but do not use IFS Middleware Server, because we have another software in use where it’s easier to transfer data. Should be possible with IFS MWS too.

 

Let me tell you why we are using it like that: In PowerBI we have several queries calling the same data source (View A). Because each Dashboard can have its own scheduler, PowerBI is calling the same information over and over again in a short amount of time generating a significant load on the IFS DB. Let’s say you have 5 Dashboards with 1 minute refresh rate. That would mean you have 5 calls on the IFS DB for the same data source.

If you push the data to a separate SQL Server you can still have a refresh rate of 1 minute, but you would only have 1 single call per minute on the IFS DB as the load will be in the SQL Express.

 

I am not sure what you with your last question. Can you explain further?

 

Please also share your experience about the infrastructure and your approaches as it’s also relevant for our own project and maybe for other users of the community.

Thanks

N
Rank
Badge +2

Hi Ig,

Ok, I understand your approach, I am also at the start of the study, I'll try to keep you up to date on the possible infrastructures I'll try to set up.


Concerning IFS analysis model prepackage, it would allow for the transfer of data from IFS App to datawarehouse and hosted by MS SQL Server thus it will create a datamarts and OLAP Cubes predifined for 11 departments (I don't know which ones). After this, you can connect directly to it via Power BI.

I give you the links of the different documentations about it:

Analysis Model Prepackage Presentation :
https://docs.ifs.com/techdocs/Foundation1/010_overview/255_br_and_a/020_Analysis_Models/default.htm#Configuration_Tool  

Details of the package :
https://docs.ifs.com/techdocs/Foundation1/020_installation/400_installation_options/007_br_and_a/008_Analysis_Models/060_AnamodComponentStructureAndPackaging.htm

Analysis Models Setup :
https://docs.ifs.com/techdocs/Foundation1/020_installation/400_installation_options/007_br_and_a/008_Analysis_Models/

Analysis Mode Administration : 

https://docs.ifs.com/techdocs/Foundation1/040_administration/255_br_and_a/020_Analysis_Models/default.htm

 

Please, keep me up to date if you use it.

 

Thanks

Kinds regards

I
Rank
Userlevel 2
Badge +5

Hi NIB,

 

the links look interesting, was not aware about that until now.

 

There is actually already another topic in the community, where you also might find some help in setting up the data warehouse:

 

Terms & ConditionsCookie settings