1、PowerPivot 和 PowerView王輝 | MVP | MCT Challenges to unlocking data insightsIntegration with existing tools41%Security & manageability37%Lack of skills & knowledge59%Connected devices per adult4.3Source: Big Data & CIO Adoption Quantitative Research, Sept 2012, MicrosoftIT has been busy keeping up wit

2、h the growing demand for BISelf-Service BICorporate BIIT ProEnd UsersETL/Data QualityHarvesting existing data, cleaning it and staging it To provision reports, dashboards & scorecards to meet End User demand.But business is outpacing IT and End Users are demanding Agility Rapidly breaking down the b

3、arriers of Control with Self-Service BI!Is it possible to balance Control with Agility?Evolution of BISpreadsheetsSpecialized ToolsAnalysisReportsDashboards & ScorecardsProvisionAnalysis CubesData WarehouseData StagingExisting DataLOB ApplicationsFilesData MartsEvolution of BINiche StartupsSelf-Serv

4、ice BIEnd UsersSpreadsheetsSpecialized ToolsIT ProETL/Data QualityAnalysisReportsDashboards & ScorecardsProvisionAnalysis CubesData WarehouseData StagingExisting DataLOB ApplicationsFilesData MartsCorporate BISQL Server7.0OLAP ServicesSQL Server2000Analysis & Reporting ServicesSQL Server2005Report B

5、uilder & Model DesignerSQL Server2008Data Mining, Dashboard & ScorecardsOffice2007SQL Server2008 R2Power Pivot in Excel SharePoint Integration Master Data Services Office2010SQL Server2012Power View Native Excel Integration Data Quality Services ColumnstoreOffice2013SQL Server2014Office 365 Integrat

6、ion Power BI Power Query Power Map Mobile Hadoop Office36515 years of innovationCloud & MobileSelf Service BIIn ExcelData ModelingIn ExcelData Mining & CPMAd-Hoc ReportingOperational ReportingMultidimensional Analyis (OLAP)Powerful, familiar BI tools for everyone Corporate BIIT professionalSelf-Serv

7、ice BIBusiness analystAdvanced analyticsData scientistScale and manage data across the enterprise with corporate BIUncover deeper insight and predict forward with advanced analyticsEmpower users with familiar self-service BI in Microsoft Office3 Key Offerings in Business IntelligenceCustomers with m

8、ultiple, disparate data sources, such as CRM, order processing, and accountingAny organization that has data they can use to drive new insights that will help their businessSolutionOffer solutions for quickly finding and combining data from multiple sources with familiar toolsDeliver intuitive, inte

9、ractive dashboards that enable customers to gain insights from dataProvide customers a platform for easily sharing insights, data, and reports Customer TargetGrow your business helping customers use data to their advantagePartner OfferingSolutions for simplified access to dataTools to easily gain in

10、sights from dataA platform for shared data and insightsCustomer PainIt is too difficult and time consuming to access and combine information from multiple sources.It is too complex to view and work with data in order to gain insights from it.There is no easy way to share data and insights with the r

11、est of the organization.Introducing Microsoft BIStrategy and VisionTo improve organizations by providing business insights to all employees, leading to better, faster, more relevantdecisionsMicrosoft has a long-term commitment to delivering a complete and integrated BI offeringSQL Server has led inn

12、ovation in the BI space for more than a decadeThere is widespread delivery of intelligence through Office The platforms are enterprise-grade and affordableIntroducing Microsoft Self Service BI (SSBI)Microsoft Office 2013Office is a suite of productivity applications well suited for SSBIThese applica

13、tions:Play a major role in realizing Microsofts BI visionAre designed to help users easily find, analyze, and more securely share business informationNotably, Excel 2013 plays a major role in delivering SSBIVisio 2013 can play a role also, albeit a lesser oneIntroducing Microsoft SSBIMicrosoft Offic

14、e 2013 ExcelOver the past decade, the core spreadsheet capabilities have been enhanced to enable analysts to analyze, communicate, and manage informationComprehensive support for querying Analysis Services data modelsRich and interactive data visualizationsAdd-ins provide rich and integrated BI capa

