70 778 Analyzing and Visualizing Data with Microsoft

70 -778: Analyzing and Visualizing Data with Microsoft Power BI (beta) Presented By: Phillip Guglielmi | Senior Solutions and Business Intelligence Architect

Who am I? Senior Solution and Business Intelligence Architect Certified Public Accountant Worked with multinational firms to perform high impact financial modelling to facilitate M&A activities. Leveraged my background while working for Deloitte’s Strategy and Operations (S&O) focused on Technological transformation projects Working with Microsoft SQL and Business Intelligence tools for past 8 years

Microsoft Certified Solutions Associate • Background • A specialist who knows “how” to work with an advanced product • For those with access to hands-on practical experience. • Someone with 1 -2 years’ experience • Simplified Tracks • Requires passing 2 exams • Only one more exam to reach MCSE (Microsoft Certified Solutions Expert) • MCSA certification is required in order to become an MCSE.

Exam Basics • 40 -60 questions • 1 -3 hours to complete the exam • Can review questions (for most question types) • Can include up to 20 different types of questions • 700 is passing • 700 <> 70% • Can be taken Online or at a registered exam location

Who should take this exam? • Candidates may include BI professionals, data analysts, and other roles responsible for creating reports by using Power BI. • Candidates for this exam should have a good understanding of how to use Power BI to perform data analysis. • Candidates should be proficient in connecting to data sources and performing data transformations, modeling and visualizing data by using Microsoft Power BI Desktop, and configuring dashboards by using the Power BI service. • Candidates should also be proficient in implementing direct connectivity to Microsoft SQL Azure and SQL Server Analysis Services (SSAS), and implementing data analysis in Microsoft Excel.

Scheduling the Exam, where can I take it? • Schedule the exam via the Microsoft website. Note, you will be redirected to the Pearson site to schedule. • Online proctored exams are available for those interested.

What are the preparation options? • Microsoft’s course 20778 A: Analyzing Data with Power BI

Power BI Desktop

Consume and Transform Data By Using Power BI Desktop 20 -25% of the exam questions are on these topics.

Consume and Transform Data Skills Measured • Connect to data sources • Connect to databases, files, folders; import from Excel; connect to SQL Azure, Big Data, SQL Server Analysis Services (SSAS), and Power Query • Perform transformations • Design and implement basic and advanced transformations, apply business rules, change data format to support visualization • Cleanse data • Manage incomplete data, meet data quality requirements Prepare

Power BI Desktop

Connecting to data sources • Connect to databases, files, folders; import from Excel; connect to SQL Azure, Big Data, SQL Server Analysis Services (SSAS), and Power Query • Getting Data w/ Power BI Desktop Connecting to databases • SQL Server • Use Direct. Query in Power BI Desktop • Assume referential integrity settings in Power BI Desktop (I think I remember there being a question on this one…) • Oracle • Connecting to files • Excel files • CSV files • Power BI Desktop files: (Data sources for the Power BI service)

Connecting to data sources • Connecting to folders • Importing from Excel • Connecting Azure SQL • Azure SQL Database • Azure SQL Data Warehouse • Connecting to big data sources: (Visualize big data with Power BI and Spark on Azure HDInsight) • SQL Server Analysis Services • Tabular • Multidimensional

Perform transformations • Design and implement basic and advanced transformations, apply business rules, change data format to support visualization • Candidates should be prepared for this section on advanced data sources and transformations because it is a large part of Power BI • Shape and combine data with Power BI Desktop

Cleanse data • Manage incomplete data, meet data quality requirements • Cleaning irregularly formatted data • Meet data quality requirements

Model and Visualize Data 45 -50% of the exam questions come from these topics.

Model and Visualize Data Skills Measured • Create and optimize data models • Create calculated columns, tables, and measures • Create performance KPIs • Create hierarchies • Create and format interactive visualizations • Manage custom reporting solutions

Power BI Desktop

Create and optimize data models • Manage data relationships, optimize models for reporting, manually enter data, Use Power Query • • How to Manage Your Data Relationships Optimizing data models for better visualizations Optimize your data for Power BI Quick Insights Enter data directly into Power BI Desktop

Create calculated columns, tables, and measures • Create DAX queries for • • Calculated columns Tables Measures Power BI Desktop Quick Measures

Create performance KPIs • Calculate the actual, calculate the target, calculate actual to target • Tutorial on creating KPIs in Power BI Desktop

Create hierarchies • Use date hierarchies, use business hierarchies, resolve hierarchy issues • Visual hierarchies and drill-down behavior • Exploring time based visualizations

