Creating Self-Service Business Intelligence dashboard with PowerPivot – Part I

What is Self-Service Business Intelligence?
Self-Service Business Intelligence – What is it? Is it a new term? You may know about this, but when I heard about it, I spent couple hours to understand and explore, what is this…. why it is called as Self-Services Business Intelligence, so, before going for dashboards with PowerPivot, if you guys have same set of questions as I had, here is the way I understood it:

Everybody knows what Self-Service is. For me, it is something like, you are getting something done by yourselves without zero or minimal help from others. Withdrawing money through an ATM is a good example for this. Simply, it empowers YOU, allowing to manage the services you want, as you want. So the concept of Self-Service Business Intelligence is same as it. You are empowered to manage your Business Intelligence scenarios, or in technical terms, your BI applications. Is it that easy??? Yes, you, not a technical person,  create your own BI application.

What is PowerPivot?
PowerPivot is a managed Self-Service Business Intelligence Product. This product allows you to connect with any type of data sources (I can use the word “any type” because almost all data sources are allowed to connect with either ODBC or OLE DB) and consume them for creating your analytical dashboards for seeing the insight of your business. Do we have to write codes for this? Do we have to create database schema for this? Do we have to use Visual Studio for creating analytical dashboards? NO, NO, NO. You are going to use Business User’s Pet, The Microsoft EXCEL. See the dashboard I have created using Microsoft Excel 2010 and PowerPivot. That is what we are going to create with this post.

Is this a simple one? For me, it is not. This dashboard shows Sales data from AdventureWorks database. It is not just a production report. It allows you to filter both regions with different set of criteria. The bottom data region which is an analytical grid allows you to drill-down to see the grain level of data. In addition to that, the last column with a sparkline shows the trend of sales and fifth column colors the background of the cell according to the values of the column. It is a analytical dashboard, or you can call it as an analytical report. Believe me, it did not take 15 minutes to create it, so let’s start.

Prerequisites
First thing is making sure that you are well equipped for creating this dashboard. First, you need one license product, which is Microsoft Office 2010 Professional Plus. If the operating system is not windows 7 (or server versions like 2008 R2), you need to make sure that .NET Framework 3.5 SP1 is installed too. This can be done with old OSs like Windows XP (with SP3) and Windows Vista (with SP1) too.

Next is PowerPivot. It is available for both 32-bit and 64-bit platform. However 64-bit handles more data than 32-bit with Excel 2010. One million rows or columns can be considered as a benchmark for determining whether you need 32-bit or 64-bit. So, if possible, go for 64-bit platform and analyze more than one million rows and columns.

You can download PowerPivot from here (go for instructions area for selecting the platform): http://www.microsoft.com/downloads/details.aspx?FamilyID=e081c894-e4ab-42df-8c87-4b99c1f3c49b&displaylang=en

Installing PowerPivot
Once PowerPivot is installed in your machine, it available as an add-in to your Excel. When you open Excel, you should see PowerPivot ribbon as bellow;

PowerPivot allows you to load data from any sources. It allows you to not just load 10,000 – 100,000 records, it allows you to load 100,000,000 records. It means, GBs? Yes, but do not worry, your Excel file is not going to be that much in size, it knows how to compress data. I am not sure about the compression rate, it is still unknown to me, I will update this post once I found it. Anyway, if you plan to share the Excel with others through SharePoint 2010, it should be less than 40MB because that is the maximum size can be uploaded to SharePoint site (again, this has to be further analyzed, I may be wrong on this). Anyway, Excel can compress millions of records to mega bytes, so, do not worry.

Importing Data into PowerPivot
Let’s start. Open Excel file and click on  PowerPivot menu item. Then click on PowerPivot Window which is top-first green icon in the ribbon for launching PowerPivot for Excel window (see the image above). We are going to import data from AdventureWorksDWR2. If you do not have it, still you can do this with other version of AdventureWorksDW databases. If you need to download all databases, here is is URL:

Once the PowerPivot for Excel is open, click on From Database icon in Home ribbon and select From SQL Server. You can see more icons for connections like From Report (for getting data from Reporting Services reports), From Data Feeds (for getting data from data feeds, like SharePoint Lists), From Text (for getting data from text files) and From Other Data Sources. The last one mentioned allows us to get data from various, heterogeneous data sources including third-party data sources such as Oracle, IBM DB2, Teradata. Once you clicked on From SQL Server, you have the connection dialog box for SQL Server. Fill it with required values and click on Next to continue.