15、bilities:ProductPurposeExcel 2010Excel 2013Power QueryData acquisition and preparationPower PivotData modelingPower ViewPresentation-ready, and interactive reportsPower Map3D geospatial visualizationNEWNEWIntroducing Microsoft SSBIAccessCleanMash-upExploreVisualizeSharePower QueryFlash FillData Mode

16、lPower PivotQuick AnalysisQuick ExplorePower ViewPower MapApps for OfficeSave to SharePointor Power BIMicrosoft Office 2013 Excel Complete and Powerful SSBI ToolIntroducing Microsoft SSBINext Generation Power BICollaborate in Office 3651 in 4 enterprise customers on Office 365Insights in Excel1 Bill

17、ion Office UsersAnalyzeVisualizeShareFindQ&AMobileDiscoverScalable | Manageable | Trusted Introducing Microsoft SSBIPower BI for Office 365Power BI delivers cloud-based BI abilitiesBI site provide a cloud portal to share workbooks, and includes benefits: Data catalog to store and search for queries

18、Data refresh from on-premise data Larger workbook sizes, up to 250MB HTML5 report viewing Natural language querying (Q&A)There is also a mobile story with a dedicated application for showing favorite Power BI sites and resourcesPower BI for Office 365 is covered in Module 9Introducing SQL ServerSQL

19、Server also can play a major role in delivering BIStandard, Business Intelligence and Enterprise editions provide BI capabilities to deliver larger scale BI solutions:Data warehousingExtract, Transform and Load (ETL) with Integration ServicesData modeling (multidimensional and tabular)Data miningRep

20、ortingSQL Server is out of scope for this course, but it is still relevant to delivering BI to SMBI customers, particularly when they may already have SQL Server instances available Introducing Power PivotIntroducing Power PivotLoading Data and Defining RelationshipsEnhancing the Data ModelWhat You

21、Will LearnIntroducing Power PivotLoading Data and Defining RelationshipsEnhancing the Data ModelPartner OpportunitiesResourcesIntroducing Power PivotExcel 2013 includes the workbook data model to mash-up and analyze dataAllows importing tables of data from different data sourcesCreating relationship

22、s between tablesCreating simple calculated fields (aggregation of single columns)The workbook data model is delivered with a client-side version of Analysis Services, known as the xVelocity In-Memory Analytics EngineThe Power Pivot add-in for Excel provides a ribbon tab and a development window to c

23、reate more sophisticated data modelsOffice Professional Plus and Office 365 Professional Plus editions onlyThe add-in is disabled by defaultIntroducing Power PivotA Power Pivot data model delivers:An intuitive query-able resource that serves business user experiencesIntegrates data from a variety of

24、 data sources, including:Traditional data source, such as relational databasesNon-traditional sources, such as data feeds, text files and spreadsheetsDelivers accelerated access to potential extremely large data volumes well beyond the row limits of an Excel worksheetEncapsulates business rules with

25、 calculations and key performance indicators (KPIs)Supports a rich variety of client tools, including those that deliver:Interactivity, data visualization, reports, scorecards, dashboards, and custom experiencesRepresents a “single version of the truth”Introducing Power PivotA Power Pivot data model

26、 can be shared by publishing to:SharePoint 2013, providing the Power Pivot add-in for SharePoint is installedSharePoint onlineOffice 365 E3Power BIIn certain circumstances, automatic data refresh can be configuredPublication and sharing is covered in Module 9Introducing Power PivotDevelopment Method

27、ologyCreate a workbook and open the Power Pivot WindowImport data to create tablesDefine relationships between tablesEnhance the data model design with hierarchies, calculated columns, calculated fields and KPIsPrepare the data model for Power ViewProduce reports based on the data modelPublish the w

28、orkbook to SharePoint/Power BIConfigure automatic data refreshOptionalLoading Data and Defining RelationshipsIntroducing the Power Pivot WindowImporting DataPreparing DataIntroducing the Power Pivot WindowThe interface used to load, explore, relate, and enrich dataLaunched from the Power Pivot tab o

29、f the Excel ribbonProvides wizards and tools to connect to, and retrieve data from corporate, local and external sourcesRelationships can be defined to allow filtering of data by using related columns and for looking up values in related tablesWhen saved, all Power Pivot data and metadata is embedde

