Microsoft SQL Server 2014 Business Intelligence Development Beginner's Guide

More Information
Learn
  • Perform Data Modeling with SSAS Multidimensional and MDX
  • Use DAX and SSAS tabular for Data Modeling
  • Manage Master Data with MDS
  • Reveal Knowledge Driven Data Quality with DQS
  • Understand prediction and Data Mining
  • Identify data patterns and predictive models
  • Design Dashboards with PerformancePoint and Power View
  • Explore Power Query and Power Map as components of Power BI
  • Create Reports with SQL Server Reporting Services and integrate them in applications
About

Take advantage of the real power behind the BI components of SQL Server 2014, Excel 2013, and SharePoint 2013 with this hands-on book. This book will give you a competitive advantage by helping you to quickly learn how to design and build BI system with Microsoft BI tools.

This book starts with designing a data warehouse with dimensional modeling, and then looks at creating data models based on SSAS multidimensional and Tabular technologies. It will illustrate how to use SSIS for ETL, and MDS and DQS for data governance and data quality. The book is packed with real-world examples that will give you a good understanding of the BI and DW components of SQL Server 2014, Excel 2013, and SharePoint 2013.

Features
  • Discover the Dimensional Modeling concept while designing a data warehouse
  • Learn Data Movement based on technologies such as SSIS, MDS, and DQS
  • Design dashboards and reports with Microsoft BI technologies
Page Count 350
Course Length 10 hours 30 minutes
ISBN 9781849688888
Date Of Publication 25 May 2014
Why is OLAP required?
Understanding the SQL Server Analysis Services engine
Developing your first cube
Time for action – creating an Analysis Services project
Time for action – creating the first cube
Time for action – viewing the cube in the browser
Dimensions and measures
Time for action – using the Dimension Designer
Time for action – change the order of the Month attribute
Time for action – modifying the measure properties
Data Source View
Time for action – creating a Named Calculation
Time for action – using a Named Query
Using dimensions
Time for action – adding a Fact relationship
Hierarchies
Time for action – creating a hierarchy
Multidimensional eXpression, calculated members, and Named Sets
Time for action – writing an MDX query
Time for action – calculated members
Deploying and processing
Time for action – deploying an SSAS project
Time for action – processing the data
Summary
Introducing SSAS Tabular
Developing a tabular project
Time for action – creating a tabular project
Time for action – creating measures
Creating hierarchies
Time for action – creating a hierarchy from a single table
Time for action – creating a hierarchy from multiple tables
Data Analysis eXpression, calculated columns, and measures
Time for action – using time intelligence functions in DAX
Securing the data
Time for action – security in tabular
Storage modes
Time for action – creating a model with the DirectQuery storage mode
Tabular versus Multidimensional SSAS
Summary
Understanding ETL and data consolidation
SQL Server Integration Services
Integration Service projects and packages
Time for action – creating your first SSIS project
The Control Flow tab
Time for action – working with Control Flow tasks
The Data Flow tab
Time for action – loading customer information from a flat file into a database table with a Data Flow Task
Containers and dynamic packages
Time for action – looping through CSV files in a directory and loading them into a database table
Deploying and executing
Time for action – deploying an SSIS project
Time for action – executing an SSIS package from a catalog
Summary
Understanding Master Data Management
Master Data Services
Time for action – configuring MDS
Comparing WebUI with the Excel Add-in
Time for action – installing Excel Add-in
Creating models and entities
Time for action – creating a model and an entity
Time for action – creating an entity with data from the Excel Add-in
Time for action – change tracking
The entity relationship
Time for action – creating a domain-based relationship
Business rules
Time for action – creating a simple business rule
Working with hierarchies
Time for action – creating a derived hierarchy
Security and permission
Time for action – permission walkthrough
Integration management
Time for action – a subscription view
Time for action – entity-based staging
Summary
Understanding data quality
Data Quality Services
Time for action – installing Data Quality Services
Knowledge Base Management
Time for action – creating a Knowledge Base
Knowledge discovery
Time for action – knowledge discovery
Data cleansing with Data Quality Projects
Time for action – using Data Quality Project for cleansing
Domain and composite domain rules
Time for action – composite domain rules
Synonyms and standardization
Time for action – creating synonyms and setting standardization
Matching
Time for action – matching policy
Time for action – matching projects
Integrating with MDS and SSIS
Time for action – the DQS cleansing component in SSIS
Summary
An introduction to data mining
The Microsoft Decision Tree algorithm
Time for action – creating a data mining solution with the Microsoft Decision Tree algorithm
Microsoft association rules
Time for action – the Microsoft association rule
Summary
Finding the best algorithm
Time for action – finding the best mining model with Lift Chart and Profit Chart
Predicting data with DMX
Time for action – predicting prospective bike buyers
Microsoft Time Series
Time for action – predicting future sales with Microsoft Time Series
Summary
The Reporting Services architecture
Developing a basic report
Time for action – creating our first report using SSRS
Extended report development
Time for action – adding parameters to a report
Printing and page configuration
Time for action – changing a page's properties
Sorting and grouping
Time for action – applying ordering and grouping on the data rows
Expressions
Time for action – changing the background color of data rows based on expressions
Time for action – working with charts in Reporting Services
Deploying and configuring
Time for action – deploying a report
Time for action – using Report Manager
Summary
The PerformancePoint service
Time for action – configuring PerformancePoint in SharePoint
Time for action – creating your first dashboard with PerformancePoint Dashboard Designer
Time for action – creating a dashboard page
Time for action – exploring on-the-fly features
Time for action – working with filters
Power View
Time for action – enabling Power View in Excel
Time for action – creating the first Power View dashboard
Time for action – geographical data visualization using Power View
Time for action – visualizing time-based information with a scatter chart
Time for action – using Filter in Power View
Summary
Self-service ETL with Power Query
Time for action – self-service ETL with Power Query
Power Map
Time for action – data visualization with Power Map
Summary
Designing .NET applications with reports
Time for action – installing AdventureWorks SSRS sample reports
Developing reports in a web application
Time for action – working with reports in web/Windows applications
Developing reports in a Metro application
Time for action – working with reports in Metro applications
Working with ReportViewer in a local processing mode
Time for action – designing reports and working with the local processing mode
Passing parameters to a report
Time for action – changing a report configuration with a ReportViewer Object through code behind
Using the results of a mining model in an application
Time for action – running DMX queries from a .NET application
Summary

