The core activities in building a data warehouse (DW) and BI are:
- Design the dimensional DW (fact and dimension tables)
- Identify the data source for each column in the DW
- Build the ETL to populate the DW
- Build a cube on top of the DW
A lot of companies want to know how to build a DWBI on small budget. A medium size DWBI perhaps cost about £400k to build: £100k on hardware, £100k on software, £200k on labour. Many companies don’t have £400k. They want to spend £25k for POC, then another £75k to make the real one. Some even only have £50k.
Building a DW + BI with £400k
The £400k DW is as follows. The £100k hardware is probably something like this:
- 4 DW servers 32 GB RAM (Dev, UAT, Staging/DR, Production), 6 cores. Blade or rack with cabinet. Dev & UAT could be 16 GB 4 cores, or VM.
- 5-10 TB disk storage (1-2 TB per environment), RAID 5, 146 GB 15k rpm, e.g. NAS or SAN including enclosure.
- 2 ETL servers 16 GB RAM, 4 cores (1 for DEV+UAT, 1 for Production + Staging/DR)
- 2 Reporting & OLAP servers 16 GB RAM
The £100k software is to purchase ETL and BI software, such as Informatica, QlikView or Cognos, and the database software, such as SQL Server or Oracle. Also OS e.g. Windows Server 2012, Spotlite and VMWare, team collaboration tools e.g. TFS, and other software such as JIRA, Beyond Compare, Visio, Office, MS Project, SnagIt and RedGate.
The £200k labour is for paying 5-6 FTE for 1 year: 1 DW architect & data modeller, 1 BA, 1 ETL Developer, 1 Report Developer, 1 tester, ½ PM, ½ Infra.
Building a DW and BI with £50k
To get this down to £50k we need to minimize all 3 fronts: we need to use only 2 servers. One for Dev + UAT, 1 for Prod. The server will be installed with everything: ETL, BI software and RDBMS. We won’t have DR. This could get the H/W and S/W cost down to 20k including storage and OS.
The remaining £30k is to pay 1 FTE for 1 year who does everything from requirement analysis, design, build, test, manage. From data models, create databases and tables on the database server, stored procedure, ETL packages, reports, and analytic cubes. It will be difficult to find this 1 person because the variety of skills required probably won’t exist in 1 person.
With 1 server functioning as both RDBMS and ETL and Reporting and Analytics, it means that we can only have a small DWBI system. Say 1 TB total net storage, which means that the DW size is probably about 100 GB max, including Staging tables.
We may able to spend £3k on software, but not more than that. SQL Server is about £3k for 2012 Standard edition for 10 users (link). And that includes RDMBS, Reporting and Analytics. Oracle BI Standard Edition is $460 (about £345 including VAT, link) per named user, so 10 users is about £3450. You would probably need to consider open source BI and databases such as MySQL, JasperSoft and Pentaho
I think it is possible to build a DW with £50k, but it has to be a small, simple DW, so that a) the small infra can host it, and b) a single developer can build it within 1 year. Simple, i.e. type 1 dimensions, no accumulative snapshot fact table (only transactional or periodic)
Building a DW and BI with £100k
With £100k we can definitely buy Microsoft BI (£3k for 10 users), or Oracle BI (£3.5k for 10 users), but not Cognos or Informatica. Cognos 10 BI for 10 users is about £30k (link). This is just Analysis Studio + Report Studio (assuming the same price) and 1 Admin pack, including VAT. BO Edge is about £27k (link). That’s for 5 users and 1 report developer. Informatica PowerCenter starts at $140k (link).
The allocation of £100k is probably something like: £30k hardware, £10k software, £60k labour. To me this sounds right for a small company. The £60k is for 2 developers for a year, with 1 slightly more senior (say £25k – £35k split). With 2 developers we can be built a lot more. The first version can be release after 6 months, and we have another 6 months for enhancing it to the users’ needs.
The £30k hardware will buy us 2 servers with ok-ish spec (something like 4 cores, 32 GB) with good amount of storage, probably about 5 TB. We won’t be able to effort SAN, but we can effort RAID 5 on DAS.
Building a DW and BI with less than £50k
If the budget is below £50k, or the time frame is less than 6 months, I would recommend not to build a proper data warehouse. We can still deliver BI without having a DW. One way is using data virtualisation such as Composite. Another way is using QlikView, Tableau, or Spotfire which reads the data sources directly. From cost, the latter is lower than the former.
You can attempt to build a DW (say MS BI) but not proper Kimball DW. I call this Pseudo Dimensional DW. It is fact and dim tables, but no surrogate key. The dimension tables have natural key as their PK. This is a lot simpler and faster to build than a proper Kimball DW. For a small scale (5 dims + 2 facts), instead of taking 3 months, a Pseudo DW could be done in 1 month.