30、d within the Excel workbookIncludes two views: Data and DiagramIntroducing the Power Pivot WindowData ViewAllows viewing and exploring the data for a single table by scrolling, sorting and filteringThe data is read-onlyCalculated columns and calculated fields can be defined in data view onlyCalculat

31、ions are covered in Module 4Introducing the Power Pivot WindowDiagram ViewAllows viewing the tables, calculated fields, hierarchies and relationshipsHierarchies can be defined in data view onlyMinimapZoom and navigate about the diagramImporting DataData can be imported into the Power Pivot data mode

32、l by:Copy-and-pasteQuerying external sourcesLinking Excel tablesPower Query queriesData imported into the data model is read-only, and can be refreshedPower Query is introduced in Module 5Importing DataCopy and pasteCopied Excel or Power Pivot data ranges can be pasted to:Create a new Power Pivot ta

33、bleAppend data to, or replace data in, an existing Power Pivot table that was created by a paste operationWhen creating a table, Power Pivot automatically detects and sets the column data typesThe data type setting can be manually modifiedData can never be refreshed, only replaced by a copy-and-past

34、e operationImporting DataExternal Data SourcesExternal data can be imported from:DatabasesData feedsFilesData can be refreshed (reloaded) from sourcesImporting DataExternal Data Sources (continued)Microsoft SQL AzureMicrosoft SQL Server Parallel Data WarehouseOracleTeradataSybaseInformixIBM DB2Other

35、s (OLEDB/ODBC)Excel FileText FileDatabase:Data Service:Other Sources:Importing DataExternal Data Sources Table Import WizardEach option in the Get External Data group launches the Table Import WizardThe Table Import Wizard:Guides the analyst through the process of loading external dataCreates a data

36、 connectionImports data and creates relationshipsDefine ConnectionDatabase type and connection properties, orFile location, orData feed URLSelect Import MethodFor databases, select from existing tables and views, or define a query or use a stored procedureSelect and Filter TablesSelect tables/feeds,

37、 and optionally define a friendly nameIntroduce related tablesPreview dataFilter tables by columns and/or rowsImporting DataLinking Excel TablesExcel tables can be linked to create Power Pivot tablesWhen the data in the Excel table is modified, the linked Power Pivot table data can be updatedUse the

38、 Power Pivot ribbon tab in the Excel workbook to:Create new linked tablesUpdate all tables or the currently selected tableUse the Linked Table tab in the Power Pivot Window to update the linked table dataIn the Power Pivot Window, linked tables are distinguished by a chain-link iconPreparing DataTab

39、les can be renamed, moved, and deletedColumns can be renamed, copied and deletedColumn values can be sorted by a related columnThe columns must be in the same tableA one-to-one relationship must exist between the columnsUse the Home ribbon tab to:Modify connection propertiesSet column data type and

40、formatModify column and row filters (for the purpose of exploring in the Power Pivot Window)Preparing Data (continued)Use the Design ribbon tab to:Freeze/unfreeze columnsSet column widthSet calculation optionsCreate and manage relationshipsModify table properties (column and row filters used to load

41、 data, or query text)Preparing DataDefining RelationshipsRelationships between tables must be one-to-manyThey are automatically created when:The source database defines foreign key relationships, andThe related tables are imported together using the Table Import WizardThey can be manually created wh

42、en:At least one column stores unique values, andThe column data types are the sameRelationships can be configured by using a dialog box, or by drag-and-drop in diagram viewEnhancing the Data ModelThe Power Pivot data model can be enhanced by:Creating hierarchiesCreating perspectivesCreating calculat

43、ionsConfiguring reporting propertiesCalculations are covered in Module 4Reporting properties are covered in Module 7Enhancing the Data ModelCreating HierarchiesAll columns expose a hierarchy with an All level and a single level beneath consisting of members based on the distinct values stored in the

44、 columnThese hierarchies provide limited value as they do not provide exploration across the dataMulti-level hierarchies can be created in diagram viewHierarchy levels are based on columns from the same tableA one-to-many relationship should exist between the columnsThe hierarchy level names default

