Showing posts with label Self Service BI. Show all posts
Showing posts with label Self Service BI. Show all posts

Sunday, June 12, 2011

Self Service BI in SharePoint 2010: Part 4 Reporting Services

This is part 4 of the 4 part blog series based on session by Peter Myers in TechEd 2011 . You can find the slides here. I have taken the key learning from the presentation for my future reference. For Part 1 or Part 2 or Part 3 go here or here or here

Reporting Services

SQL Reporting Services can be integrated with SharePoint 2010 with Web Parts. Report Builder 3.0 is a rich client authoring tool that can be installed right from SharePoint. Reports can be saved in SharePoint library and the management and delivery functions of Reporting Services is available within the SharePoint interface. The advantages of using SharePoint deployment in Reporting Services is that it enhances team collaboration and versioning of reports.

Report Builder 3.0 Interface:

image

Insert a Map:

image

Integrated with Bing Map:

image

Use existing queries to pull the data in for the report.

image

You can create relationships between spatial and analytic data: In the below example map STATENAME to State:

image

Note the mapping:

image

Apply Data Visualization: Lower the sales, lighter the color; Greater the sales, darker the color.

image

Overlay the state code if needed:

image

Create Tooltips using the expression builder so that when the user hovers over the state, the tooltip will display the state and the number:

image

When you run the report, note the Time Filter you got by just checking the parameter checkbox:

image

Result:

image

Note the tool tip:

image

Save the Report to the Report Library:

image

Back in the SharePoint, the report is visible:

image

This report could also be added to the Presentation Point Service Dashboard and integrated with KPIs.

Self Service BI in SharePoint 2010: Part 3 Visio Services

This is part 3 of the 4 part blog series based on session by Peter Myers in TechEd 2011 . You can find the slides here. I have taken the key learning from the presentation for my future reference. For Part 1 or Part 2 go here or here

Visio Services

With SharePoint 2010 Visio Services, you can now render Visio diagrams within a Browser. Visio diagrams can be based on data sources and can be made interactive. Data Sources can be SQL Server, SharePoint List Data, Excel Services and OLE DB/ODBC sources. Use the Visio Web Parts to embed diagrams in SharePoint.

Note the Visio file with showing Servers of Contoso:

image

The visio diagram will get its data from a SharePoint List:

image

Visio Shapes can have data assigned to them:

image

Data tab contains the Link Data to Shape:

image

Select Microsoft SharePoint Foundation List:

image

Choose the list:

image

Data is imported into the diagram:

image

Data can be automatically linked to Shapes:

image

Use the dialog to map the data column to the shape field:

image

Next bring the diagram to life with the help of Data Graphics which is a collection of items:

image

Create a New Graphic Item to Display the Server:

image

Now Select the Shapes where you want to apply the new graphic item. Note that the header picks up the data from the SharePoint List we had imported:

image

Create a new graphic item for the status of the servers: online or offline

image

All the servers show green because the status is green:

image

Publish to SharePoint:

image

Choose Web Drawing:

image

Upload the Visio diagram:

image

Visio services open the document in the browser:

image

If you change the status of the server, visio services picks it up:

image

Server is offline:

image

Friday, June 10, 2011

Self Service BI in SharePoint 2010: Part 2: Performance Point Services

This is part 2 of the 3 part blog series based on session by Peter Myers in TechEd 2011 . You can find the slides here. I have taken the key learning from the presentation for my future reference. For Part 1, go here

Performance Point Services (PPS)
Performance Point Services (PPS) is a performance management application where you define rich KPI and Score Card definitions to support monitoring. It includes comprehensive report types to support analysis and delivers interactive browser based dashboard all within the SharePoint. The picture below shows the key elements of PPS and how they are related.
clip_image002
Start by going to the Performance Point Content Library in SharePoint:
clip_image004
PPS comes with a Dashboard Designer tool:
clip_image006
See Data Connections:
clip_image008
Create new Scorecard:
clip_image010
Choose a template:
clip_image012
You are presented with a Wizard to create a KPI. You can import the KPIs from an excel workbook which is part of the Excel Reports Library in SharePoint.
image
Scorecard is created:
image
A KPI consists of 3 main things: Value, Goal & Status, Trend.
image
The Value of the KPI is got by using the formula: KPIValue(“Sales Performance”).
The threshold for the goals can be set as follows:
image
Likewise trends can be defined as below.
image
Once you save the dashboard, the KPI and the Scorecard will be visible in SharePoint
image
Back in the PPS Designer, note that the dimensions of the cube is visible which can be added to the Scorecard:
imageimage
The Scorecard is updated with the new dimension
image
Analytic capabilities of PPS is handled by Reports:
image
Choose the report template:
image
Create the Report:
image
Next define a filter:
image
Choose the type of filter:
image
Select the Members for the Filter:
image
Select the dimension:
image
You can choose to display the filter as a List, Tree or Multi-Select and save the filter:
image
You can create an Excel Services Report:
image
Enter the Report Settings:
image
Note the Year Parameter that was defined on the Fiscal Date Hierarchy will show:
image
Now it is time to bring all of the above in a Dashboard; so start by creating a new Dashboard:
image
Drag and Drop the filter, Scorecard and Report on the dashboard. Filters can pass values to Scorecards and Reports and Scorecards can pass values to Reports. Reports can only consume from either scorecards or filters.
image
Drag and Drop the filter on the Scorecard and the report to make a connection between the items of the dashboard:
image
Connect the Scorecard to the Report by pass the Sales Person Name (just drag the Member Unique Name onto the Report to make the connection between items of the dashboard):
image
Finally Deploy to SharePoint:
image
Dashboard View in the SharePoint:
image
Note Selection of North America from the hierarchy in the Scorecard changes the Salespeople Report:
image
Note the ability to drill across dimensions:
image
You can choose other measures available in the cube:
image
See details behind aggregated numbers:
image
Note the details ability to export to Excel:
image
Create graphs and analyze the data in decomposition tree:
image
Note the decomposition tree. The Decomposition tree is rendered through Silverlight:
image
Excel can now be viewed directly within the browser with the help of Excel Services. The below Excel Sheet connects to Analysis Services:
image
Coming up next is Vision Services. I will write that in my next blog post.

Udemy