Moving BI to the cloud: Consider this...

Azure / Power BI / SQL Server / SSAS

Moving BI to the cloud: Consider this...

While looking into the possibilities of moving my customer's BI infrastructure into the cloud, my primary question was how to handle the lack of cloud SSAS. But there's a lot more to keep in mind. Here's a few things that I think should be thought of from an architectural point of view: not only in designing your EDW architecture, but also in designing a way of work. I've summarized them below:

  • SQL Datawarehouse:
    • has no options for RLS. I question whether this functionality will be added at all (reasons: see below)
    • is not cheap. 100 DWU's will cost you a little above € 0.66hr at the time of writing, almost € 500/month. The default option, 400 DWU's, are €2.64/hr (~€1,970/mo)[ref][/ref].
    • can be paused, in which case you only pay storage (which is cheap: at the moment of writing ~ €0.0405/GB/month for geo-redundant storage < 1 TB[ref][/ref])
      • This affects your ETL: spin up SQL DWH for ETL/ELT, load DWH, cubes and Datamarts, pause.
      • Take this upspin / pause proces into account when designing the ETL/ELT processes
    • The pausing options reminds of HDInsight: quite expensive if you let it run, but you can spin it up / tear it down within minutes[ref]This isn't the only thing that reminds me of Hadoop. Think also of separation of data and calculation nodes, location-aware storage[/ref]
    • As a result, I'm inclined to see SQL Datawarehouse as a batch processing warehouse for huge datawarehousing loads - not as the default go-to for interactive querying
  • SQL Database:
    • Has RLS
      • Needs Premium edition for RLS. Premium starts at  almost  € 400 / month at the time of writing -> is available in Standard tier as well as Premium.
    • However, RLS is AFAIK currently not usable from within Power BI (not yet been able to verify this)
    • Can be used instead of SQL Datawarehouse if your model is really small and you don't need RLS
  • Power BI:
    • Every user has 10 GB of workspace, but a single model is limited to 250 MB
    • As soon as you use Direct Connect, you cannot add a semantic model anymore. It's either semantic model (add columns, measures, calculations, etc.) or Direct Connect
      • Direct Connect, Direct Query and Live Connections refer to the same things
    • Has RLS support since March 30th 2016
  • Overall cost:
    • Don't let the prices mentioned above fool you: for many, it'll still be cheaper to move to the cloud, even if initial costs may seem higher than your current operational costs. Think about:
      • the 'hidden costs' of staying up-to-date, upgrading your database every two years and so on
      • the continuous growth of operations (do your current systems really match the growing business need?)
      • compare apples to apples: include
        • SLA level
        • geo-redundancy
        • if you need to convince your finance manager: less working capital needed (no investment on iron, everything is "rented")
        • etc.

Comments (2)

  1. Paul Mooij

    Hi Koos,
    Actually my client's BI solution is running on top of SQL Database Standard tier and RLS is working fine here - on database level.
    As the PowerBI credentials are not propagated to the database, unfortunately this could not (yet) be leveraged to achieve RLS in PowerBI indeed, but I'm looking forward to do so.

    The release cycle of SQL Database is quite impressive and functionality is nearly on par with SQL 16. Without any hustle it just keeps dripping in. Compression, Partitioning, JSON, RLS, CLR, Query-Store...
    Furthermore point-in-time backups are available out-of-the-box, although this comes with the pain of the transaction log.
    Scaling up and down just for heavy-loads is awesome.
    IP-rules restrict access and Alert-rules notify on excessive resource consumption.

    Overall we're really happy with the SQL Database + PowerBI solution, using 'as a service' components really helps us focusing on delivering business value - each sprint.

  2. You're right - it is available at the standard tier too. Don't know how I came to believe it wasn't, but I was wrong anyway :). Updated post accordingly.

Comments are closed.