Serverless BI with Azure Data Services and Power

Server-less BI with Azure Data Services and Power BI Andrew Kinnier – Data Ignite, LLC Don Day – Reg. O Power. BIWorld. Tour. com #PBIWorld. Tour

What we’ll learn today • You’ll learn how a business can implement a state of the art Business Intelligence environment by subscribing to and setting up Azure Data Services and Power BI • You’ll see presentations and demos of the technologies used and how they work together • Don Day of Reg. O will show the power and flexibility of Power BI connected to an Azure Analysis Services cube. Power. BIWorld. Tour. com #PBIWorld. Tour

Session Agenda • Start with a Real World Use Case – Reg. O • What was their existing environment and why did they make the move to Azure Data Services and Power BI? • What does Server-Less or Subscription based services mean? Why would we want to do this? • Cover the data services used in our case study • Show Power BI connected to the data services • Question and Answer Power. BIWorld. Tour. com #PBIWorld. Tour

Presenter Information • Andrew Kinnier – Data Warehouse and Business Intelligence Architect • Partner and Solutions Architect at Data Ignite, LLC (dataignite. com) • Assistant Coordinator for NY/NJ Power BI User Group Power. BIWorld. Tour. com #PBIWorld. Tour

Presenter Information • Don Day • IT Manager • Reg. O Power. BIWorld. Tour. com #PBIWorld. Tour

Use Case – Reg. O Power. BIWorld. Tour. com #PBIWorld. Tour

Reg. O® is the leading global provider of highlyengineered, mission-critical flow control and safety products used in the liquefied petroleum gas (LPG), liquefied natural gas (LNG), cryogenic and other industrial gas (IG) end markets. The company was founded in 1907 as Bastian Blessing Company initially selling oxygen regulators. Today Reg. O designs and manufactures a comprehensive range of flow control valves and regulators that are sold to domestic and international markets under the Reg. O and Superior Products® brand names. Power. BIWorld. Tour. com #PBIWorld. Tour

• Global Presence • State-of-the-art manufacturing facilities are located in the U. S. , while international facilities focus on sales, distribution and light assembly. Power. BIWorld. Tour. com #PBIWorld. Tour

Data Warehouse and Reporting - Former State • Original Data Warehouse built by employee no longer with company. • Hosted on an on-premise server • Pulls data from all ERP systems in global organization • Huge step forward • Allowed for limited querying/pivoting with Excel • Contained global booking/sales data and limited operations data • Extensive use of scripting-difficult to maintain/troubleshoot • Not built using industry best practices • Not intuitive for end-user (had to be familiar with pivot tables) Power. BIWorld. Tour. com #PBIWorld. Tour

Azure Data Services Power. BIWorld. Tour. com #PBIWorld. Tour

Power. BIWorld. Tour. com #PBIWorld. Tour

Architecture Decisions for Reg. O Products • Data not very large – products are expensive, thus the number of sales records are comparatively small • Social Media is in play, however Reg. O is not streaming thousands of tweets per day. Social Media doesn’t necessitate heavy analytics at this point • ADF V 2 has recently been released. Architectural decisions were chosen in October of last year. ADF V 1 • Most data is sourced from database. 5 Major Sites: US (NC), US (Superior), Mexico, Germany, Asia. All have a different ERP system. • Power BI Service to replace Excel spreadsheet Power. BIWorld. Tour. com #PBIWorld. Tour

Services used for Reg. O • Azure Data Factory – Move data from databases and Share. Point Lists • Azure SQL Database – Raw, Archive and Serving layers, no Azure Data Lake at this time • Serving layer is Kimball Method Data Warehouse • Azure Analysis Services – SSAS Cube in the cloud • Power BI Service – Reports/Dashboards use Cube as source • Azure Automation – Schedules Data Warehouse processes Power. BIWorld. Tour. com #PBIWorld. Tour

Other Azure Data Services Power. BIWorld. Tour. com #PBIWorld. Tour

Tools not in this Use Case • Azure Stream Analytics • Stream live data directly into Power BI and Azure Data Storage • Azure Data Lake Analytics • Processes, refines unstructured and semi-structured data • Azure Data Warehouse • Massively Parallel Processing – scalable architecture to handle large data warehouse querying • HDInsight • Hadoop on Azure Power. BIWorld. Tour. com #PBIWorld. Tour

Azure Data Factory Power. BIWorld. Tour. com #PBIWorld. Tour

Azure Data Factory • Needs Azure Gateway on source systems – secure data from source to cloud • Destination is a SQL Stored Procedure which processes both Raw and Archive zones • Demo the ADF portal • Show Stored Proc, Raw and Archive tables Power. BIWorld. Tour. com #PBIWorld. Tour

Azure SQL Database Power. BIWorld. Tour. com #PBIWorld. Tour

Azure SQL Database • Raw, Archive and Data Warehouse • Kimball Method Data Warehouse • Type 1, Type 2 Dimensions • Numerous Fact Tables (Business Processes) with shared dimensions • Show SQL Database Portal Power. BIWorld. Tour. com #PBIWorld. Tour

Azure Analysis Services Power. BIWorld. Tour. com #PBIWorld. Tour

Azure Analysis Services • Cube Service in the cloud • Combine data from multiple sources into a single, trusted BI semantic model that’s easy to understand use. Enable self-service and data discovery for business users by simplifying the view of data and its underlying structure. • In-memory and compressed data – very fast query performance • Show Semantic Layer – Visual Studio Power. BIWorld. Tour. com #PBIWorld. Tour

Azure Automation Power. BIWorld. Tour. com #PBIWorld. Tour

Azure Automation • Allows scheduling of tasks • Use case – schedules Dimensions, Facts, Validation and Cube Processing • Checkpoints and Alerting • Demo the Azure Automation portal Power. BIWorld. Tour. com #PBIWorld. Tour

Power BI Service Power. BIWorld. Tour. com #PBIWorld. Tour

Data Warehouse and Reporting-New Functionality New data warehouse developed with outside vendor Hosted on Azure server in the cloud Integrated with Share. Point/Office 365 Uses many of the design aspects of the current data warehouse Uses Microsoft Power BI as design tool for reports/visuals/etc Allows for drill down/drillthrough in Power BI reports Allows for data download analysis in Excel Contains data from sales, operations, quality, procurement and other business areas. • Built using industry best practices • Securely accessible anywhere with internet access (including phone!) • • Power. BIWorld. Tour. com #PBIWorld. Tour