Create and format interactive visualizations • Select a visualization type • Configure page layout and formatting • Setup visual relationships, configure duplicate pages • Handle categories that have no data, setup default summaries and categories • Position, align, and sort visuals, enable and integrate R visuals, format calculated measures

Manage custom reporting solutions • Use Power BI API • Use Microsoft Power BI Embedded • Enable developers to create and edit dashboards through custom applications • Enable developers to embed dashboards in applications • Authenticate a Power BI web application • Enable developers to create custom visuals

Configure Dashboards in the Power BI Service 25 -30% of the questions in the exam are on these topics

Configure Dashboards in the Power BI Service Skills Measured • Configure a dashboard • Publish dashboards • Configure security for dashboards • Configure organizational content packs and apps

Power BI Desktop

Configure Dashboard • • • Connect to the Power BI service Publish connections to services by using SQL Server Analysis Services (SSAS) Publish visualizations Configure a dashboard Add text and images Filter dashboards Configure dashboard settings Customize the URL and title Enable natural language queries

Publish dashboards • Publish to web • Publish to Microsoft Share. Point • Publish to SQL Server Reporting Services (SSRS)

Configure security for dashboards • Create a security group by using the Admin Portal • Share dashboard with users or security groups • Integrate with Microsoft One. Drive for Business • Configure row-level Security • Configure gateways

Configure organizational content packs and apps • Create a content pack • Publish a content pack • Edit a content pack • Package dashboards and reports • Configure app workspace

Additional Preparation Resources • Guided Learning section on Power BI. com: https: //powerbi. microsoft. com/en-us/guided-learning/ • Ed. X. org Analyzing and Visualizing Data with Power BI: https: //www. edx. org/course/analyzing-visualizing-data-power-bimicrosoft-dat 207 x-6 • Lynda. com Learning Microsoft Power BI Desktop: https: //www. lynda. com/Power-BI-tutorials/Up-Running-Microsoft. Power-BI-Desktop/445425 -2. html • Pragmatic Works also has a lot of free webinars regarding Power BI on their website. Just do a search for Power BI on this page: http: //pragmaticworks. com/Training/Courses#type=Free

Thank you for Attending! Don’t forget to join your local PUG to enjoy year-round networking and learning. www. pbiusergroup. com/NYNJ www. pbiusergroup. com/philly

Connecting to data sources • Connect to data sources: https: //powerbi. microsoft. com/en-us/documentation/powerbi-desktop-data-sources/ • Connect to databases, files, folders; import from Excel; connect to SQL Azure, Big Data, SQL Server Analysis Services (SSAS), and Power Query • Getting Data w/ Power BI Desktop (video): https: //powerbi. microsoft. com/en-us/guided-learning/powerbi-learning 1 -1 -overview-of-power-bi-desktop/ • Connecting to databases • SQL Server • Use Direct. Query in Power BI Desktop: https: //powerbi. microsoft. com/en-us/documentation/powerbidesktop-use-directquery/ • Assume referential integrity settings in Power BI Desktop (I think I remember there being a question on this one…): https: //powerbi. microsoft. com/en-us/documentation/powerbi-desktop-assume-referentialintegrity/ • Oracle: https: //powerbi. microsoft. com/en-us/documentation/powerbi-desktop-connect-oracle-database/ • Connecting to files: https: //powerbi. microsoft. com/en-us/documentation/powerbi-service-get-data-from-files/ • Excel files: https: //powerbi. microsoft. com/en-us/documentation/powerbi-desktop-connect-excel/ • CSV files: https: //powerbi. microsoft. com/en-us/documentation/powerbi-service-comma-separated-valuefiles/ • Power BI Desktop files: (Data sources for the Power BI service) https: //powerbi. microsoft. com/enus/documentation/powerbi-service-get-data/

Perform transformations • Design and implement basic and advanced transformations, apply business rules, change data format to support visualization: https: //powerbi. microsoft. com/en-us/guidedlearning/powerbi-learning-1 -3 -clean-and-transform-data-with-queryeditor/ • This section on advanced data sources and transformations could also be useful: https: //powerbi. microsoft. com/en-us/guided-learning/powerbilearning-1 -4 -advanced-data-sources-and-transformation/ • Shape and combine data with Power BI Desktop: https: //powerbi. microsoft. com/en-us/documentation/powerbidesktop-shape-and-combine-data/

Cleanse data • Manage incomplete data, meet data quality requirements • Cleaning irregularly formatted data (video): https: //powerbi. microsoft. com/en-us/guided-learning/powerbi-learning-1 -5 cleaning-irregular-data/ • https: //msdn. microsoft. com/en-us/library/mt 296604. aspx • Meet data quality requirements