Authors

Reza Rad

Reza Rad has more than 10 years of experience in databases and soft ware applications. Most of his work experience is in data warehousing and business intelligence. He has worked with Microsoft technologies from SQL Server 7 and Visual Basic 6 to the latest versions of SQL Server and .NET. He has a Bachelor's degree in Computer Engineering.

Reza is a DW/BI architect and consultant. He has worked with large enterprises around the world and delivered high-quality data warehousing and BI solutions for them. He has worked with industries in different sectors, such as Health, Finance, Logistics, Sales, Order Management, Manufacturing, Telecommunication, and so on.

Reza has been awarded the Microsoft Most Valuable Professional in SQL Server from 2011 to 2014. He is an international speaker in SQL Server, BI, and DW conferences around the world. He speaks in SQL PASS Summits, Microsoft TechEd, SQL Saturdays, SQL Server user groups, and code camps.

Reza has written books on SQL Server and databases. One of his most recent books is SQL Server 2012 Integration Services: An Expert Cookbook, Packt Publishing.

Reza is a big fan of data-related technologies, his expertise is in EIM, ETL, Data governance, big data, data warehousing, BI, data visualizati on, Master Data Management, OLAP, and so on. He is the author of Webcast series on the RADACAD website (http://www.radacad.com). He writes articles and blog posts on his technical blog (http://www.rad.pasfu.com); he writes about most of the Microsoft technologies, such as SSIS, MDS, DQS, SSAS, Power BI, HDInsight, and so on. His blog contains the latest information on his presentations and publications.

Reza is a Mentor and a Microsoft Certified Trainer. He has been in the professional training business for many years. He conducts extensive 400 handed-level training for many enterprises around the world via both remote and in-person training. He is an active member of MSDN forums, especially those on SQL Server and Power BI. He is also the leader of the New Zealand Data Warehousing and Business Intelligence user group, which is a registered chapter of PASS.