Lets Build a Tabular Model in Azure Analysis
Let’s Build a Tabular Model in Azure Analysis Services is in Azure? Seriously!? Let's build a model! 2019 -03 -23 Phillip Labry
SQL Saturday #825 • Thank you Sponsors! • Please visit the sponsors and enter their end-of-day raffles. • Event After Party • After party is at Bar Louie in Naperville • Want More Free Training? • https: //chicago. pass. org/ 2019 -03 -23 | Phillip Labry | Azure Analysis Services
Phillip Labry v v Solution Architect IT development for over 25 years Developer, DBA, BI Architect, Consultant Experience with Manufacturing, Telecom, Banking, Retail, Government, Insurance, Healthcare, Consulting, Energy, Finance Twitter: @phillip. Labry Blog: http: //phillip. labry. com 2019 -03 -23 | Phillip Labry | Azure Analysis Services
Today’s scenario 2019 -03 -23 | Phillip Labry | Azure Analysis Services
Today’s scenario 2019 -03 -23 | Phillip Labry | Azure Analysis Services
Basic Terms Measure Numeric value that can be aggregated (Sales Amount) Fact Collection of fields mainly consisting of Measures Dimension Table of values that describes a fact (people, places, things) Star Schema Dimension tables radiating out from a related fact table Snowflake Schema Dimensions related to other dimensions Aggregate A mathematical summarization of measures Attribute Another name for Column(used in Dimensions) 2019 -03 -23 | Phillip Labry | Azure Analysis Services
What is Analysis Services? Analytical database designed to be business facing Optimized for aggregating huge data sets Two flavors: OLAP and Tabular Two Query languages: MDX and DAX 2019 -03 -23 | Phillip Labry | Azure Analysis Services
Benefits of Azure Analysis Services • Scale up, down, in, out and pause • No hardware required • Inherent redundancy • Explore data from anywhere • Use the tools you love and know • SSMS, SSDT, Power BI, Excel 2019 -03 -23 | Phillip Labry | Azure Analysis Services
Why Azure Analysis Services? • Upsize from Power BI • Faster data refreshes • Adjust for peak workloads • New features quicker • Save on hardware costs, IT infrastructure • Data Security 2019 -03 -23 | Phillip Labry | Azure Analysis Services
Model Types • In Memory • Direct Query 2019 -03 -23 | Phillip Labry | Azure Analysis Services
Supported data sources – On Premises In Memory and Direct Query • • SQL Server PDW / APS Oracle Teradata 2019 -03 -23 | Phillip Labry | Azure Analysis Services
Supported data sources – On Premises In Memory only Access Database Exchange OData Feed SAP HANA Active Directory Folder ODBC query SAP Business Warehouse Analysis Services IBM Informix OLE DB Share. Point Analytics Platform System JSON document Postgre SQL Database Sybase Database Dynamics CRM Lines from binary Salesforce Objects XML table Excel workbook My. SQL Database Salesforce Reports 2019 -03 -23 | Phillip Labry | Azure Analysis Services
Supported data sources - Azure Datasource In-memory Direct. Query Azure SQL Database Yes Azure SQL Data Warehouse Yes Azure Blob Storage Yes No Azure Table Storage Yes No Azure Cosmos DB Yes No Azure Data Lake Store Yes No Azure HDInsight HDFS Yes No Azure HDInsight Spark Yes No 2019 -03 -23 | Phillip Labry | Azure Analysis Services
BI Semantic Model: Vision (2012) Third-party applications Reporting Services Power View Excel Power. Pivot Share. Point Insights BI Semantic Model Databases LOB Applications Files OData Feeds Cloud Services
BI Semantic Model (Azure Analysis Services) 2019 -03 -23 | Phillip Labry | Azure Analysis Services
For Development v. DO NOT CHOOSE PRODUCTION SERVER FOR WORKSPACE v. Use Developer Tier pricing v. Remember to pause your machines 2019 -03 -23 | Phillip Labry | Azure Analysis Services
Dimensions v. Wide and shallow v. Describe facts v. Can contain hierarchies v. Can contain calculated columns 2019 -03 -23 | Phillip Labry | Azure Analysis Services
Hierarchies v. Predefine common hierarchies for the users v. Hierarchies are defined from largest group to smallest v. Year v. Quarter v. Month v. Hide columns used for hierarchies where appropriate 2019 -03 -23 | Phillip Labry | Azure Analysis Services
Fact Tables v. Deep and narrow v. Mostly measures(Numbers) v. Keys to dimensions(Ints) v. Natural repository for calculated measures 2019 -03 -23 | Phillip Labry | Azure Analysis Services
Calculated columns and Measures • Created in the model only • Calculated measures execute when called based on filter context • Calculated columns are created on data load and persist in memory 2019 -03 -23 | Phillip Labry | Azure Analysis Services
Requirements • Azure account • Azure Active Directory Tenant(MS live accounts are not supported) • Directory integration between AAD and on premises AD is recommended but not required • Resource Group • Create a server 2019 -03 -23 | Phillip Labry | Azure Analysis Services
Demo 2019 -03 -23 | Phillip Labry | Azure Analysis Services
Current Pricing QPUS MEMORY (GB) Hourly Price Monthly Price B 1 40 10 $0. 43/hr ~$313. 90 B 2 80 20 $0. 86/hr ~$627. 80 Developer 10 3 $0. 132/hr ~$96. 36 2019 -03 -23 | Phillip Labry | Azure Analysis Services
Current Pricing QPUS MEMORY (GB) Hourly Price Monthly Price S 0 40 10$0. 81/hr ~$591. 30 S 1 100 25$2. 03/hr ~$1, 481. 90 S 2 200 50$4. 06/hr ~$2, 963. 80 S 4 400 100$8. 11/hr ~5, 920. 30 S 8 320 200 S 9 640 400 2019 -03 -23 | Phillip Labry | Azure Analysis Services
Tips for development v. Clean table names on first import v. Settle on column names before creating any calculated columns or measures v. Flatten out snowflakes where possible v. Avoid creating calculated columns for intermediate measures v. Use views for source data v. Use meaningful and verbose names v. Use attribute properties and formatting 2019 -03 -23 | Phillip Labry | Azure Analysis Services
Current Azure Analysis Services challenges • • Preview only Backups Limited source data No Power BI connectivity 2019 -03 -23 | Phillip Labry | Azure Analysis Services
Additional resources https: //www. sqlbi. com/ https: //blog. pragmaticworks. com/ https: //blog. crossjoin. co. uk/ https: //docs. microsoft. com/en-us/azure/analysis-services 2019 -03 -23 | Phillip Labry | Azure Analysis Services
Thank you @Phillip. Labry @Redshirt. Data 2019 -03 -23 | Phillip Labry | Azure Analysis Services
- Slides: 30