How to create an on-premises Data Warehouse (DWH)?
In our in-house reports, we were pulling the tables from our database and processing them on the business intelligence tool. Inevitably, we had to repeat the calculations we made in our previous reports every time (profit, turnover, production amount, stock amount etc.)
For this reason, we created the columns and calculations that we classically use in our internal reports as fact, dimension and calculate tables on SSIS.
Data Warehouse (DWH) Stages
1-) Classical tables and necessary columns used in our reports are determined.
2-) After the columns are determined, the database is created.
3-) After the database is created, which tables are fact tables and which tables are calculation tables are determined in SSIS.
4-) After determining, the columns and calculations that are constantly needed are made. Accordingly, tables are created by creating flows in SSIS.
5-) In addition, there are tables with calculations based on this. These tables are also kept in a different service and used integrated in the reports.
As an example, the query that shows the reserve of the order quantities taken is like this and this refers to a table. This flow has also been created in the calculation service. Instead of calculating it every time, we can draw this data directly to our reports thanks to this table.
SELECT
[STOCK_ID],
[PRODUCT_ID],
SUM([QUANTITY] — [DELIVER_AMOUNT]) AS ‘Purchase_Order_Reserved’
FROM
[akkim].[dbo].[ORDER_ROW] ORDR
LEFT JOIN
[akkim].[dbo].[ORDERS] O ON ORDR.ORDER_ID = O.ORDER_ID
WHERE
[ORDER_ROW_CURRENCY] IN (-7,-6,-5,-4,-2,-1)
AND O.[PURCHASE_SALES] = 1
GROUP BY
[STOCK_ID],
[PRODUCT_ID]
As an example, the query that shows the reserve of the order quantities taken is like this and this refers to a table. This flow has also been created in the calculation service. Instead of calculating it every time, we can draw this data directly to our reports thanks to this table.
Finally, the services are deployed and transferred to SQL. By creating a job in SQL, we run the package containing the fact and dimension tables and the package containing the calculate tables in 2 steps in a single job. After the refresh times are adjusted, we now have a data warehouse that can be used.
We do not have to spend time on classical calculations and data used in reports every time.
