Moving BI to the cloud: Consider this…

Home - Azure - 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]https://azure.microsoft.com/en-us/pricing/details/sql-data-warehouse/[/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]https://azure.microsoft.com/en-us/pricing/details/storage/[/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.

Latest Posts

Categories