[greggyb]

This is not, as billed, about building a custom analytics solution.

This is SQL as BI, which is great for small technical teams, but hardly a good or scalable solution.

If anything, this is a vague argument to have a data warehouse. Data warehousing is only an intermediate step in any analytics or BI solution and the hard problems are not in moving data or writing one-off queries.

[itamarwe]

What kind of solution would you suggest? What is missing?

[greggyb]

BI or an analytics solution would be more all-encompassing than an ETL tool (Alooma) and a database platform (Redshift).

ETL is much more than just moving data around. I haven't dug too deeply into Alooma's offerings, but it does appear to have workflows for more than just data from system A to destination B. Some of the important things to look at in ETL are change-tracking for slowly changing dimensions, business logic transformations, and mashing up data from disparate sources against conformed dimensions. Master Data Management is its own practice, but is intimately involved in ETL. Some ETL tools include Alooma, SSIS, Kettle.

Beyond ETL there is the data warehouse platform which usually consists of a RDBMS, sometimes with an OLAP layer as well. OLAP is distinguished from something like Redshift, which is a columnstore RDBMS, by including a metadata layer with the data. MDX is probably the granddaddy of OLAP languages. Think something that a pivot table can speak to directly. The data warehouse can really be any RDBMS product.

At the data warehouse layer, there's much more than just flattened source tables. A good deal of effort needs to go into the dimensional modelling for end-user consumption and reporting. We could take an aside for Inmon vs Kimball here, but it's spurious, because Inmon recommends dimensional data marts for end user consumption, just like Kimball - the data warehouse is never exposed in the Inmon methodology and dimensional data modelling is required for either.

If we are utilizing an OLAP engine, then there is a lot of measure definition to be done, encapsulating a lot of the logic that is displayed in the single-purpose queries of the article's examples. The data model will be based on the dimensional model from the data warehouse, but there's a lot more metadata in an OLAP database, which helps make self-service reporting much easier. Many more people are comfortable building a pivot table than even a simple SELECT statement in SQL. Some OLAP engines are SSAS and Mondrian.

Regardless of whether we have an OLAP solution, we need a presentation layer (typically several, as there are different types of reporting needs, and different strata of sophistication among end users). This is something like Tableau, Qlik, Quicksight, Cognos, Power BI (all prior products purport to cover varying degrees of the data ETL and modelling process in addition to providing a presentation layer), Crystal Reports, and SSRS are just some samples of many in the commercial space (I'm not as familiar with open source presentation layers).

This is just a high level overview of the major components of a BI or analytics solution, and fairly barebones. The article spoke to ETL a little, and at best hit tangentially on data warehousing and presentation.