Why do we need to store data?

Question

I was wondering why and when we need a data warehouse, I mean that the main task of a data warehouse is to provide reports from a multidimensional view, but in some cases there is a way to create a report using a DBMS, this can create a report with a multidimensional view, size databases, size can be done gradually, and DBMS integration can also be integrated from many data sources, and if they have a different key from many data sources, dbms can fix it (although we don’t use Data Warehouse), in terms of efficiency, the time required to create a report from a repository of operational data and create reports from the data warehouse, whether during processing large space?

Sorry for the poor English and weird question, I am still learning about the data warehouse.

Answer 1

Why do we need a data warehouse?

I will share my experience with me and the company I'm working on, how and when we decided to create a data warehouse.

• Historical data

  Our ERP server is designed to cover one year of data (due to the large amount of data). All predefined reports are created to view data for one year. So, if you want to study the sales growth for each client or product, you need to run the report twice (from two sources - you select the source on the login page) and the search values ​​in excel.

• Multiple sources

  At some point, we bought a third-party custom solution (tablet order → web service → database) to improve the business. The ERP provider got us the luck to "integrate it into the system."

• Bad data

  Our CFO and his team needed a whole week to create a good P'n'L report. What for? The analytical data was wrong, so they need to go line by line in line (in excel export) and look for deviations.

• Report Performance

  Sometimes it took more than two hours to generate an annual sales report, grouped by product, customer and day.

• Adding Grouping

  When management decides to separate customers or products by other criteria, we must pay ERP providers and wait 1-3 weeks. And guess what the sales manager gives, and the new one asks for new types of groupings. It costs (time and money).

What to do? To change the ERP system or create a data warehouse? We spend months and months to create a custom system from our standard product. We spent money and money to pay extra hours. We have over 250 employers who teach how to use the new system. We must wait at least one year for the new ERP provider to develop our own needs.

Then we bought a company that is a competitor. We wanted to track our progress against their sales value. We decided to create a data warehouse!

And we made life easier. After that, we bought a warehouse management system, a geological tracking management system , another company and guess what? We have the data that we want, in the form in which we want, with great scalability, so as not to give money to the ERP provider.

Now our data warehouse provides:

• The historical data of our company and two other companies with perfect key mapping.
• Several sources, our current data, our historical data, acquired historical data of companies, WMS, TMS, ordering system and Excel spreadsheets (KPI, goals, evaluation, ect).
• We have created a rule table, so CFO and his team should only administer the rule table, we always get good data, and incorrect data is automatically reported (on a daily basis) to a specific department in order to change it.
• Oh, managers just love OLAP. He flashes quickly.
• Using Excel spreadsheets as one of the sources, we can easily match products or customers if management wants a new grouping.

Answer 2

• Aggregation: by month, by product, etc. Obviously, we can do this right away in Transactional DB (using SQL query, calculation, etc.), but why do we avoid it?
  • We do not want to influence the transactional database!
  • We want to separate non-transactional users from transactional users.
  • We want to display reports. To display the report, we need to perform a complex calculation, so we do not want to harm the transaction database during the processing of reports.
• Historical data: The transactional database is intended only for current / transactional data, old data will / can be archived. But can we query and combine both archived data and transactional data?
  • But where is the data stored? How do we get this data? How can we combine at runtime? This is a headache!
• Combining several sources of data: we have several sources of our business, the report should display the combined information from all sources. Therefore, we need a place to store this data, so we need a data warehouse.
• We have data from the transactional database, we are going to use this data for reporting. But what if this data is poorly formatted, what if this data is NULL (but the business rule is NOT NULL), what if this data is incorrect, and more and more. This is why we need to convert the data to the correct format, the right business rule, etc. But after that, where do we want to store it? This is when the data warehouse falls into place.