Friday, March 1, 2013
Saturday, January 21, 2012
Power View is a Web browser-based report authoring tool targeted at everyone to enable them to create compelling, interactive and rich data visualizations based on Tabular BI Semantic Models. Steps below are based on the SQL Server 2012 hands-on labs.
Start by going to the SharePoint 2010 Central Administration:
Go to Application Management>Create site collection
Template>Enterprise: Business Intelligence Center
Primary Site Collection Administrator: administrator
The site is created:
Under Centeral Administration>Site Settings>Features, ensure PowerPivot Administrative feature is enabled. If not ‘Activate’ it.
To create a new PowerPivot Gallery, Under Site Actions, select More Actions and select
Create a PowerPivot Gallery:
Now it is time to explore the Power View Design Environment. Let’s start by uploading a PowerPivot workbook to the gallery.
On the Library Tools Documents tab, inside the New group, click Upload Document. Choose Tailspin Toys Sales Model.xlsx from C:\SQL2012DEVKITRCO\Labs\PowerView.
Now it is time to explore the PowerView designer:
This will take us to the PowerView designer:
The first thing we will do is save the report. File>Save as
A Power View report is saved to a SharePoint library using a variation of Report Definition Language (RDL). It is saved as a document with an .rdlx extension.
Back under the Home Ribbon, note that this is a WYSIWYG (What-You-See-Is-What-You-Get) designer that supports an interactive design and data exploration experience. To the right of the report designer is the Field List that it exposes the tabular BI Semantic Model that initially consists of a list of tables.
The icons you see next to the SKU and Product Name fields indicate that they represent values at the lowest granularity of the table. Model developers can mark fields as label fields to help Power View users understand that these fields can be used to see all entries of the table, and to ensure they group appropriately.
Let’s start creating a report by adding a title.
Click on Product and note that the table is added to the report:
Under the Design tab, change the view to Card type and note the change to the report.
Resize the table so that as shown below to make vertical space in the page.
Now let’s add another table for Demographic and Revenue. Click anywhere on the page and choose Product>Demographic and Sales>Revenue.
Now let’s add a Bubble Chart with Sum of Quantity and Product Category. Click anywhere in the designer and choose Product>ProductCategory and Sales>Quantity.
Goto Design>Chart and select Scatter. Fill out the Scatter Chart attributes as follows:
Under the play axis, drag and drop the month.
Expanding the Chart shows as follows:
Note you can animate the chart
Now, let’s add a Tile View to the first table we added to the PowerView report:
On the Card Tools Design ribbon tab, select Tiles. On the Design Ribbon, select Cover Flow under the Tile Visualizations section
Under Product>Image>select Add as Tile By.
Save the report and we’re done.
Here is the final view of the report that shows what is possible with PowerView:
Sunday, January 15, 2012
Microsoft BI: Developing KPIs and Scorecards with Microsoft SharePoint 2010 PerformancePoint Services
PerformancePoint Services 2010 is the next generation performance management application from Microsoft. Delivered as a Microsoft SharePoint 2010 shared application, it provides rich Key Performance Indicator (KPI) and scorecard definitions.
Let’s create a PerformancePoint data source that will connect to Adventure Works DW 2008R2 Analysis Services Cube.
Go to PerformancePoint Content:
Go to Items>
Dashboard library is designed to store dashboards.
Data Connections stores various connection files: PerformancePoint Data Sources, Office Data Connection files and Universal Data Connection files.
Under Data Connections, Go to PerformancePoint Data Source
If Dashboard Designer is not already installed, then you’ll be prompted to run the install:
Dashboard Designer should launch:
Choose Analysis Services Data Source:
Specify your analysis services connection:
The Unattended Service Account means that the credentials configured for the PerformancePoint Services application will be used.
Go to Properties Time and choose:
In the time member association set the time aggregation as shown:
When you save, the connection is saved and visible in the Data Connections library:
Save the workspace by clicking the save as button:
The workspace is an XML document that defines the PerformancePoint item definitions for a particular project.
Now lets create a Sales Performance KPI
Right click on PerformancePoint Content and add a new KPI:
Go to Properties and enter the Name: Sales Performance, Display Folder: KPIs
Change the default Actual and Target to: Sales and Sales Target
Configure the Sales metric data column:
Assign “Reseller Amount” to Sales and “Sales Amount Quota” to Sales Target.
Adjust Threshold 1 to 85%
Now lets add Profit% and Profit% Target by clicking the buttons “New Actual” and “New Target” respectively. In PerformancePoint Services 2010, metrics can be based on existing data sources, existing metrics for the current KPI, or calculations. Calculations allow multiple values to be sourced and combined into a single formula. The Calculated Metric tab lists samples and common formulas.
In the Select a Data Source window, click the Calculated Metric tab and select Gross Profit Margin.
Map GrossProfit to AdventureWorks>Reseller Gross Profit and NetSales to Reseller Sales Amount
To adjust the formula, in the Formula box, remove the * 100, and then click OK
Configure Profit% Target to 0.03
In the Format Number window, select Percentage in the Format list and apply Percentage format (2 decimals) to Profit% and Profit% Target.
Click Set Scoring Pattern and Indicator in the Thresholds section/
In the Edit Banding Setting window, review the default scoring pattern and method, and then click Next.
Choose Stoplight A – Small and click Next.
Observe default worst value and click finish.
Save the workspace.
Now lets create a Sales Performance Scorecard.
Choose a Blank Scorecard:
Change the Scorecard name to “Sales Performance Scorecard”.
From the Details pane on the right, drag and drop the KPI we created into the Scorecard.
From the Dimensions, drag and drop Sales Territory>Sales Territory into the Scorecard:
Right-Click Select all descendants.
To view the data, go to Edit>View>Update
To remove the Profit% metric, right-click the Profit% cell, and then click Delete. To modify the Profit% Target metric, right-click the Profit% Target cell, and then click Metric Settings. Change as shown below:
Change the font color of Territory Dimension Members to blue and Save the Score Card in the Workspace.
Here is the final Scorecard:
We learned how PerformancePoint items are catalogued and managed in SharePoint Server 2010. We created and configured data sources. We created and configured Key Performance Indicators (KPIs) and we created and configured a scorecard.