Next screen gives you a selection, the way you need to import data. Let’s take the easiest way, select the first radio button and continue. Next screen allows you to select tables. Select FactInternetSales and click on Select Related Tables button. You will notice that all related tables (based on FK relationships) are selected now. Since DimProductCategory and DimProductSubCategory are not directly linked with FactInternetSales, they are not selected. Go ahead and select them too. Now we have selected 9 tables. Click Finish to import data.

Once you clicked on Close, you will see 9 sheets in PowerPivot for Excel workbook. Navigate through sheets and see. The FactInternetSales sheet has around 60,000 records, not much but we will use them.

You  might have noticed that I have shortened the Month Name in the chart. If you go through DimDate sheet, you will see that we do not have such a column, so, we have to create one. Scroll right in the sheet of DimDate and find the last column which is named as AddColumn. Double click on the cell and rename it as EnglishMonthShortName. Then select the new column and type this DAX =LEFT(DimDate[EnglishMonthName], 3) in the expression box. See how intelliSense works. And we will explore more on DAX in other posts.

Once you hit the Enter, all records will be filled with shortened name of the month.

Relationships among data imported
PowerPivot recognizes relationships defined between data tables (more accurately, data sheets). I have not tested this with data sources like Oracle or IBM DB2 but I am sure that PowerPivot is capable enough for recognizing relationships defined with them too. If you go for Design ribbon, you can see icons related to relationships. You can manage existing ones with Manage Relationships and can create new ones with Create Relationships. One thing you need to remember is, once data is loaded from many data sources, relationships can be created among sheets, regardless of the type of data source.

Creating PowerPivot charts – Dashboard
Okay, now we have imported data. Let’s create our dashboard. Click on the down-arrow in PivotTable Icon and click Chart and Table (Vertical) item.

Of course, you can start with a chart and add a table later too. Once the menu item is clicked, it asks whether you need it on new worksheet or existing worksheet. Select Existing Worksheet and continue. Now your screen is like this;

Chart
This is simple. Make sure Chart Area has been selected. Go through PowerPivot Field List and find FactInternetSales. Do following;

• Expand FactInternetSales in PowerPivot Field List to see fields in it. Drag SalesAmount and drop to Values box in PowerPivot Field List.
• Do same to TotalProductCost too.
• Find DimDate and drag EnglishMonthShortName onto Axis Fields (Categories).
• Go to Chart Area and click on TotalProductCost bar for selecting it.
• Right click on it and select Change Series Chart Type menu item.
  
• Once you have Change Chart Type window, select Line with Markers and click OK.
  
• Change the formatting such as color, font and number formats in chart elements, as you want. Finally we have this.
  

Table
Next is the Table. Select one of the cells in Table and do following with PowerPivot Field List.

• Drag SalesAmount from FactInternetSales onto Values box.
• Drag EnglishProductSubCategoryName from DimProductSubCategory onto Row Labels.
• Drag EnglishProductName from DimProduct onto Row Lables.
• Drag CalenderQuarter from DimDate onto Column Lables.
• Change the Label Column Labels in the table as Quarters.
• Select all numerical columns and format with $ icon.
• This is you table now.
  
• Let’s add another column for showing the Trend with sparklines. Select the entire Grand Total column and then click on Format Painter Icon in Home ribbon. It copies the format of the selected cells.
• Once it is clicked, mouse icon changes to Plus and Paint Brush. Click the top cell next to Grand Total. You have a new column with same formatting now.
• Now select cells in (cells that contain values ONLY) columns Quarter 1, 2, 3, and 4. We are going to spark lines for these values.
• Go to Insert ribbon and click Line icon. Create Sparklines window appears. Note that cells that contain quarterly values are added to Data Range. Now select empty cells in newly added column where you need sparklines, while cursor in Location Range.
  
• Click OK to fill the column. Once you have sparklines, while the column is selected, go to Design ribbon and select Visual Style as you want. Checking Markers checkbox makes SparkLine more meaningful.
• Let’s color the Grand Total column based on values. Green for higher values, red for lower values. Select the column (cells that contains values ONLY) and then select Conditional Formatting – Color Scales – Green Yellow Red as image shows.
  
• Done. Now do formatting on the Table as you need. We have successfully create two data regions for the dashboard.

Slicers
Slicers allows us to filer data easily. And, adding slicers to the dashboard is easy too. We are going to add three slicers to the dashboard; one for Year, and one for Country and another for Product Category. Slicers are added through PowerPivot Field List. Note that there are two slicer boxes in this; Slicer Vertical and Slicer Horizontal. There is no difference between these two but the placement of slicers.

Do not confuse with Report Filter in PowerPivot Field List. Report Filter is for setting filters for individual item, in this case, on either Chart or Table. Once it is set, filter is placed inside the region.

