Sergiy Lunyakin Cloud BI with Azure Analysis Services

Sponsors

About me

Agenda • • • BI components and Azure Analysis Services in the cloud Performance levels Data sources and exploration Management and development

Traditional BI components and Azure Analysis Services SQL DW Functions Power BI

Analysis Services in the cloud • • Managed SSAS Tabular engine in the Azure Cloud (Paa. S) Supports 1200 and 1400 compatibility level models Pause and resume resources Scale Up/Down Secure - Org AAD authentication (no Live. Id) Firewall Cloud and On-Premise (over Gateway) data sources Familiar tools (SSDT, SSMS, Power. Shell)

Modern BI approach

Azure Analysis Services Architecture Cloud data sources SQL Database Cloud visualization tools Azure Analysis Services Other data sources Power BI Direct Query Cached Model SQL Data Warehouse Power BI Embedded (GA) Gateway On-premises data sources SQL Server Oracle, Teradata Authoring and development tools Other data sources On-premises visualization tools Visual Studio Excel Power BI Desktop Third party BI tools Direct Query Cached Model

Org AAD Authentication • Supports only Azure Active Directory organizational accounts • Supports AAD B 2 B (invite users from another AAD tenant) • Workaround for Live. Id • Create a new account in default AAD domain (*. onmicrosoft. com) • Set as Administrator during service provisioning

Performance levels LEVEL QPUS MEMORY (GB) SLA PRICE B 1 40 10 99. 9 $319. 92/mo; $0. 43/hr B 2 80 20 99. 9 $639. 84/mo; $0. 86/hr S 0 40 10 99. 9 $602. 64/mo; $0. 81/hr S 1 100 25 99. 9 $1, 510. 32/mo; $2. 03/hr S 2 200 50 99. 9 $3, 020. 64/mo; $4. 06/hr S 4 400 100 99. 9 $6, 033. 84/mo; $8. 11/hr S 8 320 200 99. 9 $7, 722. 72/mo; $10. 38/hr S 9 640 400 99. 9 $15, 445. 44/mo; $20. 76/hr Developer 20 3 None $98. 21/mo; $0. 132/hr

Query processing units (QPUs) • Unit of measure in Azure Analysis Services • Based on a set of typical analytical queries and processing commands run on Azure and determines how many transactions are completed per second under fully loaded conditions • 20 QPUs is about 1 pretty fast core

Demo

Data sources Cloud On-premises* SQL database SQL Server SQL Data Warehouse APS Oracle Database Teradata Database Azure Blob Storage – 1400 Comp level My. SQL Database Azure Data Lake Storage -1400 …… * On-premises data sources require On-premises Data Gateway installation https: //docs. microsoft. com/en-us/azure/analysis-services-gateway https: //docs. microsoft. com /en-us/azure/analysisservices/analysis-servicesdatasource

Unified Gateway

Explore your data and connection Cloud On-premises Power BI Service Power BI Desktop Power BI Embedded – coming soon Excel SSMS • Connection to asazure: //<region>. asazure. windows. net/<servername> • Used port 443 • Latest client providers are required

Explore your data and connection • SSMS - Enable Azure AD authentication for SSAS in SSMS 2016 § [HKEY_CURRENT_USERSoftwareMicrosoft SQL ServerMicrosoft Analysis ServicesSettings] "AS AAD Enabled"="True" • Excel – Windows Authentication is not supported yet, User/Password only • Power BI Service – connect from Power BI Desktop, publish to web. Connection from web is not supported yet • Impersonation § Cloud sources/On-Premises with SQL Authentication – Service Account. § On-Premises with Windows Authentication – User/Password, In-Memory Only

Management and Development for AAS • • Azure Portal Power. Shell • • SSMS SSDT+DAX Editor DAX Studio Tabular Editor Resume-Azure. Rm. Analysis. Services. Server Suspend-Azure. Rm. Analysis. Services. Server Get-Azure. Rm. Analysis. Services. Server Remove-Azure. Rm. Analysis. Services. Server Set-Azure. Rm. Analysis. Services. Server Test-Azure. Rm. Analysis. Services. Server New-Azure. Rm. Analysis. Services. Server Invoke-Process. ASDatabase

Management and Development for AAS • AAS Web Designer - Preview • • • Create a new data model (Azure SQL DB/DW) Edit existed model Create relationships and measures Git. Hub integration Import Power BI model • Backup and Restore from Blob Storage

Demo

Processing Cloud On-premises Azure Automation + Power. Shell SSMS Azure Data Factory – Custom Activity SSIS: SSAS DDL Task, SSAS OLEDB+SQL Task Azure Data Factory v 2 – SSIS package SSDT Azure functions +. Net - 5 min timeout Power. Shell: Invoke-Process. ASDatabase RESTful API

User management • • • Users must be in your Azure Active Directory. Usernames must be by organizational email address or UPN. Add users: • Role assignment in Azure AD • TMSL in SSMS • UI in SSMS

Demo

Troubleshooting and monitoring • Ensure that you use the latest drivers • Clear Azure AD cache • C: Users<user_name>App. DataLocal • Delete the AADCache. OM or. Identity. Service folder • x. Events Trace • Stream • Custom solution to file (Christian Wade)

Summary • • Paa. S Analysis Services Tabular engine Enterprise-grade data modeling in the cloud No CAPEX Low OPEX – pay for only what you use Go from conception to insight in hours Flexible scale Pausing and resuming

Links • https: //opbuildstorageprod. blob. core. windows. net/outputpdf-files/en-us/Azure. azure-documents/live/analysisservices. pdf • https: //azure. microsoft. com/enus/documentation/videos/index/ • https: //feedback. azure. com/forums/556165 -azure-analysisservices • https: //github. com/Microsoft/Analysis-Services