Excel and SSAS solution with Azure

2
votes

We have a basic BI solution using SQL Server Analysis Services (SSAS) on top of a SQL Server Database updated by a standard 'line-of-business' systems.

Desktop Excel is used to connect to the Data Cubes and tandard Pivot table functionality used to create reports, run ad-hoc queries etc.

Question: Is there a way to move this partially or fully to Azure? What would be the best architecture? e.g.

  • Can the application uses an azure-based SQL Server database as if it was a local database.

  • Can the data from this be ETL'ed using SSIS into SSAS all in the cloud

  • Can users access these cloud-based data cubes either via their desktop Excel or else via Excel 365 or some such (MS seem to have some product called Power BI which may be involved)

  • Is the cost of moving and processing fairly large amounts of data (500k+ records per day) prohibitive?

2
excelazuressas
Just a partial answer: Moving data to Azure is free, you only pay per data you get from Azure. – FrankPl
thanks for coming back, looked more into this a bit more and as SSAS needs windows authentication think we have to use an Azure Virtual Machine which is coming in at around €800 per month for one with the required software – Niall

2 Answers

0
votes

This is an old question, but it's worth noting that there is now a cloud native Azure offering of Analysis Services which will work out cheaper than VM + SQL Server licence.

See https://docs.microsoft.com/en-gb/azure/analysis-services/analysis-services-overview

0
votes

I had the same problem so I created a database in azure as "data warehouse" and upload the data in a daily basis. Then I use Analysis Services in azure with the data source "data warehouse" to create the models, now users can connect to the models directly to the cubes using excel or Power-bi from the azure portal. The solution is not that expensive, the most expensive is the Analysis services, and you can change the tiers as you need.