Data Warehouse Architect/MVP at Data on the Geaux
Thomas LeBlanc (Microsoft Data Platform MVP) is a Data Warehouse Architect in Baton Rouge, and uses his 28+ years in IT to develop OLTP systems with normalized databases for high-performing T-SQL and dimensional data marts using SSIS, SSAS, SSRS, Power BI, and Excel. As a PASS volunteer, he is past chair of Excel BI and Data Arch VCs and is active in the Baton Rouge UG and SQLSaturday. Blog - TheSmilingDBA.BlogSpot.com and Thomas-LeBlanc.com
So, what’s going in Analytics with on premise SQL Server 2017?
This session will go thru the on premise features around SQL Server 2017. This is not just Business Intelligence features like Analysis Services (SSAS) and Integration Services (SSIS). New features and improvements are in the Database Engine as well as Master Data Services.
Some features you might think are only in the Enterprise Edition are now in Standard editions. There are also 2 statistical languages in the engine - R and Python.
The improvements to Tabular Models in SSAS help get the product to the level of Cubes. Reporting Services also has Power BI integrated with the Portal along with KPIs and Mobile Reporting.
Microsoft show they are fully committed to on premise while developing first in the cloud.
So, what is all the excitement about with Power BI. How do I use it? How do I get started? What are the options for Azure versus on premise? If you want answers to some of these questions, this session is for you. We will start with an explanation of Power BI, but the rest of the session will involve demonstrating Power BI in action.
The Data Source and Data Set is where we start. The progression of the demos will include data cleansing and then data modeling. Relationships and measures will be created with a path to visualization. The scorecard like report will include examples of canned and custom visualization as well as a demo of R integration.
We will end with deployment paths for azure and on premise reporting services. The releases are about every month for azure and about 3 times a year for on premise, so some planning has to be considered for implementing new features.
Analysis Services Multidimensional Mode has an application for data mining with many algorithms. These include decision trees, clustering, neural networks and more. The predictive modeling functionality will be demonstrated through out the session. We will start by creating a project and a data source. Explanations of the best data structures will be shown, The source will be examined and explained.
Then the real fun starts. A decision tree will be built with a wizard. Then, additional models - Clustering and Naive Bayes - will be added for comparisons and finding the best model.
Once the best model is selected, the training data will be replaced with predicting data and results will be shown in SQL Server Data Tools (SSDT) and SQL Server Management Studio (SSMS). Because we can use SSMS, end users do not need SSDT to see the results.
Power BI has some really cool features and now on premise. The 2017 Reporting Services (SSRS or PBIRS) has an install that includes deploying Power BI reports. This is exciting for those wanting to do visual analytics in house and not in the cloud.
There is a separate installer for SSRS and Power BI Desktop. We will go thru the steps to setup as a development environment. Then, the session progresses to creating your first Power BI report. The analytics side will start with DAX measures and Time Intelligence. Then, we will see the integration of Custom Visualizations as well as the R Language.
Come get started with the hottest visualization tool from Microsoft and find out how you can get it up and running in a development environment at your companies.
If you have used data modeling in Power BI or PowerPivot, you have used the tabular Model. The difference is a self-service BI application versus an enterprise database everybody with permissions can use.
This session starts with the architecture of Analysis Services (SSAS) for the Tabular Model. The flow continues with Data Sources, Data Sets and Relationships. The Dimensional Model database structure will be used to demo dim and fact tables in the database.
Measures will be created along with DAX functions to show the power of SSAS. The ever important Date Table will be demonstrated as well as Time Intelligence functions. We conclude the session with security and reporting with Excel PivotTables and Power BI.
This day long session will concentrate on what currently can be done On-Premise with Power BI. Time will be spent on connecting to various data sources. Data modeling will include engine in Power BI and using a Tabular Model for enterprise modeling. Measures will be created to show the power of the DAX language. We will expand with Time Intelligence for impressing data consumers in an organization. Data modeling will show attendees why a majority of time is spent on data structure, importing and cleansing data.
The visuals are the cool stuff, but not a lot of time has to be spent here if the data is well structured. But, we will still show canned visuals, filters and custom visuals in addition to R Scripts. The formatting and interaction between visuals and filters will complete the dashboard look to the Power BI report.
The step will show using the new Power BI Report Server including the Mobile Report tool.
Data Warehouse Architect/MVP at Data on the Geaux