MSBI # 39 – PowerPivot #1 – Introduction to Microsoft SQL Server PowerPivot (Features,Architecture,Self-Service BI,Reference)

Hi friends,

Lets start with PowerPivot also in this weekend only !!

As in this series I will go with the basics at start in the initial stage.

We will cover following topics in this Article

• What is PowerPivot
• Key Features of PowerPivot
• Download PowerPivot Free Add-in
• Basic Architecture for PowerPivot
• Screen Shots for PowerPivot
• PowerPivot With SSRS
• PowerPivot With Excel
• PowerPivot As “Managed Self-Service BI”
• Where I can Use PowerPivot ?
• Reference for PowerPivot

What is PowerPivot ?

It is visualization of data and transform to information also a self-service business intelligence tool provided as an add-in for Microsoft Excel 2010…

This is rocket science for most of the people and they will take this as definition and will never looked in this technology basically 0who are new to PowerPivot!!

Following is how we will integrate PowerPivot with other !

PowerPivot refers to a collection of applications and services that provide an end-to-end solution for creating and sharing business intelligence using Excel and SharePoint.

Key Features of PowerPivot  !!

• PowerPivot gives users the power to create compelling self-service BI solutions, facilitates sharing and collaboration on user-generated BI solutions in a Microsoft SharePoint Server 2010 environment, and enables IT organizations to increase operational efficiencies through Microsoft SQL Server 2008 R2-based management tools.
• PowerPivot integrates with Excel and SharePoint.   PowerPivot server components load the data, process queries, perform scheduled data refresh, and track server and workbook usage in the farm.
• PowerPivot is a new product available as a free add-in to Excel 2010. PowerPivot for Excel builds on top of Excel functionality and adds additional features to empower the user for managed Self-Service Analysis.
• In an Excel environment, PowerPivot for Excel provides a familiar authoring and analytical experience on the workstation.
• With PowerPivot, users can easily collect, interact with, and manipulate data from a broader range of sources, as well as work with data sets far larger than the Excel 2010 limit of 1 million rows per sheet.
• PowerPivot can scale to millions and even hundreds of millions of rows. The ability to handle this much data should easily meet the needs of almost any self-service BI analysis.
• However, bear in mind, PowerPivot is not well-suited for extremely large datasets and has a physical storage limit of 2 GB when compressed on disk.
• You can also use PowerPivot to prototype the solution quickly before involving IT in building more traditional BI infrastructure like a formal SQL Server Analysis Services cube.
• In a SharePoint farm, PowerPivot for SharePoint adds server-side applications and features that support PowerPivot data access and management for workbooks that you publish to SharePoint.
• Additionally, users building PowerPivot models should have a good understanding of not just the different data sources, but also how the pieces of data relate to one another.
• With PowerPivot can analyze and visualize millions of rows of data using PowerPivot .
• PowerPivot (Also known as Project Gemini) is our latest and hottest business intelligence innovation and intended to finally make BI accessible & easy to the masses using

Download PowerPivot Free Add-in

• If you have Excel 2010 you can download the PowerPivot add-in right away and see it for yourself.

• If you don’t have Excel 2010, download that too.

Basic Architecture for PowerPivot

Screen Shots for PowerPivot

PowerPivot Model Published to SharePoint Server 2010

PowerPivot With SSRS

• We can use SQL Server Analysis Services data processing extension to retrieve data from a PowerPivot workbook that is published in a SharePoint PowerPivot Gallery.
• The PowerPivot data source must be published in a PowerPivot Gallery on a SharePoint site.
• To support connections from Report Builder to a PowerPivot workbook, you must have SQL Server 2008 R2 ADOMD.NET on your workstation computer.
• Use report data source type Microsoft SQL Server Analysis Services.
• The connection string is the URL to PowerPivot workbook published on SharePoint in the
• Specify the credentials that you need to access the PowerPivot workbook and SharePoint site, for example, Windows Authentication (Integrated Security).
• After you connect to the PowerPivot data source, use the MDX graphical query to build a query by browsing and selecting from the underlying data structures.
• In the Filters pane, specify dimensions and members to filter out or to include in the query results.
• In the Filters pane, select the Parameters option for a filter to automatically create a report parameter with available values that correspond to the filter selections.
• If you open Report Builder from the PowerPivot workbook in a PowerPivot Gallery, the PivotTables, PivotCharts, slicers, and other layout and analytical features from the PowerPivot workbook are not re-created in the report.
• Preview images of a report in a PowerPivot Gallery are generated only if the following conditions are met: First The report and the PowerPivot workbook that provides the data must be stored together in the same PowerPivot Gallery and second The report contains only PowerPivot data from a PowerPivot data source.