45、 to the columns names, and can be renamedEnhancing the Data ModelCreating Hierarchies ExampleEnhancing the Data ModelCreating PerspectivesPerspectives allow narrowing the resources made available to a userEach perspective is defined by a name and the visible resources, including:Tables, columns, cal

46、culated fields, KPIs and hierarchiesClient tools can connect to the data model or to a perspective of the data modelPerspectives are not security mechanismsPartner OpportunitiesProduce a “single version of the truth” data modelStore volumes of data in excess of worksheet limits (1,048,576 rows) and

47、with high compression (10 x on average)Easily and efficiently integrate data from different data sources corporate, local or externalEliminate the use of the VLOOKUP functionSource data from different data formats relational, multidimensional, data feed, filesEasily refresh data, when requiredEnhanc

48、e the data model with hierarchies and perspectivesIntroducing Power ViewAuthoring Reports in Excel 2013Introducing Power ViewPreparing the Data Model for Power ViewCreating Power View ReportsWhat You Will LearnIntroducing Power ViewPreparing the Data Model for Power ViewCreating Power View ReportsPa

49、rtner OpportunitiesResourcesIntroducing Power ViewPower View is a SQL Server Reporting Services authoring experience now available in Excel 2013Power View in Excel 2013 requires a tabular data model, including a Power Pivot data modelReport sheets can be added to the workbook and configure to produc

50、e interactive data exploration, visualization, and presentation experiencesHighly visual design experienceRich meta-driven interactivityPresentation-ready at all timesIt can enable intuitive ad-hoc reporting for business usersAvailable also in SharePoint 2013 with the Reporting Services add-inIntrod

51、ucing Power ViewExampleIntroducing Power ViewSystem RequirementsPower View for Excel 2013 requires:Office Professional Plus edition, orOffice 365 Professional Plus editionSilverlightThe Map data visualization requires internet connectivity (Bing Maps)Business AnalystsCreate Power View reportsPublish

52、 workbooks consisting of reportsUsersSearch for, and view and interact with, published workbooks that contain Power View reportsIntroducing Power ViewPower View AudiencesPreparing the Data Model for Power ViewThe data model can be prepared to fully exploit the Power View experience by:Applying frien

53、dly names, descriptions and column and calculated field data formatsEnabling imagesThe Advanced ribbon tab allows:Customizing “automatic” calculated field behavior (Summarize By)Configuring Report PropertiesPreparing the Data Model for Power ViewFriendly Names, Descriptions and Data FormatsUse frien

54、dly names for tables, columns and calculated fieldsThese names will be used as labels to describe Power View data visualizationsEmbedded spaces are allowedProvide descriptions for tables, columns and calculated fieldsThese are surfaced as tooltips in the Power View Fields paneSet appropriate data fo

55、rmats for columns and calculated fieldsHide unnecessary tables, columns and calculated fieldsPreparing the Data Model for Power ViewEnabling ImagesImages can be enabled by:Adding columns of image (binary) dataCreating calculated columns to produce a URL to retrieve external imagesImages can be retri

56、eved from a web server, including a SharePoint document libraryImage data cannot be efficiently stored in a Power Pivot data modelAvoid storing large images or large sets of imagesThe Table Behavior Row Identifier property must be set to a unique identifying column of the tableThe Table Behavior pro

57、perties will be introduced later in this topicPreparing the Data Model for Power View“Automatic” Calculated FieldsThere may not be the need to define explicit calculated fieldsVisible numeric columns will be surfaced as “automatic” calculated fieldsThese fields will only be available in the Power Vi

58、ew Fields pane, and will not be available in cube reporting tools (PivotTables, CUBE functions, etc.)Use the SummarizeBy property to determine the default aggregate function:The default is to sum the column valuesThe Power View report user can identify “automatic” calculated fields, and modify their

59、 aggregate function in the report layoutPreparing the Data Model for Power ViewReporting PropertiesDefault Field Set:Ordered set of columns and calculated fields that can be conveniently added as a table to a Power View report, with a single clickTable Behavior:Row Identifier: Sets the unique identi

60、fier column for a table (like a primary key), and it cannot be based on a calculated columnKeep Unique Rows: Columns that relate directly to the row identifier and that should not be presented as nested groupsFor example, State Code and State NameDefault Label: Behaves as the friendly label for the