Follow these steps for adding slicers;

• Select chart areas (or one of data regions).
• Drag CalenderYear from DimDate onto Slicer (Vertical).
• Drag EnglishProductCategoryName from DimProductCategory onto Slicer (Vertical).
• Drag SalesTerritoryCountry from DimSalesTerrritory onto Slicer (Vertical).
• Done. Now you can click on items in slicer and see how data is changed in both Chart and Table.

Dashboard is completed. Note that we did not write a single query and did not use any other tools for creating this. We used Microsft Excel 2010 only. You can format the sheet as you want. You can color cells, background, add images, logs…etc.

This dashboard can be shared with others too. Best way of sharing this is, SharePoint 2010. Let’s see it with Part II.

Designing Database with SQL Server: Microsoft Student Champs Meeting

Last week I was invited by Madusha Nanayakkara, Microsoft Student Ambassador to deliver a session at Microsoft Student Champs Meeting. I was not sure about the knowledge level of the audience, so, prepared a presentation on things to be considered when creating a database with Microsoft SQL Server. It went very well, what a great enthusiasts! There were students from various Universities and they showed their willingness for learning about Database Management Systems. I am very glad the way they paid attention, and must thank Madusha for inviting me.

He is the link for downloading my presentation: http://rapidshare.com/files/401353206/Designing_Databases_with_SQL_Server_2008.pdf.html

Me, delivering the presentation:

OPENROWSET, OPENDATASOURCE - “Microsoft.ace.oledb.12.0” for linked server “(null)” reported error – Excel 2007, 2010

SQL Server supports distributed queries in many ways, allowing users to access heterogeneous data sources. The frequency of data access determines the way that remote data source should be set up for accessing via SQL Server. If data needs to be accessed frequently, Linked Server would be the best way. If not, distributed queries with OPENROWSET or OPENDATASOURCE can be used for accessing data.

Distributed query is a superstar for errors :). I have been using all techniques; Linked Servers, OPENQUERY, OPENROWSET, and OPENDATASOURCE and, of course, have seen enough errors too. Yesterday I faced for them again, the famous error ….for linked server “(null)”…… while I was testing codes for my students, explaining Distributed Queries.

The complete error was;

Msg 7399, Level 16, State 1, Line 1
The OLE DB Provider “Microsoft.ace.oledb.12.0” for linked server “(null)” reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider “Microsoft.ace.oledb.12.0” for linked server “(null)”.

The funny thing was, it showed the result without data but headers. Have a look on this (click on the image to see it properly);

This confirmed me that provider can access the Excel file because headers are shown. As the second error said, the problem was with fetching. As usual, googled for it and found many solution and one worked fine for me. This type of error is appeared mainly in new OSs such as Vista, Windows 7 and specially on 64-bit machine. My laptop is configured with Windows 2008 R2 – 64-bit, SQL Server 2008 64-bit and Office 2010 64-bit. It seems that the provider is not getting registered properly on these OSs. You may try with the latest version of OLE DB provider which is 14.0 but I did not try because the solution below worked for me. If you see this error, it would be better to try 14.0 before going for solution below.

Here is the link for 14.0: http://www.microsoft.com/downloads/details.aspx?familyid=C06B8369-60DD-4B64-A44B-84B371EDE16D&displaylang=en

This is what I did. I ran below code in Master database and as I said, it worked :).

USE master

GO

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1 

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1 

There is a thread, discussing this issue, here is link for it, you can read a lot on it: http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/8514b4bb-945a-423b-98fe-a4ec4d7366ea

What is $IDENTITY?

This was in one of my slides of SQL Brain Bashers presentation which was done at SQL Server User Group Meeting.

What is $IDENTITY? Everybody knows that we use IDENTITY property on columns where we need automatically incrementing identification number. The $IDENTITY does not set anything, but allows us to reference the column using it. Here is an example;

CREATE TABLE dbo.Employee

(

    EmployeeKey int IDENTITY(1,1) PRIMARY KEY

    ,FirstName varchar(50) NULL

    ,LastName varchar(50) NOT NULL

    ,

    ,

)

GO

When query for column like EmployeeKey, $IDENTITY can be used instead on column name;

-- both queries return same resultset

SELECT EmployeeKey, LastName

FROM dbo.Employee

SELECT $IDENTITY, LastName

FROM dbo.Employee

Cumulative updates for SQL Server 2008 SP1 and SQL Server 2008 R2 RTM

If you have not noticed, Microsoft had released two cumulative updates for both 2008 SP1 and 2008 R2 RTM. The cumulative update for SQL Server 2008 SP1 is here:

Update #8: http://support.microsoft.com/kb/981702/en-us

