Data Warehousing, BI and Data Science

15 February 2014

Building a Data Warehouse and BI on Low Budget

Filed under: Business Intelligence,Data Warehousing — Vincent Rainardi @ 11:34 am

The core activities in building a data warehouse (DW) and BI are:

  1. Design the dimensional DW (fact and dimension tables)
  2. Identify the data source for each column in the DW
  3. Build the ETL to populate the DW
  4. 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.

Advertisements

4 Comments »

  1. Hi! Just one question about the above estimates. The cost for a 1 year FTE is really that low? ~£30k/year for a DWBI dev? I assume you are pricing for the UK market.

    Comment by Mihai — 16 February 2014 @ 7:13 am | Reply

    • Yes it is for UK market Mihai. Microsoft BI Developer with 2 years experience is between £25k and £35k (350-450/day for contract), depending on city and sector. The low end of the market is healthcare and retail, in the north east. The high end is investment banking in the City. A SQL Server Developer or SSAS Developer is slightly higher SSIS or SSRS Developer. Cognos or BO Developer and Informatica Developers are higher, about £40-50k for 2 years experience. For 5-10 years experience the rate is a lot higher. A Microsoft BI Developer with 8 years experience in investment banking or asset management in the City (it’s a name of an area in central London) is about £70-80k (£550-600). BO, Cognos and Informatica is about the same, £70-80k. The best information for UK market rate is http://www.itjobswatch.co.uk and http://www.jobserve.com. For a comparison, http://www.cwjobs.co.uk, http://www.jobsite.co.uk and http://www.efinancialcareers.co.uk are useful.

      Comment by Vincent Rainardi — 16 February 2014 @ 8:35 am | Reply

  2. Hi, Can i make one small Data warehouse like with 1 TB to 5 TB Disk Space? How much will it cost me? I don’t want to buy these costly software. I’ll develop one application that will allow users to login and work in my server. I want data-warehouse with only 5TB with at least 250 users. How much will it cost me. My market is gulf countries.. Can you suggest me any company who can offer me 5TB disk space on cheap rates?

    Comment by Mohammed — 5 April 2015 @ 6:02 pm | Reply

    • Hi Mohammed, I can’t suggest a company as I don’t know the gulf market. I would suggest Azure but it can only do 500 GB database. So it would have to be local servers. It would be something like 2 servers (Dev+Test and Prod) @ 2 CPU 16 cores 128 GB RAM (something like PE R730), with 20 TB usable capacity RAID storage (e.g. PowerVault MD3200i) which is probably in the region of $40k, including rack enclosure, power, Windows 2012 R2 licence, Standard SQL Server Licence, and installation cost. Developing the data warehouse in-house in Dubai, assuming AED 150k annual salary (=$40k, from http://www.payscale.com/research/AE/Job=Software_Developer/Salary), a team of 3 for 6 months will cost $60k. Total of USD 100k or AED 367k. This is excluding BI software such as QlikView, which could be very expensive for 250 users (EUR 1k/user) + EUR 25k for the server licence, http://www.covaligroup.com/Qlikview-Euro-Pricing/.

      Comment by Vincent Rainardi — 6 April 2015 @ 8:20 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: