Is it possible to retrieve the entirety of data of an ERP System?

[Fatima-Zahrae]

Hello everyone,

My inquiry is more general in nature. I am interested in knowing whether it is feasible to retrieve the complete dataset of an ERP system, including all fields, in order to empower users to independently develop their applications. If this is indeed possible, I kindly request you to provide me with information regarding the methods that can be employed.

However, if retrieving the entire dataset is not possible, I would appreciate it if you could tell me about the reasons behind this limitation. Despite this constraint, I still need to explore alternatives that allow me to retrieve a portion of the data, prioritizing the inclusion of as much information as possible. In this regard, I would like to inquire about the approaches that can be used in Qlik Sense to achieve this objective.

Please any response is helpful!!

Thank you for your attention to this matter.

[Fatima-Zahrae]

Hello everyone,

My inquiry is more general in nature. I am interested in knowing whether it is feasible to retrieve the complete dataset of an ERP system, including all fields, in order to empower users to independently develop their applications. If this is indeed possible, I kindly request you to provide me with information regarding the methods that can be employed.

However, if retrieving the entire dataset is not possible, I would appreciate it if you could tell me about the reasons behind this limitation. Despite this constraint, I still need to explore alternatives that allow me to retrieve a portion of the data, prioritizing the inclusion of as much information as possible. In this regard, I would like to inquire about the approaches that can be used in Qlik Sense to achieve this objective.

Please any response is helpful!!

Thank you for your attention to this matter.

[Fatima-Zahrae]

Hello everyone,

My inquiry is more general in nature. I am interested in knowing whether it is feasible to retrieve the complete dataset of an ERP system, including all fields, in order to empower users to independently develop their applications. If this is indeed possible, I kindly request you to provide me with information regarding the methods that can be employed.

However, if retrieving the entire dataset is not possible, I would appreciate it if you could tell me about the reasons behind this limitation. Despite this constraint, I still need to explore alternatives that allow me to retrieve a portion of the data, prioritizing the inclusion of as much information as possible. In this regard, I would like to inquire about the approaches that can be used in Qlik Sense to achieve this objective.

Please any response is helpful!!

Thank you for your attention to this matter.

[Oleg_Troyansky]

Hi Fatima-Zahrae,

Generally speaking, you can load all of your data from the ERP system into Qlik. Depending on the database that's your ERP is running on, you should be able to arrange a loop over all the tables and load all of them, without specifying table names and field names. This is done with the use of ODBC function SQLTABLES. The whole process is described in detail in my book here.

Keep in mind a few important issues to keep in mind:

1. The size of your data. Some ERP systems contain hundreds or thousands of tables with hundreds of millions of transactions. Loading everything may turn out to be very heavy. You might be better off spending the time on understanding the business needs and only downloading relevant data.

2. The data modeling aspect. Transactional databases are way too normalized for efficient data analytics. Your business users most likely won't know how to arranges many transactional tables into an analytical data structure that works well for analytics. This issue is also described in detail in my book.

3. Synthetic keys and circular references. Qlik data models can only associate tables by a single key, and they don't allow circular references. Loading all ERP tables into one data model with no transformation will most likely result in many synthetic keys and circular references. So, your best bet is to store those database tables in QVD files and then includes some of those tables, very selectively, into particular analytical applications.

Bottom line - while it is possible to load all ERP tables at once, you will be better off if you can select specific tables and engage in some degree of data modeling to avoid some fundamental issues.

Cheers,