The cumulative update for SQL Server 2008 R2 RTM is here:

Update #1: http://support.microsoft.com/kb/981355/en-us

Make sure that you have updated your systems with these.

SQL Brain Bashers

Yesterday we held our SQL Server User Group meeting and I was one of the speakers. My session was supposed to be on Geospatial Visualization but had to move it into upcoming event. Finally I ended up with a presentation called SQL Brain Bashers that showed (discussed) various tricky tings related SQL Server. Seems it brushed up all of us knowledge (and had fun too), will be adding the content of the presentation with future posts.

Susantha’s session went well too, he spoke about Poweshell and how it can be used with SQL Server.

Visit http://sqlserveruniverse.com for downloading presentations and photos.

Here, me, doing my session.

Configuring Reporting Services 2008 R2 in SharePoint 2010 Integrated Mode – Part II

If you need to create an extremely user-friendly environment for Reporting Services 2008 R2, SharePoint 2010 Integrated Mode is the best solution. Configuring Reporting Services 2008 R2 in SharePoint 2010 Integrated Mode – Part 1 explains the initial configuration up to SharePoint integration. This post discusses creating a web site for Reporting Services with relevant libraries and content types, finally publishing reporting from BIDS.

Let’s start creating a Web Application. Go to SharePoint Central Administration and click Manage Web Applications under Application Management. Once Application Management is open, click on New to create a new web application.

Some of the attributed in New Web Application Form as as follows;

• Authentication
  Select Classic Mode Authentication
  
• IIS Web Site settings
  Select Create a new IIS web site option. I have named it as Report Server – 10005 and set the port as 10005.
  
• Security Configuration
  Leave default values
  
• Public URL
  In my case, URL is http://DP03:10005
  
• Application Pool
  Make sure Create new application pool is selected. I have named it as ReportServer – 10005.
  
• Database Name and Authentication
  Set the SQL Server name for Database Server. I have named the database as WSS_Content_ReportServer_10005.

Click OK to save setting. Once the process is completed, Application Created windows is appeared. Click OK to continue. New web application will be listed in Web Application window.
 
Next step is creating a site collection. Go back to Central Administration and click create site collections under Application Management. This opens Create Site Collection window. Let’s ceate one;

