Normally when an SMB hears a phrase like ‘Data Warehouse’ the first thing that comes to mind is prohibitive costs. Also, there are horror stories about the implementation process due to the rigidity of the data modeling, which is measured in months, not days.
What makes a data warehouse the preferred choice for enterprises is that the data warehouse:
- Provides a solid core to complex data models – meaning that whatever data you want to add to DW it will work and provide a single repository for all BI needs in your organization.
- Supports larger sets of data – speaking about hundreds of millions of rows or terabytes of data is business as usual.
- The data warehouse enables data to be interrogated quickly and efficiently and does not sacrifice any feature as with Power BI.
- In addition, it allows some special functionality like snapshots of the data to be recorded allowing for additional analysis of historical data that would otherwise not be retained by the ERP solution.
Advantages
DW is the central repositories of integrated data from one or more data sources. DW can store current and historical data in one single place. Corporate BI projects are a playground where SQL server engines (DB, SSAS, SSRS, and SSIS) do an excellent job of creating a corporate DW. DW solution can deliver:
- Larger data model with many contributing entities (source tables).
- Complex data model where data have to be transformed in a specific way (there is a lot that SQL can do and Power BI can’t).
- Larger data size with many rows in the source or target tables.
Threats
- In most cases (but not necessary) data from DW are further processed into cubes, where aggregations are for faster browsing. This two-step transformation adds new features and complexity to the BI project.
- Requires a DW specialist.
- Projects are expensive.