PowerPivot With Excel

• Microsoft SQL Server PowerPivot for Microsoft Excel (PowerPivot for Excel) is a data analysis tool that delivers unmatched computational power directly within the application that you already know Microsoft Excel.
• PowerPivot for Excel Help will get you up to speed and enable you to be productive right away.If you are an IT administrator and you want to learn about how PowerPivot for Excel and PowerPivot for SharePoint can be used in your organization, PowerPivot With SharePointWhen you view a PowerPivot workbook from a SharePoint library, the PowerPivot data that is inside the workbook is detected, extracted, and processed separately on Analysis Services server instances within the farm, while Excel Services renders the presentation layer.
• You can view the fully-processed workbook in a browser window or in an Excel 2010 desktop application that has the PowerPivot add-in.
• The following diagram shows how a request for query processing moves through the farm

PowerPivot As “Managed Self-Service BI”

• PowerPivot for Excel enhances self-service BI capabilities by providing you with greater flexibility of data manipulation using any size data set in structured or unstructured form using the familiar Office interface.

• In addition, you can publish your analysis in SharePoint 2010 as a web application and share it with insights with your co-workers.

• “Managed” Self Service BI means that IT also benefits greatly with PowerPivot. They can provision reports – thus providing a single version of truth – and have end users work directly with data from those reports via Data Feeds.

Where I can Use PowerPivot ?

• Excel-PowerPivot for Excel is an add-in to Excel 2010 that provides you with the capability to manipulate vast amounts of data using, integrate data from multiple data sources and share the results with others – with little or no assistance from IT
• We can use in OLAP cubes, ATOM feeds with tabular data, Paste/Append & Paste/Replace any tabular data, Excel Pivot Tables, and structured relational databases. You can fix referential integrity issues using DAX expressions.
• PowerPivot for Excel also supports NULL/unknown members, similar to classic Analysis Services.
• SharePoint/SSRS – PowerPivot for SharePoint is nothing but SQL Server 2008 R2 Analysis Services running in Vertipaq mode within SharePoint 2010.
• The only client dependency is Excel 2010. It will not work with earlier versions of Excel.

Reference Link for PowerPivote

• Version and Server Mode Considerations
• Client Dependencies
• Authentication and Authorization Considerations
• Importing report data into a PowerPivot workbook
• Publishing reports and workbooks to a server
• Deploying Reporting Services and PowerPivot for SharePoint in the same farm
• PowerPivot for SharePoint
• Roadmap to Creating PowerPivot Workbooks in Excel
• PowerPivot Components and Tools
• PowerPivot Features
• Plan a PowerPivot Deployment in a SharePoint Farm
• What is PowerPivot for Excel
• What is PowerPivot for SharePoint
• Excel Window: PowerPivot Features
• PowerPivot Window: Home TabPowerPivot Window: Design Tab
• PowerPivot Window: Linked Tables Tab
• PowerPivot Window: Keyboard Shortcuts
• Getting Started with Data Analysis Expressions (DAX)
• Install PowerPivot for Excel
• Learn About PowerPivot CapabilitiesTake a Tour of the PowerPivot UI
• Watch Videos About PowerPivot Tasks
• Create Your First PowerPivot Workbook (Tutorial)

Hope this Helps !!!!!!

Thanks for visiting my blog !!

Hope you have understood basic aspect of PowerPivote and ready to use every aspects for same

PowerPivote

If you really like reading my blog and understood at lest few thing then please don’t forget to subscribe my blog

If you wan daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog

Where todays links are

Link Resource # 24: Sept 08–Sept 12 « Dactylonomy of Web Resource