It would be better to know these features to avoid future problems. This has two tables that show features will not be supported in next version of SQL Server and features will not be supported in future versions of SQL Server.
Here is the link: http://msdn.microsoft.com/en-us/library/ms143729.aspx
This is not just about implicit conversion. This is about implicit conversion that happens without our notice, that might hinder the performance. See the code below:
USE AdventureWorks2008
GO
DECLARE @FirstName varchar(50)
SET @FirstName = 'Gigi'
SELECT * FROM Person.Person
WHERE FirstName = @FirstName
No major issue. It returns data as we want. Enable Actual Execution Plan and run the code again. Do you see what I see?
SQL Server converts the value which is varchar into nvarchar which can be a costly operation in a certain situation. SQL Server does this because char and varchar have lower precedence than nchar and nvarchar.
This implicit can be simply overcome by making the variable as nvarchar. You can see the same when integer value is passed to a string type column that contains integer values.
Although this is a simple and not a major issue as we see, it can slow down your queries.
If you have gone through BOL for HAVING keyword, you have seen above sentence at the top of the page. What does it exactly mean? Similar question raised up during my class too; Seems like this sentence makes all of us confused :).
It says that HAVING behaves like WHERE when GROUP BY is not used. Then where we can use HAVING without using GROUP BY? You just cannot replace WHERE with HAVING. But for some extent, HAVING can be used without GROUP BY. Think a about a scenario where you need to see whether number of orders placed by customers reach to certain level, let’s say 100, if so, get the number of orders. If there is a requirement like that, you can write a T-SQL like below;
USE AdventureWorks2008
GO
SELECT COUNT(*)
FROM Sales.SalesOrderHeader
HAVING Count(*) > 100
Here we have used HAVING without GROUP BY. What has happened here is, the entire row-set is considered as a group, hence HAVING works fine. I did not come across any other places this can be used, please share with me if you have.
If you have read the article Article: Reporting Services 2008 R2: Geospatial Visualization – Part I, then here is the part II of it. It has been published at www.sql-server-performance.com. 
Here is the direct link: http://www.sql-server-performance.com/articles/biz/geospatial_visualization_ii_p1.aspx
Are you seeing many “sleeping” status connections in your SQL Server? If so, have you ever thought to find out the reason for it? Is it normal? Does it indicate some sort of a problem related connection?
This topic was discussed yesterday when I was at one of clients sites. They had seen this with their SQL Server and had already been sorted out. Understanding the standard behavior and making sure everything goes properly would help us all for making the solution efficient, hence making this post.
Sleeping means that the connection is established and not closed, not active, and waiting for a command. This is normal because most of application keep the connection even though the business is done, in order to reduce the cost of opening and closing connections. The main purpose of these connections is, reusability. For example, if an application uses connections for retrieving data, the cost of the connection establishment can be minimized if existing one can be used without creating one again. Maintaining a lot of sleeping connection is an overhead? In a way, yes it is, though it is comparatively low. If everything is properly coded, you should not see many sleeping connection. Are we seeing sleeping connection which cannot be reused? Yes, there could be, and we should avoid such situation.
Testing with a Console Application
K, Let’s see how it happens. The code given below is written as a console application. It opens a connection and executes a command. Finally it closes the connection.
1: SqlConnection sqlConnection = new SqlConnection("Server=(local);Database=TestDatabase;UId=Test;Pwd=password;");
2: sqlConnection.Open();
3:
4: SqlCommand sqlCommand = new SqlCommand("select id from dbo.Employee", sqlConnection);
5: int id = (int)sqlCommand.ExecuteScalar();
6: sqlConnection.Close();
7: Console.WriteLine("Press enter to close.");
8: Console.ReadLine();
9:
Start debugging the code. Execute up to 3rd line. Now you have opened the connection. Let’s see how SQL Server maintains this connection now. Open Query Window and run sp_who for user Test.
Note that the connection is established and it is under master database, though we made connection to TestDatabase. Now execute the .NET code up to 6th line. Do not execute the 6th line. Run sp_who again and see.
Now the connection is set for correct database and command is executed too. Still connection is maintained by SQL server and waiting for a command because SQL Server has not received instructions for closing this. Now execute the 6th line, in order to close the connection. Run sp_who again and see. Has it been removed? NO, connection is still there with the status of sleeping. The reason is ADO.NET has not instructed to remove the connection yet, hence SQL Server still maintains it. Complete the execution and see again. Now you are not going to see the connection.
Am I supposed to call “Dispose” method?
Now we have a question. We closed the connection with sqlConnection.Close() method but instruction has not gone SQL Server. Should we call Dispose()? Have sqlConnection.Dispose(); between 6th and 7th lines and run the code again. Monitor the connection with sp_who as we did with previous exercise. Check the connection once newly added line is executed (before completing the full execution). Are you still seeing the connection, even after calling Dispose method? Yes, you should see it.
What we can understand with this testing is, the connection is maintained until we completely close the application. In other words, connection is removed when the objects are garbage collected (my assumption here is, dispose method does not immediately garbage collection the object). Why is that? This is the way to achieve reusability. ADO.NET can reuse the connection, without recreating, if need it again. See the below code.
1: SqlConnection sqlConnection = new SqlConnection("Server=(local);Database=TestDatabase;UId=Test;Pwd=password;");
2: sqlConnection.Open();
3:
4: SqlCommand sqlCommand = new SqlCommand("select id from dbo.Employee", sqlConnection);
5: int id = (int)sqlCommand.ExecuteScalar();
6: sqlConnection.Close();
7:
8: sqlConnection = new SqlConnection("Server=(local);Database=TestDatabase;UId=Test;Pwd=password;");
9: sqlConnection.Open();
10:
11: sqlCommand = new SqlCommand("select 1", sqlConnection);
12: id = (int)sqlCommand.ExecuteScalar();
13: sqlConnection.Close();
14:
15: Console.WriteLine("Press enter to close.");
16: Console.ReadLine();
In this code, we make two connections. At the 9th line we open another connection. Once the 9th line completed, run sp_who again and see. Are there two connections? No, ADO.NET uses the same connection which was used for the first command. It uses connections available in the pool. Yes, we call it as Connection Pool, or the process is referred as Connection Pooling. One factor to determine whether the connection can be reused is, connection string. The connection string used for the new connection should match with existing connections in the pool. If ADO.NET cannot find a matching one, new connection will be added to the pool and it will be used for executing the command. That’s how new connections get added to the pool. That is one factor!
Connection string is same but it is not used, it adds a new one
There is another factor for adding a new connection to the pool without using an existing one. This can happen even with the same connection string. Remove 6th line from above code (closing code) and debug. Once the 9th line is executed, check for connections. This is what I see;
New connection has been created, means the one we created before cannot be used. The reason for it is, we have not closed the connection, it is as an “unusable-connection”. If the connection is not closed, it cannot be reused even though the connection string is same. So, this could be one of the reasons for seeing many sleeping connections. In this case, it is NOT normal and wasting resources in the system. If you continue with same type of codes, you will end up with many number of sleeping connections. So make sure you close your connection once the command is executed.
Making sure that connection are reused
Is there a way to see whether sleeping connections are being used? Yes, there is way. It can be monitored with SQL Server Profiler. Open SQL Server Profiler and connect with your SQL Server instance. Use the standard template which include RPC:Completed for Stored Procedures. Select all columns (You may have to uncheck selected events and check again after) and filter for your testing database. Now start the trace and run the above code again. You should see “exec sp_reset_connection” which indicates that connection is reset, in other words, it is used for the new connection.
I do not want to reuse connections
If you ever need NOT to reuse connections and get rid of sleeping connections, there is a way. All you have to do is, adding Pooling=False; to the connection string. This forces to remove the connection as soon as the close method is called. Though this is possible, it is NOT recommended.
How about ASP.NET Application?
As you have noticed, connections are removed from SQL Server once the Console Application is closed. Do not expect same from ASP.NET applications. Though you close the browser, connections are maintained with sleeping status until the connection lifetime is expired (correct me if I am wrong).
Connection removal
Connections in the pool are removed based on two factors. One is Connection Lifetime which can be set with the connection string. The default value is 0, means no specific time. Other factor is validity of the connection. If the connection in the pool does not communicate with server, it is marked as an invalid connection. The Connection pooler scans the pool periodically and sees for invalid connection. If found, they are released from the pool.
What if I create 101 connections without closing?
You might have seen, experienced this error;
Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached
The default maximum number of connection can be added to the pool is 100 and this can be adjusted. Does it mean that we can create only 100 connections without closing (or with different connection strings)? Actually NO. Have this code in ASP.NET page and run. Then check with sp_who for number of connections with sleeping status.
1: SqlConnection sqlConnection = new SqlConnection("Server=(local);Database=TestDatabase;UId=Test;Pwd=password;");
2:
3: for (int x = 0; x < 110; x++)
4: {
5: sqlConnection = new SqlConnection("Server=(local);Database=TestDatabase;UId=Test;Pwd=password;");
6: sqlConnection.Open();
7:
8: SqlCommand sqlCommand = new SqlCommand("select id from dbo.Employee", sqlConnection);
9: int id = (int)sqlCommand.ExecuteScalar();
10: }
This code creates 110 connections and guess what? It works fine. When I check with SQL Server for Test login, I can see only 87 connections. If you change the 110 as something like 150, then definitely you get the error and you will see 100 connections in SQL Server. Just to make sure that the code is working fine, I started the trace and ran the code again. I could clearly see the new connections, and “exec sp_reset_connection” too. This means that though the connection is unusable (because we have not closed), it is reset for a new connection. The factor or criteria for this behavior is still unknown for me, will update the post if the reason is found. Appreciate if you can share your thoughts on this if reason is known to you.
We have been creating reports or dashboards with different types of technologies including PerformancePoint 2007 for seeing the insight of the business. Those who have used PerformancePoint 2007 know how it supports creating dashboards with various components like scorecards and reports. Now it has been further enhanced and fully integrated with SharePoint 2010. The PerformancePoint 2010 allows us to create rich, context-driven dashboards that show the business in every angle.
This post discusses the way of configuring the PeformancePoint 2010. Let’s try to discuss the way in step by step with relevant images but limiting, as it makes the post lengthy. This post does not discuss about configuring SharePoint 2010. If need, have a look on below posts;
http://dinesql.blogspot.com/2010/03/configuring-windows-2008-r2-for.html
http://dinesql.blogspot.com/2010/04/complete-farm-sharepoint-2010.html
Web Applications
The first step is creating a web application for publishing dashboards. Open Central Administration of SharePoint 2010 and click on Manage web application under Application Management. Once it is open, click on the New button which is the first button of ribbon. New button opens the form: Create New Web Application. Set the properties as you want. I use Classic Mode Authentication because windows authentication is fine with me. If you need form-based authentication, you can select Claims Based Authentication. I name the web site as BI – 1007 and set the port as 1007. Have a new application pool for your site, I name it as BI – 2007. Point the Database Server to you SQL Server and name the database as WSS_Content_BI_2007. Set the relevant authentication for it. Leave other settings default.
Click OK to create the site. Let’s start the second step.
The second step is creating a site collection in newly created web application. Open the Central Administration and click on Application Management in the left pane. This fills right pane with set of links with groups. Click on the Create site collections link under Site Collections group. 
Now you have Create Site Collection page. Make sure that newly created web application has been selected in Web Application drop-down. In my case, it is http://dp03:1007/. Set the title as BI – Home. Next is the site address. You can have the site in the root or you can have it under manage path:sites. Let’s go ahead with the default one which the root. Since we are going to create a Business Intelligence site, it would be better to select Business Intelligence Center template under Enterprise. This template contains necessary content types for PerformancePoint object so we do not need to manually add them but if you wish, you can select a different template and add PerformancePoint content types manually too. We will use the easy way, so select the template Business Intelligence Center. Have relevant accounts for Primary Site Collection Administrator and Secondary Site Collection Administrator. 
Click OK to create the site collection.
Now the site is ready. If you visit (http://{yourserver}:1007) the site you will the home page of site as below; 
Service Applications
The next step is making sure that required services are running for PerformancePoint and the site is associated with the services. These services are set of binaries that are installed on the farm. Note that the architecture of the SharePoint 2010 is based on SSA (SharePoint Service Application), not based on SSP (Shared Service Provider) which SharePoint 2007 based on. These services can be configured to the farm as instances. Once configured, they are called as Service Application. Some of Service Applications such as User and Profile Services and Secure Store Services can be shared between farms. Service Applications such as Excel Calculation Services, Visio Web Services, and PerformancePoint Services cannot be shared between farms.
First of all we need to make sure that two services that are required for our Business Intelligence site are running. The services are: PerformancePoint Service and Secure Store Service. Open Central Administration and click on Manage services on server under System Settings. 
Once the pages is open, make sure the services are running. If not, start them. 
In order to use the started services, we need to create instances; services applications with proxies that are pointers for services applications. Go back to the Central Administration and click on Manage services applications (see above image showing manage services on server). Once the page is open, click on New button and select PerformancePoint Service Application. 
Set the name of the Service Application as PPS Service Application. The service application’s proxy can be added to the default proxy group of the farm by checking the given checkbox. You can have custom proxy groups too. Only one proxy group can be associated with a web application.
You will notice the warning sign given with the form regarding Secure Store and Unattended Service Account. This is used to store the unattended service account which will be using for authenticating the data source. Once PerformancePoint is configured, we have to configure an unattended service account. For this configuration, we need Secure Store Service Application running with its proxy.
Select Create new application pool radio button for creating new application pool for the application. Name it as PPSServiceApplication. Set an account for the pool too. 
Click the Create button for creating the service application. Once created, you should see the application and application’s proxy with status of started.
Next is creating the Secure Store Service. Follow the same steps used for creating the PerformancePoint Service Application. Name it as SS Service Application. This service application needs a database. Set the database server and name the database (or accept the default name). Select Create new application pool and enter it as SSSServerApplication. Set an account to the pool and click OK to create it. When you open Manage service application from Central Administration, you should see the applications you created and proxies. 
Now we need to configure Secure Store application for PerformancePoint application. To configure, click on SS Service Application (or you can select it and click on Manage button). Then click on Generate New Key for generating a key for the application. 
Enter the Pass Phrase and click OK. Now we need to create an unattended account for PerformancePoint application. Go back to Manage service application and click on PPS Service Application. Once the window is open, click on the PerformancePoint Service Application Settings. 
In Settings window, set an account under Unattended Service Account (which can be used for connecting your data sources) and click OK. Since PerformancePoint application always uses Secure Store in the default proxy, it will automatically connect with it. If you go back to PerformancePoint Service Application Settings, you wil see that SS Service Application appears in Secure Store Service Application input box.
If you need to see the web application created in the IIS, open IIS and see SharePoint Web Services. Unfortunately, we cannot immediately identify the service because services are named with GUIDs. If need, right click on the service and click on Explore. By looking at the content of the service, you can determine the service application. 
Service Application Associations
Before using PerformancePoint components, we need to make sure one more thing, which is Service Application Associations. We need our web application to be associated with application proxies. Go back to Manage service application and click on Application Management which is in left pane. You get a new set of links. Click on Configure service application associations under Service Applications. 
Once it is open, make sure BI – 1007 web application is associated with default proxy group that has PPS Service Application proxy and SS Service Application proxy. 
If not, click on BI – 1007 site and make the association with given window.
Done. Now we can create and publish PerformancePoint components which is our next step. Let’s see it with another post.
Discussion related to SQL Server page sizes and OS page sizes was started up while my new SQL Server class was being done. New batch is really good; they ask questions :).
SQL Server maintains data with 8KB data pages. If SQL Server has a table called dbo.Customer, based on the number of records, size of the record, data will be held on hundreds of thousands of 8KB data pages. Although SQL Server maintain 8KB pages, those pages have to be written to the disk, into 512 bytes sectors, which means, 8KB page will be broken into 16 sectors. In other words, data in a database page will be distributed among 16 sectors. SQL Server has no knowledge on the completion of this distribution (by default it assumes that 8KB is written to 16 sectors with no errors once the first sector is written [Correct me if I am wrong]). If something has happened during the write process, we will see errors when data is read back and we might not be able to find the exact problem, or exact IO path. Luckily, there is feature that checks whether those 16 pages are written properly when data is read back from the disk. Explanation of it is given below, but the question raised from my class was related to sector size. Is it 512 bytes???
Sector refers the minimum chunk of data that can be written or read to a hard drive (Read more on Sector: http://en.wikipedia.org/wiki/Disk_sector). K, back to the question, do we still see 512 bytes size sectors? I had no direct answer when the question was raised because my knowledge on hardware is bit poor. So, as usual, googled and found some info.
Here is what I found: It depends, 512 bytes was the standard size for the sector during 1956. Nowadays we get larger size of sectors such as 1KB to 4KB (or more?). In that case, for example, if SQL Server has been installed on a platform that supports 2KB sectors, one database page requires only 4 sectors.
How can we find damaged data pages if something has happened during the write process? It has to be done by PAGE_VERIFY database option. By default it is on and set to CHECKSUM. It can be set either to TORN_PAGE_DETECTION or NONE. CHECKSUM calculates the checksum value for the entire 8KB page and store it in the page header. When the page is read back from the drive, SQL Server calculates it again and matches with the previous stored value. If the value is different, it throws an error message (824). TORN_PAGE_DETECTION creates a specific bit for each sector (512 bytes or 4KB) and stores them in the database page header. These torn bits are checked when pages are read back. Error 824 will be thrown when unmatched torn bits are found. Details of the errors are logged in both SQL Server log and Windows log, allowing us to see the details of error. With most of cases, DBCC CHECKDB helps to resolves the issue.
If you have used SQL Server 2005 BPA, you know how useful it is. If I am not mistaken, there was no BPA available specifically for SQL Server 2008. Now SQL Server 2008 R2 BPA is available and it can be used for both 2008 and 2008 R2. If you have not used it before, here is the overview of the tool (quoted from the site) ;
Overview
The Microsoft SQL Server 2008 R2 BPA is a diagnostic tool that performs the following functions:
You can download and see more info here: http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=0fd439d7-4bff-4df7-a52f-9a1be8725591
Note that you need to have Microsoft Baseline Configuration Analyzer 2.0 for using this. It can be downloaded from here: http://www.microsoft.com/downloads/details.aspx?FamilyID=1b6e9026-f505-403e-84c3-a5dea704ec67&displaylang=en
If you get following error when installing;
“There is a problem with this Windows Installer package. A program run as part of the setup did not finish as expected. Contact your support personnel or package vendor.”
Get the solution from here: http://sqlblog.com/blogs/davide_mauri/archive/2010/06/21/installing-sql-server-2008-r2-best-practices-analyzer-on-a-stand-alone-machine.aspx
Once everything is set up, you can start it with Microsoft Baseline Configuration Analyzer 2.0 and select SQL Server 2008 R2 BPA as the product. Unfortunately I could not get it run as I want because it ends up with following results.
This is coming from a server which is Windows 2008 R2 (64-bit) that has SQL Server 2008 Developer Edition. Server name has been set as DINESH-PC. 
The error is;
“Analysis cannot be performed.
Issue: Invalid Instance – Instance name cannot contain spaces or special character”
My machine is name is DINESH-PC which contains “-“. Then I tried with localhost, but ended up with the error mentioned with next two screens.
This is coming from another server I had setup which is Windows 2008 R2 (64-bit) that has SQL Server 2008 R2. It is a HyperV machine named as DP01. 
The error is;
“Analysis cannot be performed.
Issue: User is not a member of Administrators group on remote machine OR Powershell Remoting is not Enabled on Remote server.”
Then I ran the scripts given with the resolution. Note that the character which is used to enclose @ is not a Apostrophe, it is Grave accent character which is aligned with Tilde. Make sure that you use it instead of apostrophe.
Anyway now both machine used for testing are ended up following error. 
“Analysis cannot be performed.
Issue: SQL Server Instance Not Exist.”
I am still looking for a solution, seems this is related to remoting and some PowerShell scripts. My knowledge on it is poor, seeking a help.
I recently configured SharePoint 2010 on Windows 2008 R2 and created couple of applications. Although everything works fine, one thing bothers me every-time that is, filling login credentials whenever I open the site. 
All I wanted to was, just open the site without re-entering the user id and the password. Should not it be intelligence enough for identifying the current user and let him (me) login to the site? Yes it should be and it does :). Actually, this is not related to SharePoint, it is IE. If you face the same issue, all you have to do is;
Truly, this is fantastic. This software allows you to visualize massive data in a nice interface with Business User’s Pet: Microsoft Excel. See more here: http://www.silverlight.net/learn/pivotviewer/
The above link has three videos that show how you can use PivotViewer, be excited :).
I am not sure whether this can straightaway link with other BI tools such as PowerPivot, and Analysis Services. But I am sure that there will be….
Microsoft Silverlight PivotViewer can be downloaded from here: http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=9a1bb862-d80c-4145-9320-b279a63bff91
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;
Table
Next is the Table. Select one of the cells in Table and do following with PowerPivot Field List.
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;
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.