• Make sure that correct web application is selected (In my case, it is http://DP03:10005/.
• Add Title as Report Server.
• Let’s use the root for the Web Site Address. Do not select sites from the drop-down. Leave it blank.
• Select the Template as Team Site.
• Set both Primary Site Collection Administrator and Secondary Site Collection Administrator. Since I have everything in one machine, I set both as DP03\Administrator;.
• Click OK to create the site.
  

Once done, a message will be appeared saying Top-Level Site Successfully Created. Click on the link to go the site (In my case, link is http://dp03:10005). This is what I see;

Site needs libraries for holding reports and connections. Let’s create two libraries for the site.

• Click on Libraries link in left pane. It lists all document libraries available.
• Click the button Create for creating a new library.
• Select Document Library and Set the name as Reports.
  
• Click Create for creating the library. Once the library is open, click on Library Settings for adding content types.
   
• When Library Settings is open, library related things such as General Settings, Columns, and Views are shown. By default, it does not show Content Type. In order to enable content type, click Advanced Settings under General Settings. Once the window is open, select Yes for Allow management of content types.
  
• Click OK to save settings. Note that now the Content Type is shown under Library Settings. Click Add from existing site content types link under Content Type. This opens Add Content Types page.
• Select Report Server Content Type from Content Types From drop-down. Then move Report Builder Report from left listbox to right listbox.
  Note that if you are not seeing “Report Server Content Types”, that indicates that Add-In has not been properly installed”.
  
• Click OK to save settings. Report Builder Report content type is now appeared under Content Types of Reports library.
  
• We have to create another library for data connection. Follow same steps for creating the library. Name it as Data Connections. Then enable Allow management of content types as did for Reports library. Then add Report Data Source content type for it.
  

Now SharePoint 2010 is ready for holding reports from Reporting Services 2008 R2. Let’s try to publish some reports to this. Open Business Intelligence Management Studio and create a Reporting Services project. If you have AdventureWorks 2008 Sample Report Project, you can use it for testing. Once the project is open, get the properties of the project. Set following properties;

• TargetDataSourceFolder: http://dp03:10005/Data Connection
• TargetReportFolder: http://dp03:10005/Reports
• TargetServerURL: http://dp03:10005/

Now deploy reports and see. Reports will be published to SharePoint site we created. Once published, open the home page of the site (http://dp03:10005) and click on Reports link in left pane. It lists all the reports published.

Click one of reports and see whether it works fine. Here is the output of Company Sales 2008 report.

All done. Reporting Services 2008 R2 is integrated with SharePoint 2010. I will be exploring more on this, specially SharePoint related things. Once they are explored, I will publish them.

Update: Configuring Reporting Services 2008 R2 in SharePoint 2010 Integrated Mode – Part III

Configuring Reporting Services 2008 R2 in SharePoint 2010 Integrated Mode – Part I

Integrating Reporting Services with SharePoint is really a fun and it gives extremely user-friendly environment for Reporting Services. There are mainly two ways of integrating Reporting Services with SharePoint. If Reporting Services is installed in Native Mode, Report Explorer and Report Viewer web parts can be used for interacting with reports. When the Reporting Services is installed in SharePoint Integrated Mode, SharePoint takes overall functionalities of Reporting Services, including SharePoint features such as versioning, alerts, and enterprise search. I recently configured SQL Server 2008 R2 in SharePoint 2010 as Integrated Mode, thought to share the experience; the reason for this post. Note that this is the way I configured the integration, you may take different approach for this integration.

I started this with Windows 2008 R2 and configured SharePoint 2010 on it. See this post “Configuring Windows 2008 R2 for SharePoint 2010” if you come across any issue with the installation. When SQL Server Reporting Services is installed, make sure that you select either “Install the SharePoint integrated mode default configuration” or “Install, but do not configure the report server”.

If you need to complete the SharePoint farm installation using non-domain accounts, this post “Complete Farm SharePoint 2010 installation on Single Server using non-domain accounts” shows you the way.

Once the server is ready with SharePoint 2010 and SQL Server 2008 R2, configuration can be started. If you have selected “Install, but do not configure the report server”, you need to configure Reporting Services in SharePoint 2010 mode. Here are steps for configuring it;

• Open Reporting Services Configuration Manager and connect with Reporting Services instance.
• Make sure that Report Service status is Started. Notice that Report Server Mode is Native.
• Open the “Report Service Database” section by clicking the Database navigational tab button. It shows the SQL Server Name, Database Name, and Mode if configured. Click on Change Database button to launch Report Server Database Configuration Wizard. Here is a screenshot of it;
  
• Click Next and open the next window of the wizard. Configure the database server which will host the Reporting Services instance. Once authentication is set, click Test Connection and see whether connection can be established successfully. Click Next.
• Type the database name as ReportServer which is the default name. However you can give a names like ReportServerSharePointIntegrated too. Select the option Report Server Mode as SharePoint Integrated. Click Next to continue.
• Set credentials that will be used for connecting to the database. If everything is in one box, Service Credentials would be enough.
• When you click on Next, you will see the summary of what you have entered/selected. Continue with the wizard and complete it.
• If need to change the port of Web Service URL, go to Web Service URL section by clicking Web Service URL navigational tab button and set it. I have set the port as 20000 and my Web Service URL is http://DP03:20000/ReportServer.
  

Next step is downloading Reporting Services Add-in for SharePoint 2010. Here is the link for downloading it:http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=b3bebf9d-d86d-48cd-94e2-0639a846be80.

Once it is downloaded, start installing it. Starting screen would be this;

Complete the installation. Now all required parts are ready. Next is configuring SharePoint for Reporting Services. Open SharePoint 2010 Central Administration and click on General Application Settings link.

Once the General Application Settings is open, a section called Reporting Services can be seen which allows managing integrating settings.

The link Reporting Services Integration opens the window where Report Server Web Service URL and Authentication Mode can be set. The Report Server Web Service URL should be the URL that has been configured with Web Service URL navigational tab of Reporting Services Configuration Manager. Authentication mode can be either Windows Authentication or Trusted Authentication. Since everything is in one box, Windows Authentication would be fine with this.

In order to user Trusted Authentication, a trusted account has to be predefined. When Trusted Authentication is used, Report Server has no knowledge on the user connected to the SharePoint. Trusted account impersonates connected SharePoint user and will be used for accessing the Report Server. Trusted Authentication is usually used with Form Authentication or windows authentication without AD or Kerberos. Here is my screen;
Set Server Defaults under Reporting Services allows changing default server settings for Reporting Services. Some of this elements can be changed are; Number of Snapshots, Ad-Hoc Reporting and Client-Side Printing.

Now the integration is done. The SharePoint is fully integrated with Reporting Services. We need a hosting environment for Reporting Services. Since Reporting Services Add-In has added necessary content types for the Farm, reports can be added to any site. Or else, a new site can be created for hosting Reporting Services reports and connections. See Part II for creating a site, configuring libraries and content type, and publishing reports from BIDS.