Sunday, March 20, 2011

SharePoint Dashboard with Filters Using Common Services Performance Point 2010

In  this article  we  will create  a  dashboard  with  filters using  common services  PerformacePoint SharePoint 2010  and  SQL Server  Analysis  Services (SSA)  data source. This  dashboard will contain a chart  report and report to   the  network  and  contain common filter for both reports. Finally, the two reports  do  with our choice of  filter (s)

So, we begin to create a SharePoint site using the model of Business Intelligence Center. Click the Start button and navigate to SharePoint 2010, the instrument of central government, as follows:

This action opens the website. These pages, click on "Create Site Collection" from "Application Management" category below:

This opens the Create Site page. Use this page to choose a web application that you want to create a site collection. When you choose a web application, to provide the title and select a site URL. Alternativelycan write a description of the site, as follows:

Now select a site template from Enterprise Group:

And finally, enter the site collection administrator account with the primary "Domain \ Administrator".the domain name and click OK.

This will create a site collection.

Now click on the link and you will see the site's Business Intelligence Center as follows:

Once your site is ready, use the site and place your mouse over the dashboard create the right side. You will see the following link

Click the "Start Performance Point Services. This link takes you to a page where you can download the Dashboard Designer, click the "Run the Dashboard Designer.

This download Dashboard Designer tool, on the client side. This time, click Programs. This tool now allows you to plan your data sources, KPIs, scorecards, reports, filters and dashboards. The instrument is as follows:

The left you will see a browser workspace. In this window you will see two options -

1) Data Connections

a.  Used to create a connection with different types of data sources as shown below

b. Your presentation, we will use a multidimensional data source is Analysis Services.

2) PerformancePoint Content
  a.  Resorting to a content of Performance Point can design KPI, filters, reports, dashboards, scorecards and indicators, as shown below:

For our demonstration, we are going to design two Reports (Chart Report and Grid Report), Filters and a Dashboard.
This presentation, I'm using the Pro-clarity data warehouse database is a demo database: Microsoft. This database has given Microsoft a Microsoft Performance Point Server 2007 was released in 2007

Download the Database over here

Restore the backup database with Analysis Services. Now let's create a data connection to our database in our design tool PDW Dashboard.

Right click Data Connection > Click New Data Source as shown here:

From the Select Data Source window, select the model of the Analysis Services database, and define the image PDW:

Once you configure the connection to your database and PDW Analysis Services, point to a bucket, "Sales" and then click "Test Data Source" button. You should get a login window successfully.

Rename this data source to “PDW”.

Create a Chart Report from the PDW Data source

Next step is to create our Chart report from the PDW data source. For creating this report follow these steps:

Step 1: Right click Performance Point Content > New > Report and make a choice of “Analytic Chart” from “Select a Report Template” window as shown here:

step 2: Select the PDW data source we created earlier. Name the report "PDW Chart Report". The report will look like this:

step 3:   Now, the detail window on your right side, drag and drop "the last 12 months of sales Cal w / Sales" from "named sets" to "low Axis" in our report chart. Now drag and drop the product "family" of products "series" in our report appears as shown below:

step 4:  Now, your basic graphic report is ready. Make the choice of specific products, click the down arrow and select products - "Mountain Bikes", "Road Transport" and "touring bikes".

step 5: Now we will add parameters to our report table. To add parameters to our report, click on "Search" tab of your report, as follows:

This will show you a MDX query. Select “[Product].[Family].&[4]” from the query. In the Parameter’s section write a parameter name “ChartProduct1” and click on “Insert” button.

Repeat the above step for “[Product].[Family].&[5]” and “[Product].[Family].&[6]” to add Parameters “ChartProduct2” and “ChartProduct3” respectively.

You should have a screen that looks similar to the following:

Create a Grid Report from the PDW Data source

Now let’s create a Grid report. To create a Grid report, let’s follow these steps:

step 1: Click Performance Point content> &get; new report and a selection of "Analytic Grid" "Select a report template, as shown below:

Step 2: Select the PDW data source that created a moment ago. Name the report "PDW Grid Report." The report will look like this:

Step 3:
Now on the "Details" window, right side, drag and drop "District" from "dimension of time and the calendar attribute" and the "Columns" Report of the grid and make a choice "Q1-06" and "Q2 -06 ". Now drag and drop the "City" from "dimension" geography, "Lines", and a family of products from product dimension "Lines" section of the report grid and choose the products - "Mountain Bike", "Road Bikes" and "toured," as follows:

Step 4: Now you are ready with a basic Grid Report. Let’s add parameters to this Grid report. To do so, click on the “Query” tab.

This will show you a MDX query. Select “[Product].[Family].&[4]” from the query. In the Parameter’s section write a parameter name “GridProduct1” and click on “Insert” button.

Repeat the above step for “[Product].[Family].&[5]” and “[Product].[Family].&[6]” to add Parameters “GridProduct2” and “GridProduct3” respectively.

You should have a screen that looks similar to the following:

That’s all. Your reports are ready with parameters.

Designing Filters for Dashboard

Now let’s design our “Filters” which we can use on our Dashboard. To do this, follow these steps:

Step 1: Right Click PerformancePoint Content > New > Filter. This will show you a popup window “Select a Filter Template”. From there, select “Member Selection” as shown below:

Click the OK button. You will now see a wizard. From this wizard, first select our data source “PDW” and click the “Next” button. The next step is where you will select a “Filter Dimension” and “Filter Members”

Step 2: For the "click Filter Dimension on the" Select size. Show all sizes. Product. Family size from this list. “Now click on "Select Members" and select all the items individually.

Click next and keep the default settings as it is. Finish the wizard. Now rename your filter to “Product1” name.

Repeat the above step twice and add two more filters with the name “Product2” and “Product3”.

Step 3:  Now, our filters are ready. We will create a dashboard, which will consolidate our reports (graphical and grid) and filters that we created above.

Right click Performance Point Content > New > Dashboard which will use a Dashboard template “Header – Two Columns” as shown below:

Rename your Dashboard with “PDW Filter Dashboard”. Your Dashboard will look similar to the one shown below:

Step 4: Now, right click on "head" and click "Split Area. This area will be divided header into two sections. Now, right click and click again on Zone1" Split Area. Now your dashboard looks like this:

Step 5:  Now let's add our figure relates the left column of our panel and the grid in relation to the right column of our desk. We also add our header Filters Product1, Product2, Product3, Zone 1, Zone 2 sections, respectively, as follows:

Mapping Filters to Chart Report Parameters

Now you have placed the reports and filters to appropriate positions as per our requirement. Let’s map the filters “Product1”, “Product2”, “Product3” to our chart report parameters “ChartProduct1”, “ChartProduct2”, “ChartProduct3”. To do this let’s follow below steps:

First drop down your “Product1” filter and drag and drop “Member Unique Name” to the “Drop Fields to Create Connections” section of our “Analytic Chart Report” as shown below:

This will show you a popup window “Connection”. From that window, select your Parameter “ChartProduct1” and from “Connect To” dropdown list “Member Unique Name”.

Repeat the above steps for filters “Product2” and “Product3”.

Repeat this procedure for your Grid Report as well.

Your dashboard should now look similar to the one shown here:

Now Save your dashboard and deploy it to SharePoint Site. You will see the filters on top of your dashboard. Change the filters and see that both the reports are rendering the data as per your filter choice.

Here’s how your Dashboard will look on a SharePoint Site:

In this article we saw how to create a SharePoint dashboard filters. We also saw how to apply these filters, in general, all reports, which will also bring together the Dashboard. This demonstration, we used the multidimensional data source is Microsoft SQL Server Analysis Services (SSAS) database.




No comments:

Post a Comment