SQLServerBrains

Business Intelligence : New capabilities delivered for end users in Excel 2013

2012-08-24T21:29:00.000-07:00

Ability to analyze data ranging from a few rows to hundreds of millions of rows with extreme analytical performance on your desktop using Excel data models powered by xVelocity in-memory analytics engine
Opportunity to speed up analysis in Excel by easily cleaning up and shaping your data with Flash Fill; by previewing, applying conditional formatting, creating tables, charts and PivotTables using Quick Analysis; by easily navigating multidimensional and tabular data models and creating trend charts to analyze information over time using Quick Explore.

Quick Explore enables a user to look at the product categories summarized by Sales Quantity with 1 click:

Quick Analysis suggests a Pivot Table type with a preview of actual data:

Empower users of all levels to access, mash-up and analyze data from virtually any source and rapidly create compelling analytical applications with PowerPivot.

PowerPivot Diagram View shows the data relationship:

Provide stunning data visualization to discover new insights at the speed of thought with a highly interactive and familiar data exploration, visualization, and presentation experience for users of all levels with Power View.

Interactive visualization with Power View in Excel 2013:

New capabilities delivered for IT:

At the same time, we are strengthening our IT management and oversight capability by

Enabling discovery and assessment of user-created spreadsheets with SharePoint 2013
Enabling comparing spreadsheets, tracking lineage, conducting interactive diagnostics and creating spreadsheet analysis reports with Inquire in Excel

SharePoint 2013 discovery and assessment criteria setup:

Inquire in Excel showing spreadsheet lineage and workbook analysis:

Power Pivot with Excel 2010 - Features and Getting Started

2012-08-15T23:18:00.001-07:00

PowerPivot for Excel is an add-on for Excel 2010. PowerPivot allows users to conduct powerful business intellegence (BI) in an environment that is familier. It's a free download from Microsoft and allows users to work with extremely large data sets. Before PowerPivot, this kind of analysis was limited to enterprise BI tools such as SAS and Business Objects.

PowerPivot uses an in-memory engine called VertiPaq. This new SSAS engine takes advantage of the increased RAM available in most personal computers today. Most IT shops are challenged with the resources needed to build out an enterprise BI environment. PowerPivot moves some of this work closer to the business user. While there are many features in PowerPivot for Excel.

Work With Very Large Data Sets

If you open Microsoft Excel and move to the very bottom of a worksheet, you will see that the maximum number of rows is 1,048,576. This represents about a million rows of data. With PowerPivot for Excel, there is no limit on the number of rows of data. While this is a true statement, the actual limitation is based on the version of Microsoft Excel you are running and whether you are going to publish your spreadsheet to SharePoint 2010.

If you are running the 64-bit version of Excel, PowerPivot can reportedly handle about 2 GB of data. You must have enough RAM to make this work. If you plan to publish your PowerPivot based Excel spreadsheet to SharePoint 2010, the maximize file size is also 2 GB. The bottom line is PowerPivot for Excel can handle millions of records. If you hit the maximum, you will receive a memory error.

Combine Data From Different Sources

This has to be one of the most important features in PowerPivot for Excel. Excel has always been able to handle different data sources such as SQL Server, XML, Microsoft Access and even web based data. The problem comes when you need to created relationships between different data sources. There are 3rd party products available to help with this, and you can use Excel functions like VLOOKUP to "join" data. But these methods are impractical for large data sets. PowerPivot for Excel is built to accomplish this task.

Within PowerPivot, you can import data from virtually any data source. I have found that one of the most useful data sources is a SharePoint List. I have used PowerPivot for Excel to combine data from SQL Server and a list from SharePoint. You will need SharePoint 2010 to make this work along with the ADO.Net runtime installed on the SharePoint environment.

When you connect PowerPivot to a SharePoint list, you are actually connecting to a Data Feed. To create a Data Feed from a SharePoint list, open the list and click on the List ribbon. Then click on Export as Data Feed and save it. The feed is available as a URL in PowerPivot for Excel.

There is one challenge that will be resolved by Microsoft soon. PowerPivot for Excel only supports inner joins. If you need to do an outer join, you will need to wait until an update is released from Microsoft or tweak your data so that an inner join can be used.

Create Visually Apealing Analytical Models

PowerPivot for Excel allows you to output a variety of visual data to your Excel worksheet. You can return data in a PivotTable, PivotChart, Chart and Table (horizontal and vertical), Two Charts (horizontal and vertical), Four Charts and a Flattened PivotTable. The power comes when you create a worksheet that includes multiple outputs. This provides a dashboard view of the data that makes analysis really easy. Even your executives should be able to interact with your worksheet if you build it correctly. Slicers, which shipped with Excel 2010, makes it simple to visually filter data.

Use DAX to Create Calculated Fields for Slicing and Dicing Data

DAX stands for Data Analysis Expressions and is the formula language used in PowerPivot tables. There are similarities between Excel formulas and DAX. The DAX formula language is used primarily in creating calculated columns. Check out the DAX Reference in TechNet for a complete reference.

In a regular Pivot Table in Excel that included a properly formated date field, you can use grouping to include the ability to filter or group by year, quarter, month and day. In PowerPivot, you need to create these as calculated columns to accomplish the same thing. Add a column for each way you need to filter or group data in your Pivot Table. Many of the date functions in DAX are the same as Excel formulas which makes this a snap.

For example, use =YEAR([date column]) in a new calculated column to add the year to your data set in PowerPivot. You can then use this new YEAR field as a slicer or group by in your Pivot Table.

Publish Dashboards to SharePoint 2010

If your company is like mine, dashboard are still the work of your IT team. PowerPivot when combined with SharePoint 2010 puts the power of dashboards into the hands of your users. One of the prerequisites of publishing PowerPivot driven charts and tables to SharePoint 2010 is the implementation of PowerPivot for SharePoint on your SharePoint 2010 farm

Installing PowerPivot

The PowerPivot plugin for Excel can be downloaded from the official PowerPivot site: www.powerpivot.com.

PowerPivot can be installed on both x86 and x64 systems including Windows XP sp3, Windows Vista sp1, and Windows 7. If you aren’t installing it on Windows 7, you will need to install .Net Framework 3.5 SP1.

A minimum of 1G of memory is required to run PowerPivot, but 2G is recommended. Depending on the solution, more memory for PowerPivot can increase the performance of the transformations due to its in-memory processing capabilities.

Also on the PowerPivot site is a link to a trial for Microsoft Office 2010 Professional Plus if you don’t already have it installed. If you customize the installation of the Office Suite, you need to choose at least Excel and the Office Shared Tools.

Once Microsoft Office is installed, you can download the PowerPivot install from the download page on the PowerPivot site. Locate and click on the PowerPivot install file. The add-in will actually install the next time you run Excel, at which time you will be asked to approve the installation of the PowerPivot add-in.

Creating Your First PowerPivot

Now that the PowerPivot add-in is installed, you can see that you have a new PowerPivot tab across the top of Excel. Clicking on that tab displays the PowerPivot ribbon. To select the data to use for your PowerPivot, you will need to click on the PowerPivot Window button (highlighted on the left below).

On the PowerPivot window, select Get External Data from Database -> From SQL Server. Notice that you can also get data from many other sources including data feeds, text files, and the web. In this case, however, we are going to use the AdventureWorks2008DW sample database.

In the Table Import Wizard, specify localhost for the server and AdventureWorks2008DW as the Database name. If you need to install AdventureWorks2008DW, you can find it here.

Hit Next and make sure to select “Select from a list of tables and views to choose the data to import” from the next screen.

From the table list, select the FactResellerSales table and rename it to ResellerSales in the Friendly Name column.

While you are there, click the Select Related Tables button. This selects the 7 tables in the database that have a relationship with the FactResellerSales table. Click Preview & Filter just to have a look; we won’t change anything here, though you could use it to filter out rows and columns from the model.

Click Cancel to get out of Preview & Filter and then hit Finish on the Table Import Wizard screen. This brings up the following window which displays the progress on each table import.

A Details link displays in the message column on each import line if there is information or errors regarding the import. Notice there is a detail link on final step, data preparation. Clicking that displays a popup window with information about the data preparation. Most of it is successful, but it does inform you of a few errors. For instance, self-joins are not supported in the case of employee. Fortunately, none of these affect the pivot we are creating.

Closing the Table Import Wizard reveals the model from which we will work. First, let’s tweak a few things. You see each imported table is represented on its own tab in the model. Let’s select the ResellerSales tab.

Scroll all the way to the right and click on “Add Column” in the header. For the expression, subtract TotalProductCost from SalesAmount. You can either type in the expression as it appears below, or build it as you click the columns.

Now we need to give our new computed column a better name than ComputedColumn1. Right-click on the column header and select “Rename Column” from the drop down menu. Change the name to “Profit”.

To get started on the Pivot Table, we need to switch back to the workbook itself. You can either just click on the spreadsheet in the background or you can use the toolbar button that looks like the Excel icon (next to Formatting) to switch back.

Once back in the workbook, select the PivotTable button and choose Chart and Table (Horizontal) from the menu. This allows you to create both a chart and table for your data contained on one table. The chart will be on the left and data will be on the right.

When asked for a decision on placing it in a new or existing workbook, choose the existing workbook.

The Field List panel on your right controls the contents of the pivot. Let’s begin by dragging the fields we are interested in from the list into their respective areas. First drag the amount fields for TotalProcuctCost, SalesAmount, and Profit from the ResellerSales table down to the Values list. Drag the SalesTerritoryRegion from DimSalesTerritory to the Row Labels list. Similarly, drag the ResellerName column from the DimReseller table down to the Row Labels list and drop it in below the SalesTerritoryRegion column.

Now that the report is filled in, we’ll clean it up a little. Change the header “Row Labels” to “Region\Reseller”. Change the headers over the three amount fields to remove “Sum of” from the name. Your pivot report and Field List panel should resemble the following screen shot.

It’s starting to look good, but the chart is way too busy. Maybe we should just look at one region at a time. So let’s add a horizontal slicer. From the Field List, choose SalesTerritoryRegion again and drag it to the Slicers Horizontal list. Now you see a button for each region displayed at the top of the report. Click on France. You’ll see the report and chart filter down to display just the region of France and its resellers.

Let’s further narrow down the chart by doing a manual filter using the filter button on the chart for ResellerName. From the list, first click Select All to unselect all the resellers, then pick just Accessories Network, Ace Bicycle Supply, and Atypical Bike Company. This reduces the Resellers displayed on the chart to just these three. Note that if we would have selected resellers not from France (our existing Region filter), the chart would be empty.

The resulting chart is much easier to read having just the three resellers of interest graphed.

We are not quite done. It’s bothering me that our amounts have more than 2 decimal places, so let’s take care of that real quick. Select all the data for the three amount columns and when the formatting menu appears, select the $ icon as shown below.

And now, the final product. Congratulations on completing your first PowerPivot report.

Views and its types in SQL Server 2008

2012-08-10T10:22:00.001-07:00

A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.
You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table.

SQL Server 2008 organizes user-defined views into three major categories:

Standard Views: Most of the time, we create this kind of view where we use single or more that one tables, use JOINs and other clauses and pull the data out. The tables are saved on a single physical file, most of the time on primary data file – that is .mdf file. All tables don’t have any kind of partitions – I’ll discuss partitions in Partitions Views section below. So, most of views that we write come under this category.
Indexed Views: Usually views pull the data out from underlying tables and utilize indexes created on table columns. However, an indexed view allows us to create a unique clustered index on it – allowing us to physically keep a separate copy of sorted data based on which column we created a unique clusetered index. This dramatically improves the view’s performance for some types of queries. An indexed view works best for queries that are giving summary data. However, this type of view is not recommended in case there is high frequencyof data insert, update or delete on underlying table(s).
Partitioned Views:This type of view joins horizontally partitioned data from a set of members table across one or more servers. We partition a table horizontally and keep data partitions either on one server or on multiple servers (federated servers) in order to gain performance. We may sub-categorize this category into two:

Local Partitioned Views: This joins required tables on the same instance of SQL Server ie. same SQL Server engine.
Distributed Partitioned Views: This joins required tables across servers i.e. multiple SQL Server engines.

Standard Views:

In SQL Server a view represents a virtual table. You can say it is a subset of a table. Just like a real table, a view consists of rows with columns, and we can retrieve data from a view (sometimes even update data in a view). The fields in the view's virtual table are the fields of one or more real tables in the database.

Creating a view, by example

The following is an example of a simple SQL Server view using the Student table. Note: You may have to update some of the rows to add data to see the effects of this view.

IF ( OBJECT_ID('dbo.vw_students1') IS NOT NULL ) 
   DROP VIEW dbo.vw_students1 
GO

CREATE VIEW dbo.vw_students1 
AS
  SELECT 
    lastname    , 
    firstname   , 
    (firstname + ' ' + lastname) as "Fullname_fl",
    (lastname + ', ' + firstname) as "Fullname_lf",
    birth_dttm,  
    DATEDIFF(yy, birth_dttm, GETDATE()) - 
    CASE 
       WHEN MONTH(birth_dttm) > MONTH(GETDATE()) 
            OR 
            (MONTH(birth_dttm) = MONTH(GETDATE()) 
             AND DAY(birth_dttm) > DAY(GETDATE())
            ) 
    THEN 1 ELSE 0 
    END as "Age"  
    FROM   Students        
GO

In general, you should adopt some naming standard for your views. This standard is vw_<name of view>. The name should be somewhat reflective of the purpose of the view. You can clearly see that I did not do such a good job with this. Sometimes coming up with a short descriptive name for your view is easier said than done.

The syntax for creating a view is...

CREATE OR REPLACE VIEW `<your_view_name>`

...followed by a normal SQL SELECT. This SELECT can include a WHERE clause or anything else for that matter that can be put into a SELECT statement. The scenarios are endless. It really depends on the purpose of the view.

As you can see in our view we are formatting the first and last name. This is a pretty common thing to do, By having a view that already does this we save having to write that function in every query where this is a requirement. You can also see that we have take the birth date column and calculated age.

Executing a View

Execute an SQL View

The example below shows all of the code from the view. You could also do a SELECT *, or further restrict the columns you want to see. You can also add additional row restriction to the view as we have done.

SELECT  TOP 100
        lastname     , 
        firstname    , 
        Fullname_fl  , 
        Fullname_lf  , 
        birth_dttm   , 
        Age         
FROM    dbo.vw_students1 (NOLOCK) 
WHERE Age is not null
GO

Indexed View


On the surface, an indexed view is the same thing as a normal view, however the critical difference is that an indexed view actually allows you to create a clustered index on it, effectively working around the "one clustered index per table" limitation. The downside to this is that the indexed view is a full copy of the data from the underlying tables, so for space considerations you will need to take this into account when using them.

Let's look first at how to create a normal view, so that we have something to compare to as we create an indexed view

create view ContactsView as 
( 
      select [Users].[FirstName], [Users].[LastName], [Contacts].* 
        from [Users] 
       inner join [Contacts] on [Contacts].[UserID] = [Users].[UserId] 
);

What this will do is create a pretty flexible view that will only pull the first and last name of the user from the users table, and all of the columns from the Contacts table and automatically pick up any new columns that are added to the Contacts table, with no further modifications.  Indexed views require a more rigid definition of what they will return.  For this reason, we cannot use a wildcard (*) in the underlying query.  Each column must be individually called out; also, each table named in the query must be in 2 part dot notation, referencing both the schema of the table and the table name itself, and the view must be declared with schemabinding enabled for it.





 Below is the modified query to create a view capable of being an indexed view:


create view [dbo].[OrganizationContactsView] with schemabinding as 
( 
      select [FirstName], [LastName], [Contacts].[UserID],[Contacts].[ContactDescription], [Contacts].[IsPublic] 
        from [dbo].[Contacts] 
       inner join [dbo].[Users] on [Contacts].[UserID] = [Users].[UserId] 
 );

 Now, we need to create the clustered index on it.  Clustered indexes on a view must be unique, otherwise you will not be able to create one; nonclustered indexes will work fine.  For the present example, a UserId will only be in the contacts table once per Organization, so we can make a unique clustered index spanning the OrgId and UserId columns, like this:




create unique clustered index IX_OrganizationContactsView_OrgId on[OrganizationContactsView](OrgId,UserId)




Partitioned views




Partitioned views are used to access data that has been horizontally split, or partitioned, across multiple tables. These tables can be in the same or different databases—or even spread across multiple servers. Partitioning of tables is done to spread the I/O and processing load of large tables across multiple disks or servers.




You combine the tables in a partitioned view by using a UNION ALL statement that causes the data from the separate tables to appear as if they were one table. These separate tables are referred to as member tables or base tables. The member tables in a SELECT statement of the view must all be structured in the same way, and the view must adhere to the following restrictions:


All the columns from the member tables should be included in the view definition.


Columns with the same ordinal position in the SELECT list should have the same data type.


The same column cannot be used multiple times in the SELECT list.


A partitioning column that segments the data must be identified and needs to have the same ordinal position across all the member table SELECT statements.


The partitioning column cannot be a computed column, an identity, a default, or a time stamp.


The data values in the partitioning column cannot overlap in the underlying tables.


The partitioning column must be part of the primary key of the member table.


The member tables in the partitioned view need a CHECK constraint on the partitioning column.


A table can appear only once as part of the UNION ALL statement.


The member tables cannot have indexes created on computed columns in the table.


The number of columns in the member table primary key constraints should be the same.


All member tables should have the same ANSI PADDING setting when created.



The list of restrictions for creating partitioned views is extensive, but the creation of a partitioned view is relatively straightforward and intuitive. Consider, for example, the Sales.SalesOrderHeader table in the Adventureworks2008database. This table is relatively small, but it is the type of table that could have a large number of rows and experience heavy utilization. To balance the workload against this table, you could use a partitioned view that utilizes base tables that each contain a separate year’s data. Listing 1 shows the CREATE TABLE statements to create the base tables for each year. The yearly tables are intended to hold summarized daily numbers, and each contains only a subset of the columns in the Sales.SalesOrderHeader table.


Listing 1. Creating the Base Tables for a Partitioned View

CREATE TABLE Sales.Sales_2001
(
    OrderDay datetime NOT NULL
        CHECK (OrderDay BETWEEN '20010101' AND '20011231'),
    SubTotal money NOT NULL ,
    TaxAmt money not null,
    Freight money not null,
 CONSTRAINT PK_Sales_2001_OrderDay PRIMARY KEY CLUSTERED (OrderDay ASC)
)

CREATE TABLE Sales.Sales_2002
(
    OrderDay datetime NOT NULL,
        CHECK (OrderDay BETWEEN '20020101' AND '20021231'),
    SubTotal money NOT NULL ,
    TaxAmt money not null,
    Freight money not null,
 CONSTRAINT PK_Sales_2002_OrderDay PRIMARY KEY CLUSTERED (OrderDay ASC)
)

CREATE TABLE Sales.Sales_2003
(
    OrderDay datetime NOT NULL
        CHECK (OrderDay BETWEEN '20030101' AND '20031231'),
    SubTotal money NOT NULL ,
    TaxAmt money not null,
    Freight money not null,
 CONSTRAINT PK_Sales_2003_OrderDay PRIMARY KEY CLUSTERED (OrderDay ASC)
)

CREATE TABLE Sales.Sales_2004
(
    OrderDay datetime NOT NULL
        CHECK (OrderDay BETWEEN '20040101' AND '20041231'),
    SubTotal money NOT NULL ,
    TaxAmt money not null,
    Freight money not null,
 CONSTRAINT PK_Sales_2004_OrderDay PRIMARY KEY CLUSTERED (OrderDay ASC)
)

Notice that each table has a primary key on OrderDay, the partitioning column. Also notice that a CHECK constraint is defined for each table; it ensures that only orders for the given year can be stored in the table.



To demonstrate the power of a partitioned view, it is best to populate the base tables that will be used by the view.Listing 2 contains a series of INSERT statements that select from the Sales.SalesOrderHeader table and populate the base tables. The SELECT statements summarize several key columns by day and contain a WHERE clause that limits the result to orders for the respective years.


Listing 2. Populating the Base Tables for a Partitioned View
INSERT Sales.Sales_2001
   SELECT CONVERT(VARCHAR(8),OrderDate,112),
      SUM(SubTotal), SUM(TaxAmt), SUM(Freight)
    FROM Sales.SalesOrderHeader
    WHERE OrderDate between '20010101' AND '20011231'
    GROUP BY CONVERT(VARCHAR(8),OrderDate,112)
INSERT Sales.Sales_2002
   SELECT CONVERT(VARCHAR(8),OrderDate,112),
      SUM(SubTotal), SUM(TaxAmt), SUM(Freight)
    FROM Sales.SalesOrderHeader
    WHERE OrderDate between '20020102' AND '20021231'
    GROUP BY CONVERT(VARCHAR(8),OrderDate,112)

INSERT Sales.Sales_2003
   SELECT CONVERT(VARCHAR(8),OrderDate,112),
      SUM(SubTotal), SUM(TaxAmt), SUM(Freight)
    FROM Sales.SalesOrderHeader
    WHERE OrderDate between '20030101' AND '20031231'
    GROUP BY CONVERT(VARCHAR(8),OrderDate,112)

INSERT Sales.Sales_2004
   SELECT CONVERT(VARCHAR(8),OrderDate,112),
      SUM(SubTotal), SUM(TaxAmt), SUM(Freight)
    FROM Sales.SalesOrderHeader
    WHERE OrderDate between '20040102' AND '20041231'
    GROUP BY CONVERT(VARCHAR(8),OrderDate,112)



Now that you have the populated base table, you can create a partitioned view and ensure that the view is selecting only from the base tables that it needs.

Two types of partitioned views are discussed in this article: local and distributed. A local partitioned view utilizes base tables found on the same server. A distributed partitioned view contains at least one base table that resides on a different (remote) server. The focus in the section is on local partitioned views. The T-SQL for creating a local partitioned view named Sales.vw_Sales_Daily is shown in Listing 3.


Listing 3. Creating a Local Partitioned View
Create View Sales.vw_Sales_Daily
 as
      SELECT * FROM Sales.Sales_2001
       UNION ALL
      SELECT * FROM Sales.Sales_2002
       UNION ALL
      SELECT * FROM Sales.Sales_2003
       UNION ALL
      SELECT * FROM Sales.Sales_2004


The best way to validate that a partitioned view is working properly is to run a conditional SELECT against the view and display the execution plan. If the partitioned view is functioning properly, it should be accessing only the base tables it needs to satisfy the SELECT and should not access all the tables in the view unless it needs to. The following example shows a sample SELECT against the new partitioned view:




SELECT * FROM Sales.vw_Sales_Daily
 WHERE OrderDay > '20040701'
   and SubTotal > 2000




If you execute this statement and review the actual execution plan, you see that an index seek is performed against the Sales.Sales_2004 table. This is the correct result, given that the SELECT statement is targeting order data from 2004.


Modifying Data Through a Partitioned View


You can modify data via a partitioned view if the SQL statement performing the modification meets certain conditions, as described here:


All columns in the partitioned view must be specified in the INSERT statement. Columns that include a DEFAULT constraint or allow nulls are also subject to this requirement.


The DEFAULT keyword cannot be used on inserts to partitioned views or on updates to partitioned views.


UPDATE statements cannot modify PRIMARY KEY columns if the member tables have text, ntext, or image columns.


Inserts and updates to a partitioned view are not allowed if the view contains a time stamp.


Identity columns in a partitioned view cannot be modified by an INSERT or UPDATE statement.


INSERT, UPDATE, and DELETE statements are not allowed against a partitioned view if there is a self-join with the same view or with any of the member tables in the statement.




Note

Data can be modified through partitioned views only in the Enterprise and Developer Editions of SQL Server 2008.


In addition to the conditions shown in this list, you must also satisfy any restrictions that apply to the member tables. Check constraints, foreign key constraints, and any other table-level restrictions must be accounted for in the modification statement. The user executing the modification against the partitioned view must have the appropriate INSERT, UPDATE, or DELETE permissions on the member tables for the update to succeed.


Distributed Partitioned Views


Microsoft provides distributed partitioned views (DPVs) as a primary means to scale out a database server. Scalability allows an application or a database to utilize additional resources, which allows it to perform more work. There are two kinds of scalability: scaleup and scaleout. A scaleup solution focuses on a single server scaled to provide more processing power than its predecessor. An example of scaleup would be migrating from a server with a single dual-core processor to a machine with 4-quad-core processor. Scaleout solutions include the addition of servers to augment the overall processing power.

DPVs are similar to local partitioned views, but they utilize one or more tables located on a remote server. The placement of partitioned data on remote servers allows the processing power of more than one server to be utilized. The partitioning is intended to be transparent to the application and allow for additional partitions and servers as the application’s needs scale.

The following list outlines the basic requirements for creating a DPV:


A linked server definition is added to each member server that will contain the partitioned data. The linked server contains the connection information required to run distributed queries on another member server.


The lazy schema validation option is set to true on each of the member servers, using sp_serveroption. This option is set for performance reasons and allows the query processor to skip schema checking of remote tables if the query can be satisfied on a single member server.


A DPV is created on each member server. This DPV references the local tables in addition to the tables found on the other member servers.



Listing 3 shows SQL commands that can be used to satisfy the requirements in the preceding list. The DPV created in the last portion of the script is similar to the local partitioned view created in the previous section. The key difference in this DPV example is the inclusion of a distributed query that retrieves records for Sales.Sales_2002from a remote server. The remote server in this example is named DbSvrXP.


Listing 3. Creating a Distributed Partitioned View
Exec sp_addlinkedserver @server='dbsvrxp',
      @srvproduct='',
      @provider='MSDASQL',
      @provstr='DRIVER={SQL Server};
SERVER=dbsvrxp;UID=linklogin;PWD=pw;Initial Catalog=Adventureworks2008'
—Set the server option for improved DPV performance
exec sp_serveroption dbsvrxp, 'lazy schema validation', true

Create View Sales.vw_Sales_Daily
 as
      SELECT * FROM Sales.Sales_2001
       UNION ALL
      SELECT * FROM dbsvrxp.Adventureworks2008.Sales.Sales_2002
       UNION ALL
      SELECT * FROM Sales.Sales_2003
       UNION ALL
      SELECT * FROM Sales.Sales_2004


The DPV created in Listing 3 contains only one remote table. The example could be further expanded to have each table in the UNION clause on a different remote server. Keep in mind that the DPV CREATE statement needs to be adjusted when run on the remote server(s). The tables that are local on one server are now remote on the other server, and those that are remote can now be local.

If the DPVs are properly defined, SQL Server 2008 attempts to optimize their performance by minimizing the amount of data transferred between member servers. The query processor retrieves the CHECK constraint definitions from each member table. This allows the query processor to map the specified search arguments to the appropriate table(s). The query execution plan then accesses only the necessary tables and retrieves only the remote rows needed to complete the SQL statement.

Data can be modified through a DPV as well. Updatable DPVs, which were introduced in SQL Server 2000, are still available in SQL Server 2008. Data modifications are performed against a view, allowing true transparency. The view is accessed as if it were a base table, and the user or application is unaware of the actual location of the data. If it is configured properly, SQL Server determines via the WHERE clause specified in the update query which partition defined in the view must be updated rather than updating all tables in the join.


Note

Data can be modified through distributed partitioned views only in the Enterprise and Developer Editions of SQL Server 2008

Partitioning in SQL Server 2008

2012-08-09T03:10:00.001-07:00

Partitioning is a feature designed to improve the performance of queries made against a very large table. It works by having more than one subset of data for the same table. All the rows are not directly stored in the table, but they are distributed in different partitions of this table. When you query the table looking for data in a single partition, or just a few, then due to the presence of these different subsets, you should receive a quicker response from the server.

There has been a long history, in SQL Server, as this feature has evolved. It first started with partitioned views. A developer had to create different tables having same schema and use these tables through a UNION operation in a view. Now after 2005, your tables and indexes can also be partitioned. This feature is further improved upon in SQL Server 2008.

SQL Server only supports one type of partitioning, which is Range Partitions. More specifically I should say 'Horizontal Range Partitions'. This the partitioning strategy in which data is partitioned based on the range that the value of a particular field falls in. The other partitioning types are reference, hash, list etc. partitions, which are not supported currently in SQL Server.

Simply saying, when we partition a table, we define on which portion of a table a particular row will be stored. Now you must be wondering what would be the criterion that a specific row would be saved in a particular partition. There are actually rules defined for ranges of data to fill a particular partition. These ranges are based on a particular column; this is called the Partition Key. It should be noted that these ranges are non-overlapping. To achieve this, a Partition Function and a Partition Scheme is defined.

There might be many questions in your mind. Let's go step by step. We start with the terminologies used in Partitioning.

PARTITION FUNCTIONS

The Partition Function is the function that defines the number of partitions. This is the first step in the implementation of partitioning for your database object. One partition function can be used to partition a number of objects. The type of partition is also specified in the partition function, which currently can only be 'RANGE'.

Based on the fact about boundary values for partitions that which partition they should belong to, we can divide partition function into two types:

Left: The first value is the maximum value of the first partition.
Right: The first value is the minimum value of the second partition.

The syntax for the creation of a partition function is as follows:

CREATE PARTITION FUNCTION partition_function_name ( input_parameter_type )
AS RANGE [ LEFT | RIGHT ]
FOR VALUES ( [ boundary_value [ ,...n ] ] ) [ ; ]

e.g.
Generally the input_parameter_type is numeric. SQL Server supports only certain input_parameter_types. The list of partition functions defined can be obtained by using the SYS.PARTITION_FUNCTIONS catalog view:

SELECT * FROM sys.partition_functions

The first thing that you would want to do is to test whether your partition function is implemented as per your desire or not. Specially, we can check if it is working on boundary values. You can check it with the special function provided: $Partition. We test the partition function MyPartitionFunc2 created by us earlier. In this SQL, we are verifying to which partition, (Partition Key = 100), would belong to.

SELECT $PARTITION.MYPARTITIONFUNC2(100) [PARTITION NUMBER]

To find out the boundary values defined by partition functions, partition_range_values catalog view is available.

SELECT * FROM SYS.PARTITION_RANGE_VALUES

Though SQL Server does not directly support List Partitioning, you can create list partitions by tricking the partition function to specify the values with the LEFT clause. After that, put a CHECK constraint on the table, so that no other values are allowed to be inserted in the table specifying Partition Key column any value other than the 'list' of values.

PARTITION SCHEME

This is the physical storage scheme that will be followed by the partition. To define scheme, different file groups are specified, which would be occupied by each partition. It must be remembered that all partitions may also be defined with only one file group.

After the definition of a partition function, a partition scheme is defined. The partition scheme just like specifying an alignment for data i.e. it specifies the specific file groups used during partitioning an object. Though it is possible to create all partitions on PRIMARY but it would be best if these different partitions are stored in a separate file groups. This gives some performance improvement even in the case of single core computers. It would be best if these file groups are on different discs on a multi core processing machine.

The syntax for creating partition schema is as follows:

CREATE PARTITION SCHEME partition_scheme_name
AS PARTITION partition_function_name
[ ALL ] TO ( { file_group_name | [ PRIMARY ] } [ ,...n ] )
[ ; ]

The list of partition schemes can be obtained by using the SYS.PARTITION_SCHEMES.

SELECT * FROM SYS.PARTITION_SCHEMES

To get the list of all data spaces SYS.DATASPACES catalog view may be used:

SELECT * FROM SYS.DATA_SPACES

PARTITIONED TABLE

After creation of a partition scheme, a table may be defined to follow that scheme. In this case the table is called PARTITIONED. A partitioned table may have a partitioned index. Partition aligned index views may also be created for this table. These index and view may be based on different partition strategy (partition function and partition scheme).

There may be question in your mind if it is possible to partition your table using multiple columns. The answer may be YES or NO. Why? No, because there is no such direct support for this in SQL Server. Yes, because you can still do that by using persisted computed column based on any number of columns you want. It must be remembered that this is still a single dimension partition.

Now you might be wondering whether your existing tables could be partitioned or not. For partitioning your existing table just drop the clustered index on your table and recreate it on the required partition scheme.

CREATE TABLE
[ database_name . [ schema_name ] . | schema_name . ] table_name
( { <column_definition> | <computed_column_definition> }
[ <table_constraint> ] [ ,...n ] )
[ ON { partition_scheme_name ( partition_column_name ) | filegroup
| "default" } ]
[ { TEXTIMAGE_ON { filegroup | "default" } ] [ ; ]

SQL Server 2008 has introduced an exciting new feature, 'FileStream'. Remember if you have any column specified which is based on filestream data, you have to follow some additional steps to get things to work with partitioning.

PARTITIONED INDEX

We can also partition our indexes, and this should contribute to improved performance. If indexes are partitioned they serve as the local indexes for each partition. We can also go with Global indexes on a partitioned table without caring about the different partitions of the table.

It must be remembered that indexes can be partitioned using a different partition key than the table. The index can also have different numbers of partitions than the table. We cannot, however, partition the clustered index differently from the table. To partition an index, ON clause is used, specifying the partition scheme along with the column when creating the index:

CREATE NONCLUSTERED INDEX MyNonClusteredIndex ON dbo.MyTable(MyID1)
ON MyPartitionScheme(MyID2);

You can see that Mytable is indexed on the MyID1 column, but the index is partitioned on the MyID2 column. If your table and index use the same Partition function then they are called Aligned. If they go further and also use the same partition scheme as well, then they are called Storage Aligned (note this in the figure below). If you use the same partition function for partitioning index as used by the table, then generally performance is improved.

PARTITION ALIGNED INDEX VIEWS

Partition aligned index views allow to efficiently create and manage summary aggregates in relational data. The query results are materialized immediately and persisted in physical storage in the database. This is an extension of Indexed views which existed in SQL Server 2005. Earlier, it was difficult to manage index views on partitioned table. This is because switching in and out the partition was not possible as the data was not distributed in a partitioned way in the indexed view. Indexed views were required to be dropped before this operation. In SQL Server 2008, this became possible with the introduction of Partition Aligned Index Views. In this way Indexed views have now evolved to become 'Partition Aware'.

It is said that these types of index views increase the speed and efficiency of queries on the partitioned data. The following conditions should be true if an index view has to be partition aligned with the table on which this view is defined.

The partition functions of the indexes of the indexed view and table must define the same number of partitions, their boundary values and the partition must be based on the same column.
The projection list of the view definition includes the partitioning column (as opposed to an expression that includes the partitioning column) of the partitioned table.
Where the view definition performs a grouping, the partitioning column is one of the grouping columns included in the view definition.
Where the view references several tables (using joins, sub queries, functions, and so on), the indexed view is partition-aligned with only one of the partitioned tables.

These views can be local or distributed. The local partitioned index view is the one in which all partitions lie on the same SQL Server instance. For a distributed one, different partitions of tables, queried in single view, reside on different SQL Server instances across the network. The distributed partitioned views are specially used to support federation of SQL Server instances.

CREATE A PRACTICE DATABASE

Before looking at an example implementation of partitioning, let us create a new database to test this feature. Let us create a database with two file groups FG1 and FG2. Make sure that you create PartitionPractice folder in C directory before running the following query:

USE master
GO
CREATE DATABASE MyPartitionPracticeDB
ON PRIMARY
( NAME = db_dat,
FILENAME = 'c:\PartitionPractice\db.mdf',
SIZE = 4MB),
FILEGROUP FG1
( NAME = FG1_dat,
FILENAME = 'c:\PartitionPractice\FG1.ndf',
SIZE = 2MB),
FILEGROUP FG2
( NAME = FG2_dat,
FILENAME = 'c:\PartitionPractice\FG2.ndf',
SIZE = 2MB)
LOG ON
( NAME = db_log,
FILENAME = 'c:\PartitionPractice\log.ndf',
SIZE = 2MB,
FILEGROWTH = 10% );
GO
USE MyPartitionPracticeDB
GO
1. STEP # 01 (Create partition function)

CREATE PARTITION FUNCTION partfunc (int) AS
RANGE LEFT FOR VALUES (1000, 2000, 3000, 4000, 5000);
2. STEP # 02 (Create partition scheme)

CREATE PARTITION SCHEME MyPartitionScheme AS
PARTITION MyPartfunc TO
([FG1], [FG2])
GO

3. STEP # 03 (Create table or index based on the partition scheme)

The important clause related to partitioning is ON clause in CREATE TABLE statement. We create our table MyPartitionedTable as follows:

CREATE TABLE MyPartionedTable
(
ID int PRIMARY KEY,
Name VARCHAR(50)
)
ON MyPartitionScheme(ID)

GUI SUPPORT FOR PARTITIONING

Great GUI support is provided in SQL Server 2008 for partitioning. If you see the properties of a table, you can easily find partition related properties under the Storage tab.

There is no support of partitioning at the time of table creation, but later you can use the wizard for partitioning. There is a new partitioning wizard introduced in SQL Server 2008 Management Studio.

CREATE PARTITIONS

After the creation of a non-partitioned table, it can then be partitioned. To ensure the ease of partitioning, GUI support is available in SQL Server Management Studio. Not only can you create partitions in a non-partitioned table but you can also manage partitions in an already partitioned table.

Just right click a non-partitioned table and look at the following pop-up menu i.e. the Create Partition option under Storage menu:

When you select this option, an easy to use wizard is started to allow you to create partitions. Remember, you can also use already existing partition functions and scheme to partition a table. You can also create new ones and the wizard allows you to enter the details for the partition function, it allows the LEFT or RIGHT options, and for entering the details of the partition scheme. It also asks for the details of file groups for new partitions.

This wizard is very easy to use and allows the developer to generate partition functions, partition schemes and other options if none of the already existing ones fits the user's needs. There are also some third party solutions available to manage partitioning of your objects outside SQL Server Management Studio.

ADDITIONAL OPERATIONS WITH PARTITIONS

SQL Server also supports other operations with partitions. These operations help in managing a partitioned object. They are as follows:

1. Split
2. Merge
3. Switch

Remember that these operations are meta data operations and do not involve any movement of data.

ADDING A NEW PARTITION

This operation is supported to accommodate the continuous changes when a new Partition needs to be added if the already existing partitions are very large. This is technically called a Partition Split. ALTER TABLE statements support partition split with the required options. To split a partition, the ALTER PARTITION FUNCTION statement is used.

ALTER PARTITION FUNCTION MyPartitionFunction()
SPLIT RANGE (5000)

But before splitting, a new file group must be created and added to the partition scheme using the partition function being modified, otherwise, this statement would cause an error while executing.

REMOVING AN EXISTING PARTITION

The Merge operation is used to remove an existing partition. The syntax of MERGE statement is nearly the same as the SPLIT statement. This statement would remove the partition created in SPLIT command shown before this.

ALTER PARTITION FUNCTION MyPartitionFunction ()
MERGE RANGE (5000)

SWITCHING A PARTITION

This operation is used to switch a partition in or out of a partitioned table. It must be remembered that for switching in, the already existing partition must be empty within the destination table. In the following example, we are switching in a table MyNewPartition as 4th partition to MyPartitionedTable.

ALTER TABLE MyNewPartTable switch TO MyPartitionedTable PARTITION

In the following example we are switching partition 3 out to a table MyPartTable:

ALTER TABLE MyPartitionedTable switch PARTITION 4 TO MyNewPartTable

We partitioned our table because of large amount of data. In order to speed up data retrieval, we have incorporated several indexes on our table. This would certainly help in satisfying the query requirements of users, but it adds additional time while updating or adding records to the table given the large amount of data in the table. So it is better that we insert our records in an empty table, which is exactly same as the partitioned table. Insert new records in that table and then switch that partition into the partitioned table. We discuss this further as we will be discussing the Sliding Window Technique.

The requirement for the table when switching in and out of a partition means that both of them must also have same clustered and non-clustered indexes, and additionally, the same constraints.

PARTITIONING AND PARALLEL EXECUTION:

If you have multiple processing cores on your server then partitioning your large tables could also result in more optimized parallel execution plans by the database engine. It must be remembered that in SQL Server 2005, a single thread was created per partition (at max). So if there are less number of partitions then the number of processors, all the cores were not optimally utilized and some processors used to be sitting idle. With SQL Server 2008, this restriction has been removed. Now all processors can be used to satisfy the query requirement.

When SQL Server uses parallel execution plan for partition tables, you can see Parallelism operator in the execution plan of the query. In actual there are multiple threads working on different partitions of the table. Each partition is processed by a single thread. We can control parallel plan of our queries by using MAXDOP hint as part of query or plan guide. First we see how we could control the degree of parallelism using query hints:

SELECT * FROM MyPartitionedTable
ORDER BY OrderDate DESC
OPTION (MAXDOP 4)

The plan can also be created to support parallelism:

EXEC sp_create_plan_guide
@name = N'Guide1',
@stmt = N'SELECT * FROM MyPartitionedTable
ORDER BY OrderDate DESC',
@type = N'SQL',
@module_or_batch = NULL,
@params = NULL,
@hints = N'OPTION (MAXDOP 4)';

Remember if you are creating a custom plan guide for your query then you must not have specified (Maximum Degree Of Parallelism) MAXDOP = 1 as an option. Additionally the configuration option of maximum degree of parallelism for the server should be appropriately set.

sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
sp_configure 'max degree of parallelism', 0;
GO
RECONFIGURE WITH OVERRIDE;
GO

You can also set the configuration settings of the instance from the Properties window of the instance selected from the object explorer in the SQL Server Management Studio.

MULTITHREADED PARTITION ACCESS

Threading support for partitions has also been improved in SQL Server 2008. Earlier in 2005 one thread was assigned to each partition. So if there are e.g. 40 threads available and table has only 2 partitions then only 2 threads may be utilized for accessing these partitions' data. In 2008, all the partitions are accessed by all the available threads. These threads access table partitions in a round robin fashion.

You need to make sure that the 'max worker thread' configuration is properly set for your SQL Server instance.

SELECT * FROM SYS.CONFIGURATIONS
WHERE NAME = 'max worker threads'

This feature is also important in the case of non-partitioned table. This is because back in 2005 if we had large amounts of data in a non-partitioned table then only one thread was available to it. Now in 2008 all the available thread could utilize it which results in boosting the performance of data access.

Many operations can be done in parallel due to partitioning like loading data, backup and recovery and query processing.

SLIDING WINDOW TECHNIQUE

SQL Server cannot have an unlimited number of partitions. The limitation is 1000 partitions in 2005. Based on this limitation we cannot switch in partitions forever. So, how to resolve this? The Sliding Window Technique is the answer.

According to this technique, a table always has some specified number of partitions. As the new partition comes, the oldest partition is switched out of the table and archived. This process can goes on forever. Additionally, this should be very fast to do since it is a metadata operation.

This technique could give a drastic boost in performance based on the fact that so long as data is being loaded into the source staging table, your partitioned table is not locked at all. You can even improve that using bulk inserts. After it is loaded the switching in process is basically a metadata operation.

This process is so regular that you can automate this using dynamic SQL. The tasks to be followed are as follows:

Managing staging tables for switching in and out the partitions.
Switching new partition in and old partition out of the table.
Archive staging table in which data is archived and remove both staging tables.

Partitioning a SQL Server Database Table - Basics

2012-08-09T01:55:00.000-07:00

Partitioning a SQL Server database table is a three-step process:

Create the partition function
Create the partition scheme
Partition the table

The remainder of this article explores each of those steps in further detail.

Step 1: Creating a Partition Function

The partition function defines [u]how[/u] you want SQL Server to partition the data. At this point, we’re not concerned with any particular table, we’re just generically defining a technique for splitting data.

We define the partitions by specifying the boundaries of each partition. For example, suppose we have a Customers table that contains information on all of our customers, identified by a unique customer number, ranging from 1 to 1,000,000. We might decide to partition that table into four equally spaced partitions, using the following partition function (I’ll call it customer_partfunc):

 CREATE PARTITION FUNCTION customer_partfunc (int)
 AS RANGE RIGHT
 FOR VALUES (250000, 500000, 750000)

These boundaries define four partitions. The first contains all values less than 250,000. The second contains values between 250,000 and 499,999. The third contains values between 500,000 and 749,999. All values greater than or equal to 750,000 go in the fourth partition.

Notice that I used the "RANGE RIGHT" clause in this example. This indicates that the boundary value itself should go in the partition on the right side. Alternatively, if I had used "RANGE LEFT", the first partition would have included all values less than [u]or equal to[/u] 250,000; the second partition would have included values between 250,001 and 500,000, and so on.

Step 2: Creating a Partition Scheme

Once you have a partition function describing [u]how[/u] you want to split your data, you need to create a partition scheme defining [u]where[/u] you want to partition it. This is a straightforward process that links partitions to filegroups. For example, if I had four filegroups named "fg1" through "fg4", the following partition scheme would do the trick:

 CREATE PARTITION SCHEME customer_partscheme
 AS PARTITION customer_partfunc
 TO (fg1, fg2, fg3, fg4)

Notice that we now link a partition function to the partition scheme, but we still haven’t linked the partition scheme to any specific database table. That’s where the power of reusability comes into play. We could use this partition scheme (or just the partition function, if we desired) on any number of database tables.

Step 3: Partitioning a Table

After defining a partition scheme, you’re now ready to create a partitioned table. This is the simplest step of the process. You simply add an "ON" clause to the table creation statement specifying the partition scheme and the table column to which it applies. You don’t need to specify the partition function because the partition scheme already identifies it.

For example, if you wanted to create a customer table using our partition scheme, you would use the following Transact-SQL statement:

 CREATE TABLE customers (FirstName nvarchar(40), LastName nvarchar(40), CustomerNumber int)
 ON customer_partscheme (CustomerNumber)

That’s everything you need to know about partitioning tables in Microsoft SQL Server! Remember to leverage the power of reusability by writing generic partition functions and partition schemes that might apply to multiple tables!

Analysis Services 2012

2012-08-05T01:38:00.000-07:00

Most important point to make about Analysis Services 2012 is that it is really two products in one. Analysis Services in the SQL Server 2008 R2 release and before is still present, but it is now called the Multidimensional model. It has had a few improvements relating to performance, scalability, and manageability, but there is no new major functionality. Meanwhile, there is a new version of Analysis Services that closely resembles PowerPivot—this is called the Tabular model.

What Is the BI Semantic Model?

One term that has been mentioned a lot in the discussions about Analysis Services 2012 is the BI Semantic Model or BISM. This term does not refer to either the Multidimensional or Tabular models specifically but, instead, describes the function of Analysis Services in the Microsoft BI stack: the fact that it acts as a semantic layer on top of a relational data warehouse, adding a rich layer of metadata that includes hierarchies, measures, and calculations. In that respect, it is very similar to the term Unified Dimensional Model that was used around the time of the SQL Server 2005 launch. In some cases, the term BI Semantic Model has referred to the Tabular model only, but this is not correct.

Why Have Two Models?

Why has this split happened? Although Microsoft does not want to make any public comments on this topic, there are a number of likely reasons.

Analysis Services Multidimensional is getting old. It was designed in an age of 32-bit servers with one or two processors and less than a gigabyte of RAM, when disk-based storage was the only option for databases. Times have changed, and modern hardware is radically different; now a new generation of memory-based, columnar databases has set the standard for query performance with analytic workloads, and Analysis Services must adopt this new 10 Microsoft SQL Server 2012 Analysis Services: The BISM Tabular Model technology to keep up. Retrofitting the new xVelocity in-memory engine into the existing Multidimensional model was not, however, a straightforward job, so it was necessary to introduce the new Tabular model to take full advantage of xVelocity.

Despite the success of Analysis Services Multidimensional, there has always been a perception that it is difficult to learn. Some database professionals, accustomed to relational data modeling, struggle to learn multidimensional concepts, and those that do find the learning curve is steep. Therefore, if Microsoft wants to bring BI to an ever-wider audience, it must simplify the development process—hence the move from the complex world of the Multidimensional model to the relatively simple and familiar concepts of the Tabular model.

Microsoft sees self-service BI as a huge potential source of growth, and PowerPivot is its entry into this market. It is also important to have consistency between the Microsoft self-service and corporate BI tools. Therefore, if Analysis Services must be overhauled, it makes sense to make it compatible with PowerPivot, with a similar design experience so self-service models can easily be upgraded to full-fledged corporate solutions.

Some types of data are more appropriately, or more easily, modeled by using the Tabular approach, and some types of data are more appropriate for a Multidimensional approach. Having different models gives developers the choice to use whichever approach suits their circumstances.

The Future of Analysis Services

Having two models inside Analysis Services, plus two query and calculation languages, is clearly not an ideal state of affairs. First and foremost, it means you have to choose which model to use at the start of your project, when you might not know enough about your requirements to know which one is appropriate—and this is the question we will address in the next section. It also means that anyone who decides to specialize in Analysis Services has to learn two technologies. Presumably, this state of affairs will not continue in the long term.

Microsoft has been very clear in saying that the Multidimensional model is not deprecated and that the Tabular model is not its replacement. It is likely that new features for Multidimensional will be released in future versions of Analysis Services. The fact that the Tabular and Multidimensional models share some of the same code suggests that some new features could easily be developed for both models simultaneously. The post on the Analysis Services blog previously referenced suggests that in time the two models will converge and offer much the same functionality, so the decision about which model to use is based on whether the developer prefers to use a multidimensional or relational way of modeling data. Support for DAX queries in the Multidimensional model, when it arrives, will represent one step in this direction.

One other thing is clear about the future of Analysis Services: It will be moving to the cloud. Microsoft has confirmed it is working on a cloud-based version of Analysis Services and this, plus SQL Azure, SQL Azure Reporting Services, and Office 365, will form the core of the Microsoft cloud BI strategy.

What’s new in Power View in Excel 2013 and in SharePoint 2013

2012-08-02T22:53:00.000-07:00

Power View in Excel 2013 and Power View in SharePoint 2013 both provide an interactive data exploration, visualization, and presentation experience for all skill levels. Pull your data together in tables, matrices, maps, and a variety of charts in an interactive view that brings your data to life. Many of the features now in Power View in Excel 2013 were in Power View in SharePoint 2010, and new features have been added to both.

Power View in Excel 2013: New features

Power View sheets can connect to different data models in one workbook

In Excel 2013, every workbook can contain an internal Data Model that you can modify in Excel, in PowerPivot, and even in a Power View sheet in Excel. You can base a Power View in Excel 2013 sheet on the Data Model in the same workbook or on an external data source – another workbook, or a tabular model deployed to a SQL Server 2012 Analysis Services (SSAS) instance. A single Excel workbook can contain multiple Power View sheets, and each of the sheets can be based on a different data model. A workbook can contain only one internal Data Model, but Power View sheets in a workbook can link either to that internal model or to an external data model.

To base a Power View sheet on an external data model, insert a blank Excel worksheet in the workbook, and on the Data tab > Get External Data (by creating a connection or using an existing connection).

If you want to reuse a connection you already have in this workbook, click Existing Connections, select the connection you want to use, and click Open.

Note If you get external data while in the PowerPivot window, it is automatically added to the internal Data Model, and so it isn’t a separate data model.

Provide the needed information in the Data Connection Wizard.
In the last step, the Import Data dialog box, click Power View Report.

Excel opens a new Power View sheet with the external data model in the Field List.

Each Power View sheet has its own charts, tables, and other visualizations. You can copy and paste a chart or other visualization from one sheet to another, but only if both sheets are based on the same data model.

Modify the internal Data Model without leaving the Power View sheet

Now that you can create Power View sheets and an internal Data Model in an Excel 2013 workbook, if you base your Power View sheet on the internal Data Model, you can make some changes to the Data Model while you’re in the Power View sheet. For example:

From the Field List in Power View in Excel, you can create relationships between different tables in the workbook.
If the Data Model in Excel has calculated fields, you can create KPIs based on those fields, and then add them to your Power View report.

Power View in Excel Services and Excel Web App, and in Office 365

When you create Power View sheets in Excel, you can view and interact with them on-premises in Excel Services and in Office 365. You can only edit Power View sheets in Excel 2013 on a client computer.

On-premises, Power View isn’t supported on Excel Web App. In Office 2013, administrators can choose whether users view files with Excel Services or Excel Web App. If the administrator chooses to view workbooks with Excel Web App, you can’t view Power View sheets when viewing Excel workbooks in the browser.

In Office 365, you don’t view Power View sheets using Excel Services. Instead you view them with Excel Web App Data Center, part of SharePoint Online.

Power View in Excel 2013 and SharePoint 2013: New features

Pie charts

Pie charts are simple or sophisticated in Power View. You can make a pie chart that drills down when you double-click a slice, or a pie chart that shows sub-slices within the larger color slices. You can cross-filter a pie chart with another chart. Say you click a bar in a bar chart. The part of the pie chart that applies to that bar is highlighted, and the rest of the pie is grayed.

Examples of visualizations available in Power View

Slicer filtering the report to breads
Tile flow navigation for tiles, currently on croissant
Card in a tile container, filtered to the current tile (croissant)
Line chart in tile container showing quantities consumed and server, filtered to croissants January to December
Multiples, filtered to breads and sorted in descending order by quantity served
Column chart filtered to breads, showing quantities served and consumed

Maps

Maps in Power View use Bing map tiles, so you can zoom and pan as you would with any other Bing map. Locations and fields are dots on the map: The bigger the value, the bigger the dot. When you add a multi-value series, you get pie charts on the map.

Key performance indicators (KPIs)

You can add key performance indicators (KPIs) (key performance indicator (KPI): A quantifiable measure for gauging business objectives. The KPI gauges the performance of the value, defined by a base measure, against a target value, also defined by a measure or by an absolute value.) to your Power View report to show progress toward goals if the data model your Power View report is based on has them.

KPIs are based on calculated fields (calculated field: A field in a PivotTable report or PivotChart report that uses a formula you create. Calculated fields can perform calculations by using the contents of other fields in the PivotTable report or PivotChart report.). In Power View in Excel, you can actually create KPIs if the data model your Power View sheet is based on contains calculated fields. You create calculated fields and KPIs on the PowerPivot tab in Excel, or by managing the Data Model in the PowerPivot window. Then you can add the KPIs you create to your Power View report.

Hierarchies

If your data model has a hierarchy, you can use it in Power View. For example, the data model could have a hierarchy of Continent, Country/Region, State/Province, and City. In Power View you can add one field at a time, or you can add all the fields in the hierarchy at once.

If your data model doesn’t have a hierarchy, you can create one in Power View, too. Just adding multiple fields to the Rows or Columns box for a matrix creates a hierarchy for that matrix. Adding multiple fields to the Axis box of a chart creates a hierarchy for that chart.

You can put fields in any order in a hierarchy. It needn’t be a natural hierarchy like Category > Subcategory > Product. It could be Category > Year > Continent.

The advantage of having the hierarchy in a data model is that you can use it in every Power View report you build on that data model. You can only create a hierarchy in the Data Model in Excel by going to the PowerPivot window

Drill up/drill down

When you have a matrix with multiple fields in the rows or columns, you can set it to show levels, and that collapses the matrix to show only the top, or outermost, level. You double-click one value in that level to expand to show the values under that one in the hierarchy. To use the example in the Hierarchies section, if you set Power View to show levels, then you see only continent names in the matrix. Double-clicking a continent—say, Asia —shows the countries/regions in Asia, with an arrow to go back to continents. Double-clicking a country—say, India—shows the states/provinces of India, again with an arrow to go back up to countries/regions.

Charts work the same way. If you convert a matrix to a chart, you end up with a chart with multiple fields in the Axis box. Going back to the Continents example, if you double-click the Europe bar in a Continents bar chart,

You see the bars for the countries/regions in Europe only, with an arrow to take you back up.

If you open the Filter pane, you see that drilling down on either a chart or matrix is acting as a filter on that object. It is filtering the values in the object as you drill down, and removing the filter as you drill back up. It is only filtering that object, though. It isn’t filtering anything else on the sheet.

Report styles, themes, and text resizing

Power View has new report themes. When you change the theme, the new theme applies to all the Power View views in the report or sheets in the workbook.

Power View for SharePoint Server 2010 featured eight basic accent themes that controlled chart colors.

Power View in Excel 2013 and in SharePoint Server 2013 now features 39 additional themes with more varied chart palettes as well as fonts and background colors.

You can also change the text size for all of your report elements.

Backgrounds and background images

You can set the background of each view from white to black, with a number of gradient options. On darker backgrounds the text changes from black to white so it stands out better.

You can also add background images to each view. Browse to an image file on your hard disk or elsewhere and apply it as the background image for a view. You can then set it to fit, stretch, tile, or be centered on the view, and set its transparency from 0% (invisible) to 100% (not at all transparent). The report stores a copy of the image.

You can combine background and image for a variety of effects.

Hyperlinks

Add a hyperlink to a text box in a view and link it to any Internet or email address. In Power View in Excel and in edit mode for a Power View report in SharePoint, you follow the hyperlink by clicking it while holding down the Ctrl key. In reading and full-screen modes in Power View in SharePoint, you just click the link.

Printing

You can print Power View views in SharePoint and in Excel. In both cases, what you print is what you see on the view when you send it to the printer. If the view contains a region with a scroll bar, the printed page contains the part of the region that is visible on the screen. If a view contains a region with tiles, then whichever tile is selected is the one that prints.

Support for right-to-left languages

Power View in SharePoint now has options to set the default direction for new views and the direction for a specific existing view. Unless you change it, the direction is the same as the direction for SharePoint.

Power View in Excel takes the setting for default direction from Excel. You can change those settings. In Excel, go to File > Options > Advanced and look for Default direction. You can also change the direction for a specific sheet in the same dialog box, without changing the default direction.

Power View changes how it handles integers

By default, Power View for SharePoint 2010 aggregated decimal numbers, but treated integers as categories rather than aggregating them. A data model designer either in PowerPivot or in SQL Server Data Tools could set defaults for integers, but this was the default behavior.

In Power View in Excel 2013 and in SharePoint 2013, Power View aggregates both decimal numbers and integers by default. A data model designer can still specify other default behavior, but that is the default. To change your data into a chart, at least one data column needs to be aggregated.

Power View backward- and forward-compatibility

Power View RDLX files in SharePoint are backward compatible, meaning that if you saved a Power View file in SharePoint 2010 with the SQL Server 2012 Reporting Services add-in, you can open and save it in Power View in SharePoint 2013 with the SQL Services 2012 Service Pack 1 (SP 1) Reporting Services add-in. The reverse isn’t true: You can’t open a newer-version Power View RDLX file in older versions of SharePoint and SQL Server Reporting Services.

Power View and data models

Power View and SQL Server Analysis Services data models are forward- and backward-compatible with each other:

You can base a Power View file in SharePoint 2010 with the SQL Server 2012 Reporting Services add-in on an Excel 2013 data model or on a SQL Server 2012 SP 1 Analysis Services tabular model, and vice versa. However, some features such as hierarchies and KPIs are only available if you base a Power View report in SharePoint 2013 with the SQL Server 2012 SP 1 Reporting Services add-in on an Excel 2013 data model or on a SQL Server 2012 SP 1 Analysis Services tabular model.

Power View and Excel Services

Power View and Excel Services are backward- but not forward- compatible:

SharePoint 2013 is backward-compatible with Excel 2010 PowerPivot workbooks. If you upload an Excel 2010 PowerPivot workbook to SP15, you can open it in Excel Services and also base a Power View report on it.
SharePoint 2010 is not forward-compatible with Excel 2013 workbook Data Models. If you upload an Excel 2013 workbook with a Data Model to SharePoint 2010, it may not work properly in Excel Services and you can’t base a Power View report on it.

Bing Maps in Microsoft Excel 2013 using Power View

2012-08-02T03:39:00.003-07:00

There has been a lot of buzz in the BI community since yesterday, and the reason is none other than the public preview of Office 2013 (Excel 2013 in particular). There’s been a lot of articles written on the new features available and the integration of PowerPivot and Power View has really got everyone talking about.

Power View in Excel 2013 has the ability to create maps from your data and uses Bing maps for the same. Unlike the maps in SSRS, here you do have the ability to zoom and pan as needed. Also, Bing maps automatically detects the location and hence you don’t need to provide a shapefile or even the latitude/longitude information. In this blog, I will take you through the steps to create a map report in Power View.

Creating a basic map report

1) Open up a new workbook Excel 2013 and then enter the following data in the cells

Country	City	Sales
USA	Charlotte, North Carolina	100
USA	Madison, Wisconsin	50
USA	Jacksonville, Florida	140
USA	Rochester, New York	40
USA	Philadelphia, Pennsylvania	120

2) Select the entire data, go to the Insert tab and click on Power View icon

You should get a loading screen while it takes a couple of seconds to open Power View

3) Now click on the Country field, and then you should be able to see the Map icon appear on the menu above. Click on the Map icon as shown in the image below

You should get a warning to enable content as the data needs to be sent to Bing to get geocoded. Click on enable content to proceed. Note that you would need an internet connection for implementing this.

4) Now you can pretty much rearrange your fields by dragging them into the areas below. I have dragged Sales into the Size, City into Locations as well as Color. So I get a map report below which shows the cities as bubbles with corresponding colors and size as the amount of sales

5) You can also play around with other properties like Title, Legend, Data Labels and Map Background. They are present when you click on the Layout tab.

It is interesting to note that the data used for creating the Power View report gets imported into PowerPivot by default. This is because Power View can only communicate through DAX currently, and hence needs a tabular model behind it. The PowerPivot model can be viewed by clicking on the PowerPivot tab and then selecting the manage tab

Since there is no way to add additional data into this model without deleting and recreating the table, it would be a good practice to create the PowerPivot model first from linked tables, and then using the PowerPivot fields to create the Power View report. This way, we will be able to keep on adding data as long as there is a link between the table and PowerPivot. I will be showing you how to do this in the next part

Creating a drill down in map report

1) Select the same set of data in excel, go to the PowerPivot tab and select the Add to Data Model icon as shown below.

Note that you can still create Power View reports directly, but we will be using this technique for the reasons mentioned above earlier.

2) Select the home tab, then click on Pivot table option and select Power View to create Power View report

It is recommended to set the reporting properties of the Country and City field, so that Power View can recognize them as geographical entities, as shown in the image below

3) Now you should be able to see Power View (in case you don’t, you can click on a blank cell, go to the Insert tab and click on Power View icon). Notice that the Country and City fields have a map icon

4) Now click on the Country field, and then you should be able to see the Map icon appear on the menu above. Click on the Map and then drag both the country and city fields within locations. You can also put the Sales measure in the Size area. Now you should have a map report which drills down from the Country level to the city

If you double click on the blue dot in the center (which is USA), you will get the drill down report by cities. Notice that the title has automatically changed from Sales by Country to Sales by City. You can also click on the up arrow (highlighted in the image below) to return to the parent report

You can also create hierarchies in your PowerPivot model, and that can be directly added dragged and dropped to the Locations area. This will ensure that you don’t have to drag and drop each field individually and the drill down would be present across the entire hierarchy.

You can also see the benefit of adding the linked table to the PowerPivot model instead of using a range. Now if I have to add data, I can just go to the excel sheet and append the rows that I want as shown below

Now, all I need to do is to go to my Power View sheet and refresh the report. You can see that the new data is already included in the report

Creating Pie charts in map report

1) Use the previous report, and then add the city to the Color area. Now you can see that there is a pie chart by cities at a country level

2) You can hover the mouse on the pie charts, and the pie chart will expand and show the tooltips as shown below

You can also use the horizontal / vertical multiples location areas to split it by the selected field as shown below

Similarly, you can use the Tile By option also (however, this feature is not available if you use the multiples option)

Another best practice is to concatenate your city names with state/country info also (as I have done in all the examples) as there might be duplicate city names and doing this will help Bing in geocoding it better. You can also do this operation as a calculated column in PowerPivot, if you don’t want the city names to be displayed with their country/region info appended

Scatter Chart over Time on the X-Axis and Play Axis

2012-08-02T03:14:00.002-07:00

As you have seen in many Power View demos, you can run the Scatter Chart over time by placing date/time onto the Play Axis. This is pretty cool and it allows you to see trends over time on multiple dimensions. But how about if you want to see time also on the x-axis?

Scenario

In this scenario, I’d like to see the number of devices on the y-axis, date on the x-axis, broken out by device make. This can be easily achieved using a column bar chart.

Yet, if I wanted to add another dimension to this, such as the number of calls (QueryTime), the only way to do this without tiling is to use the Scatter Chart. Yet, this will not yield the results you may like seeing either.

It does have a Play Axis of Date, but while the y-axis has count of devices (count of ClientID), the x-axis is the count of QueryTime – it’s a pretty lackluster chart. Moving Count of QueryTime to the Bubble Size makes it more colorful but now all the data is stuck near the y-axis. When you click on the play-axis, the bubbles only move up and down the y-axis.

Date on X-Axis and Play Axis

So to solve the problem, the solution is to put the date on both the x-axis and the play axis. Yet, the x-axis only allows numeric values – i.e. you cannot put a date into it. So how do you around this limitation?
What you can do is create a new calculated column:
DaysToZero = -1*(max([date]) – [date])

What this does is to calculate the number of days differing between the max([date]) within the [date] column as noted below.

As you can see, the max([date]) is 7/30/2012 and the [DaysToZero] column has the value of datediff(dd, [Date], max([Date]))

Once you have created the [DaysToZero] column, you can then place this column onto the x-axis of your Scatter Chart. Below is the scatter chart configuration

With this configuration, you can see events occur over time when running the play axis as noted in the screenshots below.

Connecting PowerPivot to Hadoop on Azure – Self Service BI to Big Data in the Cloud

2012-08-02T02:58:00.003-07:00

This post describes the steps to connect Hadoop from PowerPivot.
Pre-requisites

PowerPivot for Excel (as of this post, using SQL Server 2012 RC1 version)

Access to Hadoop on Azure CTP

Configuration Steps

The steps to follow are the:

Install the HiveODBC Driver (we will configure the DSN later)
Steps 1 – 3 from Using the Excel Hive Add-In to open the ports in Hadoop on Azure

2) Create a Hive ODBC Data Source > File DSN
Here, we will go about creating a File DSN Hive ODBC Data Source.

To do this:

Go to the ODBC Data Sources Administrator and click on the File DSN tab.

Click on Add, Choose HIVE, Click Next, Click Browse to choose a location of the file; click Finish.

Open the File DSN you just created and click Configure. The ODBC Hive Setup and configure the host (e.g. [clustername].cloudapp.net) and authentication information (the username is what you had specified when you had created the cluster)

3) Connect PowerPivot to Hadoop on Azure via the HiveODBC File DSN

Open up the PowerPivot ribbon and click on the Get External Data from Other Sources.

From the Table Import Wizard, click on the Others (OLEDB/ODBC) and click Next

From here, click Build and the Data Link Properties, click on Provider, and ensure the Microsoft OLEDB Provider for ODBC Drivers is selected. Click Next.

In the Data Link Properties dialog, choose “Use connection string”, and click Build and choose the File DSN you had created from Step #2. Enter in the password to your Hadoop on Azure cluster. Click OK.

The Data Link Properties now contains a connection string do the Hadoop on Azure cluster.

Note, after this dialog, verify that the password has been entered into the connection string that that has been built into the Table Import Wizard. Note, the blue arrow points to a lack of a PWD=<password> clause. If the password isn’t specified, make sure to add it back in.

Click OK, click Next. From here you will get the Table Import Wizard and we are back to the usual PowerPivot steps.

Click on “Select from a list of tables and views to choose the data to import”

Choose your table (e.g. hivesampletable) and import the data in.

It looks like a lot of steps but once you get into the flow of things, it’s actually a relatively easy flow.

Connect Excel to Hadoop on Azure via HiveODBC

2012-08-02T02:36:00.001-07:00

One key feature of Microsoft’s Big Data Solution is solid integration of Apache Hadoop with the Microsoft Business Intelligence (BI) components. A good example of this is the ability for Excel to connect to the Hive data warehouse framework in the Hadoop cluster. This section walks you through using Excel via the Hive ODBC driver.

Install the Hive ODBC Driver

Prerequisites:

Ensure that Excel 2010 64-bit is already installed before installing the HiveODBC Driver.

Download the 64-bit Hive ODBC driver MSI file from the Portal by clicking the Download tile after logging into HadoopOnAzure.com .

Note, the HiveODBC driver includes both the HiveODBC Driver and the Excel Hive-AddIn.

Double click HiveODBCSetupx64.msi to start the installation.
Read the license agreement.
If you agree to the license agreement, click I agree and Install.

4. Once the installation has completed, click Finish to exit the Setup Wizard.

Install the Microsoft Excel Hive Add-In

Prerequisites:

Microsoft Excel 2010 64-bit
64bit Hive ODBC driver installed

Start Microsoft Excel 2010 64-bit.
You will be asked to install the HiveExcel add-in. Click Install.
Once the add-in has been installed, click the Data tab in Microsoft Excel 2010. You should see the Hive Panel as shown in the screenshot below.

Create a Hive ODBC Data Source to use with Excel

Click Start->Control Panel to launch the Control Panel for Microsoft Windows.
In the Control Panel, Click System and Security->Administrative Tools->Data Sources (ODBC). This will launch the ODBC Data Source Administrator dialog.

In the ODBC Data Source Administrator dialog, click the System DSN tab.
Click Add to add a new data source.
Click the HIVE driver in the ODBC driver list.

4. Then click Finish. This will launch the ODBC Hive Setup dialog shown in the screenshot below.

Enter a data source a name in the Data Source Name box. For Example, “MyHiveData”.
In the Host box , enter the host name of the cluster you created on the portal. For example, “myhadoopcluster.cloudapp.net”.
Enter the username you used to authenticate on the portal.
Click OK to save the new Hive data source.
Click OK to close the ODBC Data Source Administrator dialog.

Using the Excel Hive Add-In

Go to HadoopOnAzure.com and click Sign In to sign into your Hadoop cluster.
Click the Open Ports tile to access port configurations for your cluster.

3. On the Configure Ports page, click the toggle switch for the ODBC Server port to turn it on.

Open Microsoft Excel 2010 64-bit.
In Microsoft Excel 2010 64-bit, click the Data tab.
Click the Hive Panel to open the Hive panel in Excel.
In the drop-down list labeled Select or Enter Hive Connection, select the data source name you previously created.
You will be asked to enter the password to authenticate with the cluster on the portal. Enter the password for the user name.
In the drop-down list labeled Select the Hive Object to Query, select hivesampletable [Table].
Select the checkbox for each of the columns in the table. The Hive Query panel should look similar to the following.

11. Click Execute Query.

Microsoft BI Tools with Hadoop - Big Data Analytics

2012-08-02T02:09:00.001-07:00

Microsoft’s goal for Big Data is to provide insights to all users from structured or unstructured data of any size. While very scalable, accommodating, and powerful, most Big Data solutions based on Hadoop require highly trained staff to deploy and manage. In addition, the benefits are limited to few highly technical users who are as comfortable programming their requirements as they are using advanced statistical techniques to extract value. For those of us who have been around the BI industry for a few years, this may sound similar to the early 90s where the benefits of our field were limited to a few within the corporation through the Executive Information Systems.

Analysis on Hadoop for Everyone

Microsoft entered the Business Intelligence industry to enable orders of magnitude more users to make better decisions from applications they use every day. This was the motivation behind being the first DBMS vendor to include an OLAP engine with the release of SQL Server 7.0 OLAP Services that enabled Excel users to ask business questions at the speed of thought. It remained the motivation behind PowerPivot in SQL Server 2008 R2, a self-service BI offering that allowed end users to build their own solutions without dependence on IT, as well as provided IT insights on how data was being consumed within the organization. And, with the release of Power View in SQL Server 2012, that goal will bring the power of rich interactive exploration directly in the hands of every user within an organization.

Enabling end users to merge data stored in a Hadoop deployment with data from other systems or with their own personal data is a natural next step. In fact, we also introduced Hive ODBC driver, currently in Community Technology Preview, at the PASS Summit in October. This driver allows connectivity to Apache Hive, which in turn facilitates querying and managing large datasets residing in distributed storage by exposing them as a data warehouse.

Microsoft BI connectivity with Hadoop

This connector brings the benefit of the entire Microsoft BI stack and ecosystem on Hive. A few examples include:
- Bring Hive data directly to Excel through the Microsoft Hive Add-in for Excel
- Build a PowerPivot workbook using data in Hive
- Build Power View reports on top of Hive
- Instead of manually refreshing a PowerPivot workbook based on Hive on their desktop, users can use PowerPivot for SharePoint to schedule a data refresh feature to refresh a central copy shared with others, without worrying about the time or resources it takes.
- BI Professionals can build BI Semantic Model or Reporting Services Reports on Hive in SQL Server Data tools
- Of course all of the 3^rd party client applications built on the Microsoft BI stack can now access Hive data as well!
Klout is a great customer that’s leveraging the Microsoft BI stack on Big Data to provide mission critical analysis for both internal users as well as to its customers

Best of both worlds

As we mentioned in the beginning of this blog article, one size doesn’t fit all, and it’s important to recognize the inherent strengths of options available to choose when to use what. Hadoop broadly provides:

- an inexpensive and highly scalable store for data in any shape,

- a robust execution infrastructure for data cleansing, shaping and analytical operations typically in a batch mode, and

- a growing ecosystem that provides highly skilled users many options to process data.

The Microsoft BI stack is targeted at significantly larger user population and provides:

- functionality in tools such as Excel and SharePoint that users are already familiar with,

- interactive queries at the speed of thought,

- business layer that allows users to understand the data, combine it with other sources, and express business logic in more accessible ways, and

- mechanisms to publish results for others to consume and build on themselves.

Successful projects may use both of these technologies in complementary manner, like Klout does. Enabling this choice has been the primary motivator for providing Hive ODBC connectivity, as well as investing in providing Hadoop-based distribution for Windows Server and Windows Azure.

Microsoft Big Data Solution SQL Server, Apache Hadoop and Windows Azure

2012-08-02T00:53:00.000-07:00

Big Data Solution

Microsoft’s end-to-end roadmap for Big Data embraces Apache Hadoop™ by distributing enterprise class Hadoop based solutions on both Windows Server and Windows Azure. The roadmap includes Microsoft BI tools such as SQL Server Analysis Services, Reporting Services and even PowerPivot and Excel. This enables you to do BI on all your data, including those in Hadoop.

Key Benefits

Broader access of Hadoop to end users, IT professionals and Developers, through easy installation and configuration and simplified programming with JavaScript.
Enterprise ready Hadoop distribution with greater security, performance, ease of management and options for Hybrid IT usage.
Breakthrough insights through the use of familiar tools such as Excel, PowerPivot, SQL Server Analysis Services and Reporting Services.

Technical Considerations
On the more technical front, we have been working on a simplified download, installation and configuration experience of several Hadoop related technologies, including HDFS, Hive, and Pig, which will help broaden the adoption of Hadoop in the enterprise.
The Hadoop based service for Windows Azure will allow any developer or user to submit and run standard Hadoop jobs directly on the Azure cloud with a simple user experience. Therefore it doesn’t matter what platform you are developing your Hadoop jobs on -you will always be able to take a standard Hadoop job and deploy it on our platform, as we strive towards full interoperability with the official Apache Hadoop distribution.
This is great news as it lowers the barrier for building Hadoop based applications while encouraging rapid prototyping scenarios in the Windows Azure cloud for Big Data.To facilitate all of this, we have also entered into a strategic partnership with Hortonworks that enables us to gain unique experience and expertise to help accelerate the delivery of Microsoft’s Hadoop based distributions on both Windows Server and Windows Azure.
For developers, we will enable integration with Microsoft developer tools as well as invest in making Javascript a first class language for Big Data. We will do this by making it possible to write high performance Map/Reduce jobs using Javascript.For end users, the Hadoop-based applications targeting the Windows Server and Windows Azure platforms will easily work with Microsoft’s existing BI tools like PowerPivot and recently announced Power View, enabling self-service analysis on business information that was not previously accessible. To enable this we will be delivering an ODBC Driver and an Add-in for Excel, each of which will interoperate with Apache Hive.
Finally, in line with our commitment to Interoperability and to facilitate the high performance bi-directional movement of enterprise data between Apache Hadoop and Microsoft SQL Server, we have released two Hadoop-based connectors for SQL Server to manufacturing.
The SQL Server connector for Apache Hadoop lets customers move large volumes of data between Hadoop and SQL Server 2008 R2, while the SQL Server PDW connector for Apache Hadoop moves data between Hadoop and SQL Server Parallel Data Warehouse (PDW). These new connectors will enable customers to work effectively with both structured and unstructured data.

Microsoft SQL Server 2012 Takes on Big Data with Hadoop

2012-08-02T00:46:00.000-07:00

At PASS Summit 2011 in Seattle, one of the biggest surprise announcements was Microsoft's support for Apache Hadoop as a part of its SQL Server 2012 announcements.

To understand why Hadoop is important and how it relates to SQL Server, we need to get an idea of what Hadoop actually is and what it isn’t. First, Hadoop isn’t a relational database system, so it’s not a replacement or substitute for SQL Server. Hadoop is an open-source project that’s managed by the Apache Software Foundation. It was designed to solve a somewhat different problem—the problem of handling large amounts of unstructured data. SQL Server and other relational databases primarily store structured data. Data can be stored by using the XML and FileStream data types, but there can be limitations to the size, as well as the amount of processing power that can be applied to access the data. The basic technology behind Hadoop was originally developed by Google so that it could index all types of textual information. Google’s ideas were then incorporated into an open-source project named Nutch and later Yahoo! worked to transform Hadoop into an enterprise application. Hadoop is used by several notable companies, perhaps the most recognizable company is Facebook. In 2010, Facebook had the largest Hadoop cluster in the world, with more than 20PB of storage.

Hadoop is written in Java and runs on a collection of commodity shared-nothing servers. You can add or remove servers from a Hadoop cluster at anytime without disrupting the service. The more servers you use, the more computing power you get. A Hadoop implementation consists of two key components: the Hadoop Distributed File System (HDFS), which provides data storage across multiple servers, and high-performance parallel data processing, which uses a technique called MapReduce. MapReduce essentially splits up data discovery and indexing tasks by sending different parts to all of the servers in your cluster. Each server works on its own piece of the data. The results are then delivered back to the user as a complete set. In essence, MapReduce maps the operation out to all of the servers in the cluster and reduces the results into a single result set.

To implement Hadoop you can buy a collection of commodity servers and run the Hadoop software on each server to create a high-performance Hadoop cluster. For better scalability, you can add more servers. When you load all your data into Hadoop, the software breaks the data into pieces and distributes it across all the available servers. There's no central location in which you access your data. The Hadoop cluster keeps track of where the data resides and automatically stores multiple copies of the data. If a server fails or is removed from the cluster, Hadoop automatically replicates the data from a known copy.

Being an open-source product you might wonder what Hadoop has to do with Windows. At PASS Summit 2011, Microsoft announced that the company had created a Windows version of Hadoop that's able to run on Windows Server for on-premises implementations or on Windows Azure for cloud implementations. In addition, Microsoft is working with HortonWorks to develop bi-directional connectors for Hadoop and SQL Server. The SQL Server connector for Apache Hadoop lets customers move large volumes of data between Hadoop and SQL Server 2008 R2 or SQL Server 2012. There will also be a SQL Server Parallel Data Warehouse (PDW) connector for Hadoop that transfers data between Hadoop and SQL Server PDW. These new connectors will enable customers to work with both structured SQL Server data and unstructured data from Hadoop.

Hadoop isn’t a replacement for SQL Server’s relational database. Instead, it provides new capabilities that weren’t previously unavailable. I think Microsoft’s view is that Hadoop will be used in conjunction with SQL Server’s relational and analytic capabilities to enable enterprises to deploy Hadoop implementations alongside their exiting IT systems. This will extend the types of data that you can use in your applications similarly to how SQL Server Analysis Services (SSAS) does with the SQL Server relational database engine. In addition, it'll help SQL Server better compete with both Oracle and IBM’s DB2, which have also embraced Hadoop. Big data is a rapidly growing a trend and the ability to incorporate big data with SQL Server is a big deal. Click here for more information about Microsoft’s implementation of Hadoop and how it relates to SQL Server.

Number of rows/records in all the tables in SQL Server

2012-08-01T23:57:00.001-07:00

A small post, to get the number of records from all the tables from SQL Server, try the below SQL query.

SELECT '[' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + ']' AS fulltable_name, SCHEMA_NAME(t.schema_id) AS schema_name, t.name AS table_name,
i.rows
FROM sys.tables AS t INNER JOIN
sys.sysindexes AS i ON t.object_id = i.id AND i.indid < 2

--

What's New in Excel Services - SharePoint 2013

2012-08-01T23:47:00.001-07:00

Excel Services:

Excel Services in Microsoft SharePoint Server 2010 is a shared service that you can use to publish Microsoft Excel 2010 workbooks on SharePoint Server. The published workbooks can be managed and secured according to your organizational needs and shared among SharePoint Server 2010 users, who can render the workbooks in a browser. Excel Services was introduced in Microsoft Office SharePoint Server 2007 and is available only in the Enterprise edition of SharePoint Server 2010.

Excel Services consists of Excel Calculation Services, the Microsoft Excel Web Access Web Part, and Excel Web Services for programmatic access. It supports sharing, securing, managing, and using Excel 2010 workbooks in a browser by providing the following:

Global settings for managing workbooks, which include settings for security, load balancing, session management, memory utilization, workbook caches, and external data connections.
Trusted file locations (which allow you to define which document libraries are trusted by Excel Services) together with session management, workbook size, calculation behavior, and external data settings of workbooks stored in those locations.
An extensive list of trusted data providers for connecting to your data, plus the ability to add your own trusted data provider.
Trusted data connection libraries, which allow you to define which data connection libraries in your farm are trusted by Excel Services.
The ability to add your own user-defined function assemblies.

Looking at several specific scenarios can help you understand how best to take advantage of Excel Services:

Sharing workbooks through the browser Users can save Excel 2010 workbooks to a SharePoint Server document library to give other users browser-based access to the server-calculated version of the workbook. When the workbook is accessed, Excel Services loads the workbook, refreshes the external data if it is necessary, calculates it if it is necessary, and sends the resulting output view back through the browser. A user can interact with Excel-based data by sorting, filtering, expanding, or collapsing PivotTables, and by passing in parameters. This provides the ability to perform analysis on published workbooks. A user does not have to have Excel 2010 installed to view the workbook. Users will always view the latest version of a workbook, and they can interact with it in a browser. Security permissions can be set to limit what access is provided to which user.
Building business intelligence (BI) dashboards Browser-based dashboards can be created by using Excel and Excel Services together with the Excel Web Access Web Part. PerformancePoint Services can also use Excel Services workbooks as a data source.
Reuse of logic encapsulated in Excel workbooks in custom applications Besides a browser-based interface with the server, Excel Services provides a Web-service–based interface so that a published workbook can be accessed programmatically by any application that uses Web services. The Web service applications can change values, calculate the workbook, and retrieve some or all of the updated workbook by using that interface according to what security permissions are set for the published workbook.
Report Building One of the most useful features of Excel Services is report building. By publishing data-connected workbooks to a SharePoint document library and making them available through Excel Services, you can make reports that you have created in Excel available to others in your organization. Instead of multiple users having separate copies of the workbooks on their computers, the workbooks can be created and changed by a trusted author in a central location that is trusted by Excel Services. The correct version of the worksheet is easier to find, share, and use from Excel, SharePoint Server, and other applications.

The new ECS in SharePoint 2013 is pretty awesome, the Extel team has worked hard to make ECS on par with Excel. But wait what do these changes have in common with MOLAP? Well since most of the underlying Excel code is shared, a lot. Let me show you.

The Pivot table in Excel 2013 is created that points to the Adventureworks Molap cube. Nothing new yet here:

Now lets share this to SharePoint 2013, again nothing new here just a new UI:

Now lets open it in the browser, this is where things get interesting! Observe it looks very much like Excel 2013, and it even has a Pivot table fields list.

You now can change the pivot tables on the server (of course when you have the credentials) and add measures and rows and column all within the browser.

This is awesome! We now have a web based MOLAP browser that allows ad-hoc analytics. Just to make sure this is clear, this of course also works for Tabular

Features in PerformancePoint Services - SharePoint 2010 Business Insights

2012-08-01T03:09:00.001-07:00

The following list highlights the greater attention to enterprise scalability of PerformancePoint Services 2010 over PerformancePoint Server 2007.

Scale Up

Scale Out

Common Dashboard Framework and Infrastructure

MOSS Installer Framework

Enterprise Management Scenarios

Familiar Administration Environment

Fail Over

Logging

Ease of Deployment

PowerShell Management Scripting

SharePoint Repository

Objects created in PerformancePoint 2010 are now stored in SharePoint lists and libraries, not in a separate database. Because of this new location, these other features come along too.

Single Security Model
Single Security Administration Environment
Enterprise Management Scenarios
Backup and Restore
Disaster Recovery
The PPS administrator’s user experience is the SharePoint user experience

PerformancePoint is a SharePoint site

Filters compatible with SharePoint filters (WSS Connection Framework)

You can now use SharePoint’s standard connection framework to hook up PerformancePoint filters to standard SharePoint web parts.

Foundation for more seamless integration

The SharePoint architecture provides the foundation to make these things possible.

Search, Indexing, Workflows
Excel Services, Visio Server, Project Server
Custom security implementations

Supported environments

New supported environments make it possible for PerformancePoint to run on the latest hardware, making the most out of the latest and greatest in speed and reliability.

PerformancePoint Services for SharePoint
Windows Server 2008 64-bit
SharePoint Server O14 Enterprise CAL

PerformancePoint 2010 Clients

These are the client browsers and operating systems that are now supported.

Windows XP or greater
Internet Explorer 7.0 or greater
FireFox 3.5
Safari 3.5 (Mac only)

Scorecards

Scorecards have undergone significant changes. The following list should contribute to some fantastic performance indications.

Dynamic hierarchies with navigation
Drill Down and Drill Up
Dynamic Selections, such as Children and Descendants

KPIs on Columns

Multiple Actual Values

New Time Intelligence Formula Support

Variance

Calculated Metrics

Scorecard hierarchies available for filters

Cube formatting applied to KPIs

Time intelligence formula editing

Improved filtering

Sort and filter on non-numeric values

Improved value filtering
Improved status filters
Filter empty rows or columns

New report (KPI details)

Find out the fine details for each KPI.

Displays information about KPIs
- Applied filters
- KPI name, description, properties
- Metrics with trend
- Actual and target values
- Created and modified date
- Indicator and thresholds
- Calculation
- Variance
- Scoring method and distribution graph
- Comments
- Person Responsible

Pie charts

A pie chart, a highly-requested chart type, has been added for PerformancePoint 2010.

Analytic reports

Value filtering in grids and charts
Change measure on published reports

Navigation on bottom axis

Improved numeric formatting

Per-measure or per-column

Analysis Services conditional formatting

Analytic view designer

Notice the UDM organized cube layout inside the Details pane.

Filters

Filters have come a long way in this version. Here are some of the enhancements

Reusable between dashboards
Compatible with SharePoint filters (WSS Connection Framework)
Time Intelligence filters for multiple data sources

Dashboard deploy

PerformancePoint now has on-click deployment to SharePoint

Accessibility and Globalization

Accessibility meets SharePoint standard
Scorecard Analysis Services KPIs honor client locale
Dashboard accessibility now meets SharePoint's standard (WCAG 2.0 AA)

New Visualization

Much like the decomposition tree from ProClarity, the one in PerformancePoint 2010 also helps customers follow a line of data to its logical conclusion.

PerformancePoint Services (PPS) - Configuration

2012-08-01T00:42:00.002-07:00

What is PerformancePoint Services (PPS) in SharePoint 2010

In Definition

The definition would be that it enables you to create rich, context-driven dashboards that aggregate data and content to provide a complete view of how your business is performing at all levels.

The quick elevator pitch would be that PerformancePoint Services is the easiest way to create and publish Business Intelligence Dashboards in SharePoint 2010.

The concept of a dashboard takes root where you might assume. It’s the pilot’s cockpit display, the driver’s car dashboard…basically how you gauge and monitor the success of any activity from driving a Formula 1 race car to running a Fortune 500 company.

In any business enterprise, monitoring that success means access to data. SharePoint 2010 is able to use many technologies to view data, but PerformancePoint lives and breathes data. We replace the fuel gauge and speedometer with KPIs, Scorecards and Data Visualizations that are connected to the data in a that allows you to dive in and answer questions.

In Practice

Now that you have heard the marketing lingo, what is PerformancePoint Services made of, and how can you get value out of it? PerformancePoint Services is part of SharePoint 2010 and surfaces itself in a web part page like a SharePoint savvy user might expect. The unique part is two fold.

PerformancePoint Services starts with it’s authoring experience. The Dashboard Designer application is your toolbox to create from the bottom up: Key Performance Indicators (KPIs), Scorecards, Analytic Charts and Grids, Reports, Filters and Dashboards. Each of these components are unique to PerformancePoint Services and provide functionality that interacts with a server component that handles the hard parts like data connectivity and security.

The Dashboard Designer is a WYSIWYG experience, the pieces you build will appear in the browser exactly how you created them. That brings us to the second part, the end-user experience. PerformancePoint Services is designed with sharing in mind. The pieces you build are bundled into a dashboard and presented in a SharePoint page that understands who is viewing it and what they are allowed to see. That means you design, you publish and they consume…no IT involvement, no complicated workflows.

This article is divided in two main sections:

Configuring Performance Point Services (PPS).
Consuming the PPS Service and creating a Scorecard and Chart.

Configuring Performance Point Services (PPS):

For configuring PPS, one should complete the following four steps to use Performance Point features. The four important steps are:

Start a Secure Store Service and Performance Point Service at farm level via Central Admin.
Configure the Secure Store Service.
Create and configure the PPS application.

Start Secure Store Service and Performance Point Service

To start configuration of the Secure Store Service and Performance Point Service, we have to start on the Farm. For that go to Central Admin -> Application Management -> Service Application -> Manage Service on server.

Here on this page there are many services available. Out of these services, start Performance Point Service and Secure Store Service as shown below:

This will allow configuring further steps.

Configure the Secure Store Service

The Secure Store Service is a new addition to SharePoint 2010. Basically it replaces the Single Sign On feature. It is a service that has a secure database which stores the credentials associated with application IDs which can be used to authorize access to external data sources.

To configure the Secure Store Service, enter Central Admin -> Application Management -> Manage service applications.

Here you can create a new Secure Store Application or use an existing one. Let’s create a new Secure Store Service application.
For that, click on New and select Secure Store Service.

Here it will pop up a window for creation of the new Secure Store.

Provide Name, Database Server and Database Details, Application pool, and Manage Account detail.
I created a Secure Store named SecureStoreForPerformancePoint.
Once the Secure Store is created then there is no key generated which would encrypt the credentials. To create a new key, click on “Generate new key” on the top ribbon.

A pass phrase needs to be created to encrypt the key. Accept the check to Re-encrypt the Database Using the New Key and click OK.

On successful generation of the Secure Store Service, it shows the following screen.

On creation of the Secure Store Service, it has to be associated with the application. To configure the association, go to CA -> Application Management.

This shows the association of all default services with all web applications. Select the web site where you want to use the Performance point service and make the newly created Secure Store Service as default.

Creating and configuring the PPS application

Once the Secure Store service is configured then to create and configure the PerformancePoint service application, re-enter CA ->Application Management -> Manage service applications.

Enter an appropriate application name. In the “Application Pool” area, we can create a new application pool or choose an existing one. After filling in all details, click on “Create“.

Once the Performance Point Service is created, we need to set up an unattended service account so that we can use a single shared account to access an external data source. To do so, select the newly created Performance Point Service and click on “Manage” on the ribbon.

There will be several options such as defining the trusted data source location or content locations, importing PPS content and others. For our purpose, click on "PerformancePoint Service Application Settings".

By default the Secure Store Service name will be pre-populated. In order to configure the unattended account, provide an appropriate username and password and click OK.

Once the Performance Point Service is created, this service also needs to be associated with a Web Application similar to what we did with the Secure Store Service.

Once the service is configured then it can be consumed for creating Scorecard and Chart reports.
Consuming the PPS Service and creating Scorecard and Reports

Performance Point Service in SharePoint is provided a site template named Business Intelligence Center. The user can create a Web Application from Central Admin by making use of this template and start using Performance Point Service for creating Scorecard and Reports using Dashboard Designer.

However Performance Point Service can be consumed on any other site template as well by activating some features related to this service and also start using Performance Point Service for creating Scorecard and Reports using Dashboard Designer. (I follow this way). Follow the below steps:

Activate required features
Create List and Library
Create Scorecard using Dashboard Designer
Create Report using Dashboard Designer
Show Scorecard and Report on site

Activate required features

I created a Web Application by using the Team Site Template. On this team site, the user has to Activate Feature at two places.

On this site, go to Site Settings -> Site collection Administration -> Site Collection Features (Note: Only Site collection Admin can see this link and can perform this step).

And Activate PerformancePoint Services Site Collection Features as shown below:

b. On this site, go to Site Settings -> Site Actions -> Manage Site Features

And activate PerformancePoint Services Site Features as shown below:

Create List and Library

After activating above mentioned features, you are allowed to consume PPS. Now, create a Library of type Performance Point to store data connections.

And also create a List of type Performance Point Content List, as shown below:

The List and Library will help to create Scorecard and Reports.
Create Scorecard using Dashboard Designer

For creating Scorecard go to PPS Configuration List and click on Add New Item Link. Doing so will open the Dashboard Designer window (the first time it will ask to download and run an ActiveX control for Dashboard Designer).

Here in this Dashboard Designer

Let’s first create a Data connection in the PPS Connection Library.

For that right click on it and click on New Data Source.

Here various types of data sources are available. I am planning to consume data from an Analytical service.

The next screen provides server name, database name, and cube name to consume. (I am using the AdventureWorks database.)

Once the Data Connection is created, you rename and save the same.

For creating a Scorecard, right click on PPS Configuration list in Dashboard Designer.

This will show a dialog box to select a template for a Scorecard, as shown below:

Make sure you select the Analysis Services template from Microsoft for our Scorecard. We will use a Wizard option for our Scorecard. This wizard will guide us to select a data source. The next step is to add a new KPI in our Scorecard or select the existing KPIs from our data source (in case you have any). The steps are shown below:

Once you select the data source, click the Next button in the wizard. This will take you to a step where we can either import existing KPIs from our data source or we can create new KPIs. Choose ‘Create new KPIs from SQL Server Analysis Services Measures’ and click the Next button.

Create KPI as shown below:

Now click the Next button until you reach the Finish wizard button. Keep all the default settings. Rename your Scorecard as ‘Sales Scorecard’. You are now done with a basic scorecard with three KPIs. It should look like the one shown below:

Now let’s modify the Scorecard by adding our products and timeline. You will see a ‘Details’ window on your right hand side screen, as shown below:

Add Country on Actual and Target and selected the desired country as shown below:

Then click on Edit and Update from top ribbon. Your Scorecard value will change as shown below:

Similarly, add Calendar Year from right side on Scorecard.

And Select desired members as shown below:

Again Edit and Update from top ribbon. Your Scorecard value will change as shown below:

That’s all. Your Scorecard is ready.
Create Report using Dashboard Designer

Reports are also created using Dashboard Designer. In Dashboard Designer, right click on PPS Configuration List.

In the popup wizard, select Report Template. Here we select Analytical Chart.

In the next step it will ask to select Data Source. Select the AdventureWorks data source which we created while creating the Scorecard.

On clicking Finish, it will create a Report Window. To configure the report, we have to put dimension data.
Let’s put Product data in Series as shown below:

On Adding, it shows as below:

Click on the dropdown and select the desired products.

Then Add Fiscal Year from right side detail window into Bottom series as shown below:

And that’s all. Report is ready as shown below:

Showing Scorecard and Report on site
The Scorecard and Report are created using Dashboard Designer and saved into the PPS Configuration List. Now to show them on a site, SharePoint provides OOB Webparts. Those are Performance Point Scorecard and Report.

For displaying Scorecard, use Scorecard webpart and provide the path of your newly created Scorecard in the Location property. The same is applicable for reports as well.

Transpose Table - Columns to Row in SQL Server

2012-07-30T06:01:00.001-07:00

One of most discussed question about TSQL algorithms is „How to transpose (change columns into rows) a table?” Most solutions based on static code which is adapted to your table. For every table, you need to write different code. Some authors propose using PIVOT statement, but it is not very handy to use this because PIVOT uses rows aggregation so avoiding this is always so easy.

Another question is "What if i need not only data change but gets columns/rows names too?” I you read my previous posts so you know which method we will use: XML-based.
This is how it looks:

DECLARE @xml XML ,
    @RowCount BIGINT
CREATE TABLE #Table
    (
      Column#1 INT ,
      Column2 NVARCHAR(MAX) ,
      Column3 DECIMAL(15, 2)
    )
CREATE TABLE #TempTable
    (
      RowID BIGINT ,
      CellId BIGINT ,
      Value NVARCHAR(MAX) ,
      ColumnName NVARCHAR(MAX)
    )
DECLARE @sSQl NVARCHAR(MAX)= 'SELECT (SELECT DISTINCT ColumnName FROM #TempTable WHERE CellId=Cell.CellId) as ColumnName,'
INSERT  INTO #Table
        SELECT  5 ,
                'Column_1_Test_String' ,
                99.99
INSERT  INTO #Table
        SELECT  9 ,
                'Column_2_Test_String' ,
                NULL


SET @xml = ( SELECT * ,
                    Row_Number() OVER ( ORDER BY ( SELECT   1
                                                 ) ) Rn
             FROM   #Table Row
           FOR
             XML AUTO,
                 ROOT('Root') ,
                 ELEMENTS XSINIL
           ) ;
WITH XMLNAMESPACES('http://www.w3.org/2001/XMLSchema-instance' AS xsi),RC AS
   (SELECT COUNT(Row.value('.', 'nvarchar(MAX)')) [RowCount]
   FROM @xml.nodes('Root/Row') AS WTable(Row))
,c AS(
SELECT b.value('local-name(.)','nvarchar(max)') ColumnName,
  b.value('.[not(@xsi:nil = "true")]','nvarchar(max)') Value,
  b.value('../Rn[1]','nvarchar(max)') Rn,
  ROW_NUMBER() OVER (PARTITION BY b.value('../Rn[1]','nvarchar(max)') ORDER BY (SELECT 1)) Cell
FROM
 @xml.nodes('//Root/Row/*[local-name(.)!="Rn"]') a(b)

 ),Cols AS (
 SELECT  DISTINCT c.ColumnName,
  c.Cell
 FROM c
 )
 INSERT INTO #TempTable (CellId,RowID,Value,ColumnName)
 SELECT Cell,Rn,Value,REPLACE(c.ColumnName,'_x0023_','#')
 FROM c
 
SELECT  @sSQL = @sSQl
        + '(SELECT T2.Value FROM #Temptable T2 WHERE T2.CellId=Cell.CellID AND T2.Rowid='
        + CAST(T.RowId AS NVARCHAR) + ') AS Row_' + CAST(T.RowID AS NVARCHAR)
        + ','
FROM    ( SELECT DISTINCT
                    RowId
          FROM      #TempTable
        ) T
SET @sSQl = LEFT(@sSQL, LEN(@sSQL) - 1)
    + ' FROM (SELECT DISTINCT CellId FROM #TempTable) Cell'
EXECUTE sp_Executesql @sSQl
--here you will have your output
DROP TABLE #Table
DROP TABLE #TempTable

Description:
Lines 1-24: declaring variables and filling up source table:

Lines 25-33: writing source table as XML into XML variable

Lines 34-52: filling up ##Temptable (temporary table). It is used for debug purpose only. If you test this solution, you can avoid this and implement it as next CTE in chain of sub-queries.

Lines 54-63: Create dynamic SQL statement to prepare transposed output
Line 64: Executing dynamic SQL. Here we have next output:

Lines 66-67 Dropping temporary tables.

Here I described how you can easily transpose table without knowing nothing about source table structure. Based on code above,you can prepare stored procedure which will get XML generated from table and it will return transposed XML. In such way you can easily transpose any of your tables from any place in your code.

SQL Server Heap Index

2012-07-26T22:11:00.002-07:00

A heap is a table without a clustered index. Heaps have one row in sys.partitions, with index_id = 0 for each partition used by the heap. By default, a heap has a single partition. When a heap has multiple partitions, each partition has a heap structure that contains the data for that specific partition. For example, if a heap has four partitions, there are four heap structures; one in each partition.

Depending on the data types in the heap, each heap structure will have one or more allocation units to store and manage the data for a specific partition. At a minimum, each heap will have one IN_ROW_DATA allocation unit per partition. The heap will also have one LOB_DATA allocation unit per partition, if it contains large object (LOB) columns. It will also have one ROW_OVERFLOW_DATA allocation unit per partition, if it contains variable length columns that exceed the 8,060 byte row size limit. For more information about allocation units, see Table and Index Organization.

The column first_iam_page in the sys.system_internals_allocation_units system view points to the first IAM page in the chain of IAM pages that manage the space allocated to the heap in a specific partition. SQL Server uses the IAM pages to move through the heap. The data pages and the rows within them are not in any specific order and are not linked. The only logical connection between data pages is the information recorded in the IAM pages.

The sys.system_internals_allocation_units system view is reserved for Microsoft SQL Server internal use only. Future compatibility is not guaranteed.

Table scans or serial reads of a heap can be performed by scanning the IAM pages to find the extents that are holding pages for the heap. Because the IAM represents extents in the same order that they exist in the data files, this means that serial heap scans progress sequentially through each file. Using the IAM pages to set the scan sequence also means that rows from the heap are not typically returned in the order in which they were inserted.

The following illustration shows how the SQL Server Database Engine uses IAM pages to retrieve data rows in a single partition heap.

Unique and non-unique SQL Server indexes on a heap table

In the upcoming weblog postings I want to work out the differences between unique and non-unique indexes in SQL Server. I assume that you already know the concepts about clustered- and non clustered indexes and how they are used in SQL Server.

In the past I've done a lot of trainings and consulting regarding SQL Server performance tuning and it seems that some people doesn't know the differences and implications between unique and non-unique indexes. And as you will see in the upcoming postings there are really big differences how SQL Server stores those two variants that impact the size and the efficiency of your indexes.

Let's start today with unique and non unique non clustered indexes on a table without a clustered index, a so-called heap table in SQL Server. The following listing shows how to create our test table and populate it with 80.000 records. Each record needs 400 bytes, therefore SQL Server can put 20 records on each data page. This means that our heap table contains 4.000 data pages and 1 IAM page.

-- Create a table with 393 length + 7 bytes overhead = 400 bytes -- Therefore 20 records can be stored on one page (8.096 / 400) = 20,24 CREATE TABLE CustomersHeap ( CustomerID INT NOT NULL, CustomerName CHAR(100) NOT NULL, CustomerAddress CHAR(100) NOT NULL, Comments CHAR(189) NOT NULL ) GO

-- Insert 80.000 records DECLARE @i INT = 1 WHILE (@i <= 80000) BEGIN INSERT INTO CustomersHeap VALUES ( @i, 'CustomerName' + CAST(@i AS CHAR), 'CustomerAddress' + CAST(@i AS CHAR), 'Comments' + CAST(@i AS CHAR) )

SET @i += 1 END GO

-- Retrieve physical information about the heap table SELECT * FROM sys.dm_db_index_physical_stats ( DB_ID('NonClusteredIndexStructureHeap'), OBJECT_ID('CustomersHeap'), NULL, NULL, 'DETAILED' ) GO

After the creation of the heap table and the data loading, you can now define a unique and non-unique non-clustered index on the column CustomerID of our heap table. We will define both indexes on the same column so that we can analyze the differences between unique- and non-unique non-clustered indexes.

-- Create a unique non clustered index CREATE UNIQUE NONCLUSTERED INDEX IDX_UniqueNCI_CustomerID ON CustomersHeap(CustomerID) GO

-- Create a non-unique non clustered index CREATE NONCLUSTERED INDEX IDX_NonUniqueNCI_CustomerID ON CustomersHeap(CustomerID) GO

If you want to define a unique non-clustered index on a column that doesn't contain unique data, you will get back an error message from SQL Server. Important to know is that SQL Server creates a non-unique non-clustered index if you don't specify the UNIQUE property when creating a non-clustered index. So by default you will always get a non-unique non-clustered index!

After the creation of both indexes you can analyze their size, their index depth, their size etc. with the DMV sys.dm_db_index_physical_stats. You can to pass in as the 3rd parameter the index-id. The IDs of all non-clustered indexes starts at 2, therefore the first non-clustered index gets the ID 2 and the second one the ID 3

-- Retrieve physical information about the unique non-clustered index SELECT * FROM sys.dm_db_index_physical_stats ( DB_ID('NonClusteredIndexStructureHeap'), OBJECT_ID('CustomersHeap'), 2, NULL, 'DETAILED' ) GO

-- Retrieve physical information about the non-unique non-clustered index SELECT * FROM sys.dm_db_index_physical_stats ( DB_ID('NonClusteredIndexStructureHeap'), OBJECT_ID('CustomersHeap'), 3, NULL, 'DETAILED' ) GO

As you can see from both outputs, the index root page of the unique non-clustered index is occupied of around 24%, where the index root page of the non-unique non-clustered index is occupied of around 39%, so there must be a difference in the storage format of unique/non-unique non-clustered indexes on a heap table! In the next step we create a simple helper table that stores the output of the DBCC IND command. The structure of this helper table is directly taken from the excellent book SQL Server 2008 Internals.

-- Create a helper table CREATE TABLE sp_table_pages ( PageFID TINYINT,PagePID INT, IAMFID TINYINT, IAMPID INT, ObjectID INT, IndexID TINYINT, PartitionNumber TINYINT, PartitionID BIGINT, iam_chain_type VARCHAR(30), PageType TINYINT, IndexLevel TINYINT, NextPageFID TINYINT, NextPagePID INT, PrevPageFID TINYINT, PrevPagePID INT, PRIMARY KEY (PageFID, PagePID) ) GO

After the creation of this helper table we can dump out all pages that are belonging to our non-clustered indexes to this helper table with the following two calls to DBCC INC in combination with the INSERT INTO statement

-- Write everything in a table for further analysis INSERT INTO sp_table_pages EXEC('DBCC IND(NonClusteredIndexStructureHeap, CustomersHeap, 2)') GO

-- Write everything in a table for further analysis INSERT INTO sp_table_pages EXEC('DBCC IND(NonClusteredIndexStructureHeap, CustomersHeap, 3)') GO

Now we can start analyzing our non-clustered indexes by using the undocumented DBCC PAGE command. To get some information back from DBCC PAGE you have to enable the flag 3604 of DBCC:

DBCC TRACEON(3604) GO

Let's dump out the index root page of our unique non-clustered index by the following command:

DBCC PAGE(NonClusteredIndexStructureHeap, 1, 4192, 3) GO

This will result in the following result in SQL Server Management Studio:

As you can see from this screenshot SQL Server stores the child page of the B-tree where the minimum key of the non-clustered index is located. The child page 4161 contains for example the record with the minimum key of 540 up to the maximum key of 1078. When you dump out the index root page with the dump option 1 you get the byte by byte representation of all index records on the index root page:

DBCC PAGE(NonClusteredIndexStructureHeap, 1, 4192, 1) GO

SQL Server needs here 11 bytes for storing an index row. These 11 bytes are storing the following information:

1 byte: Status Bits
4 bytes: Customer ID, like 540
4 bytes: child PageID, like 4161
2 bytes: FileID, like 1

As you can see it's up to the length of the non-clustered key how long an index row is. This also means that SQL Server is able to store more index rows on an index page if you choose a smaller non-clustered key. If you choose for example a CHAR(100) as a non-clustered index key, then SQL Server needs more index pages for your non-clustered index, which is not so efficient as using a smaller index key. The T-SQL script enclosed to this posting shows you how you can decode those bytes from the hexadecimal representation.
Finally you can dump out the child page 4161, which is located on the leaf-level of the non-clustered index.

DBCC PAGE(NonClusteredIndexStructureHeap, 1, 4161, 3) GO

As you can see from the figure, SQL Server stores for each index key on which data page and on which slot the corresponding record is located. Because we have not defined a clustered index on our table, SQL Server uses here the RID (Row Identifier) to point to the correct record on the data page. Index pages on the leaf-level on a heap table are different from leaf-level index pages defined on a clustered table (a table that contains a clustered index).When you dump out the leaf-level index page of the non-clustered index you can see that SQL Server needs 13 bytes per index row:

1 byte: Status Bits
4 bytes: CustomerID, like 540
4 bytes: PageID, like 178,
2 bytes: FileID, like 1
2 bytes: Slot number, like 19

Finally with this information in your hand, it is very easy to locate the correct record on the data page, because you know the PageID, FileID, and also the slot number where the record on the data page is located. Easy, isn't it?
Let's move on now to non-unique non-clustered indexes. Earlier we have already created such an index, which gets the index-id of 3 from SQL Server, because it's the second non-clustered index we have defined. In my case the index root page of the non-unique non-clustered index is located on page 4264, therefore I dump it out with the following command:

DBCC PAGE(NonClusteredIndexStructureHeap, 1, 4264, 3) GO

But wait! Now the result from DBCC PAGE on the root index page on a non-unique non-clustered index is different! As you can see SQL Server returns here an additional column named "HEAP RID (key)". The value in this column is used to make your non-unique non-clustered index unique. The HEAP RID column uses 8 additional bytes in your index row, which encodes the following information that are granted to be unique on a heap table:

4 bytes: PageID, like 178
2 bytes: FileID, like 1
2 bytes: Slot number, like 19

The overead of a non-unique non-clustered index on a heap table costs you 8 additional bytes per index row - on all index levels, expect the leaf-level, because SQL Server stores here always the HEAP RID as you have seen previously! So please keep this 8 bytes of additional index record overhead in mind, when you create non-clustered indexed that are NOT unique! And as I have said earlier, they are NOT unique by default!!!
In this example your non-unique non-clustered index is about 2 times bigger than the unique non-clustered index, because the unique index needs 11 bytes and the non-unique index needs 19 bytes (overhead of 8 bytes). When you look back to the output of the DMV sys.dm_db_index_physical_stats you can see that the index root page of the unique non-clustered index has a page space usage of around 24% where the index root page of the non-unique non-clustered index has a page space usage of around 39%. This will make a big difference on large non-clustered indexes!

Microsoft Power View

2012-07-25T23:51:00.001-07:00

With SQL Server 2012, Microsoft continues its investment in self-service business intelligence (BI) with the introduction of Power View, an interactive, web-based data exploration, visualization, and presentation technology. I'll discuss the underlying architecture of Power View and how it fits into Microsoft's overall BI strategy. I'll then walk you through how to install it and take advantage of its capabilities.

Do We Need Another BI Tool?

Figure 1 illustrates how Power View fits into Microsoft's overall BI strategy. With a BI platform that already includes Microsoft Excel, Excel Services, SQL Server Reporting Services (SSRS), and PerformancePoint Services, you might be wondering why Microsoft introduced another BI tool. Although it does share similarities with these existing technologies, Power View extends ad-hoc reporting to a broader audience.

Figure 1: Understanding how Power View fits into Microsofts overall BI strategy

In most organizations, there are a small number of end users who are comfortable building Excel PivotTables and PivotCharts. Excel Services makes PivotTables easy to consume, but the workbooks published to Excel Services are fairly fixed in terms of layout and interactivity. The number of users who can design a report in SSRS is very small, even with tools such as Report Builder. PerformancePoint Services provides intuitive charts and grids, but their design and deployment is usually done by an IT professional. Plus, there's no easy way for end users to save personal views. Power View not only addresses these important usability gaps but also provides its own unique capabilities.

Installing Power View

Power View is a component of SSRS 2012. Specifically, it's part of SSRS installed in SharePoint integrated mode. To install Power View in your environment, you need SharePoint 2010 Enterprise Edition SP1 or later.

Power View is designed to work against an intermediate, business-friendly tabular data model. This tabular data model can be either a PowerPivot workbook that has been deployed to a SharePoint document library or a database in a SQL Server Analysis Services (SSAS) 2012 instance running in tabular mode. Microsoft plans to support connectivity to SSAS running in multidimensional mode in a future service pack.

Getting Started

Power View reports are created in a web browser. Because Power View leverages Microsoft Silverlight, there's no desktop application for report authors (or consumers) to download, other than the Silverlight 5.0 runtime. Figure 2 shows an example of how to launch Power View from a SharePoint library, using a PowerPivot workbook as the data source. Power View can also be launched by clicking a Report Data Source (RDS) file or a Business Intelligence Semantic Model (BISM) file, a new type of data connection. The RDS or BISM file is stored in a SharePoint document library and points to either a PowerPivot workbook or a tabular mode database.

Figure 2: Launching Power View from a SharePoint library

Once launched, the authoring environment looks and behaves like a cross between Excel, Report Builder, and PowerPoint. Similar to an Excel PivotTable, you can check fields (or drag and drop fields) in a table list to begin building a report, as shown in Figure 3. Another similarity to a PivotTable is that there's no need to switch between design mode and preview mode. Every change made to the report is instantly visible. Note that behind the scenes, Power View communicates with a tabular data model by issuing Data Analysis Expressions (DAX) queries. DAX was initially introduced as an expression language in PowerPivot.

Figure 3: Building a report in Power View

Like Report Builder, Power View has many types of visualizations from which to choose. In my report, I wanted to analyze various economic indicators by presidential term. I selected the Tiles visualization so that each tile represents a president, and I converted the initial table to a card view (which displays each row from a table in a format similar to an index card). A tile acts as a container in that the card and any additional tables and charts will automatically be scoped to the selected tile (in this case, a particular president). Like a traditional reporting tool, Power View lets you arrange different tables and charts in a free-form manner. For example, in Figure 4, I added a line chart within the tile to display economic growth by calendar quarter. In addition to the line chart, Power View supports matrix, column, bar, and scatter charts.

Figure 4: Adding a line chart within the tile to display economic growth by calendar quarter

Saving and Sharing Power View Reports

To save a Power View report, you simply click Save on the File menu. The report is saved as a single file, with an .rdlx extension. A report file can be saved locally, but it must be saved to a SharePoint document library in order for others to view it.

Viewing a saved report is straightforward. An end user simply clicks the report from a document library, and it's rendered in the browser. (Note that a user will need both Read and View permissions to view a Power View report.) From here, the user can print, export, or edit the report. Any user who is able to view a report is also able to edit it, but the user must have at least Contribute permissions to save any changes.

SQL Server Index - Basics

2012-07-24T22:34:00.003-07:00

One of the most important routes to high performance in a SQL Server database is the index. Indexes speed up the querying process by providing swift access to rows in the data tables, similarly to the way a book’s index helps you find information quickly within that book. In this article, I provide an overview of SQL Server indexes and explain how they’re defined within a database and how they can make the querying process faster. Most of this information applies to indexes in both SQL Server 2005 and 2008; the basic structure has changed little from one version to the next. In fact, much of the information also applies to SQL Server 2000. This does not mean there haven’t been changes. New functionality has been added with each successive version; however, the underlying structures have remained relatively the same. So for the sake of brevity, I stick with 2005 and 2008 and point out where there are differences in those two versions.

Index Structures

Indexes are created on columns in tables or views. The index provides a fast way to look up data based on the values within those columns. For example, if you create an index on the primary key and then search for a row of data based on one of the primary key values, SQL Server first finds that value in the index, and then uses the index to quickly locate the entire row of data. Without the index, a table scan would have to be performed in order to locate the row, which can have a significant effect on performance.

You can create indexes on most columns in a table or a view. The exceptions are primarily those columns configured with large object (LOB) data types, such as image, text, and varchar(max). You can also create indexes on XML columns, but those indexes are slightly different from the basic index and are beyond the scope of this article. Instead, I'll focus on those indexes that are implemented most commonly in a SQL Server database.

An index is made up of a set of pages (index nodes) that are organized in a B-tree structure. This structure is hierarchical in nature, with the root node at the top of the hierarchy and the leaf nodes at the bottom.

Clustered Indexes

A clustered index stores the actual data rows at the leaf level of the index. Returning to the example above, that would mean that the entire row of data associated with the primary key value of 123 would be stored in that leaf node. An important characteristic of the clustered index is that the indexed values are sorted in either ascending or descending order. As a result, there can be only one clustered index on a table or view. In addition, data in a table is sorted only if a clustered index has been defined on a table.

Note: A table that has a clustered index is referred to as a clustered table. A table that has no clustered index is referred to as a heap

Nonclustered Indexes

Unlike a clustered indexed, the leaf nodes of a nonclustered index contain only the values from the indexed columns and row locators that point to the actual data rows, rather than contain the data rows themselves. This means that the query engine must take an additional step in order to locate the actual data.

A row locator’s structure depends on whether it points to a clustered table or to a heap. If referencing a clustered table, the row locator points to the clustered index, using the value from the clustered index to navigate to the correct data row. If referencing a heap, the row locator points to the actual data row.

Nonclustered indexes cannot be sorted like clustered indexes; however, you can create more than one nonclustered index per table or view. SQL Server 2005 supports up to 249 nonclustered indexes, and SQL Server 2008 support up to 999. This certainly doesn’t mean you should create that many indexes. Indexes can both help and hinder performance, as I explain later in the article.

In addition to being able to create multiple nonclustered indexes on a table or view, you can also add included columns to your index. This means that you can store at the leaf level not only the values from the indexed column, but also the values from non-indexed columns. This strategy allows you to get around some of the limitations on indexes. For example, you can include non-indexed columns in order to exceed the size limit of indexed columns (900 bytes in most cases).

Index Types

In addition to an index being clustered or nonclustered, it can be configured in other ways:

Composite index: An index that contains more than one column. In both SQL Server 2005 and 2008, you can include up to 16 columns in an index, as long as the index doesn’t exceed the 900-byte limit. Both clustered and nonclustered indexes can be composite indexes.
Unique Index: An index that ensures the uniqueness of each value in the indexed column. If the index is a composite, the uniqueness is enforced across the columns as a whole, not on the individual columns. For example, if you were to create an index on the FirstName and LastName columns in a table, the names together must be unique, but the individual names can be duplicated.

A unique index is automatically created when you define a primary key or unique constraint:

Primary key: When you define a primary key constraint on one or more columns, SQL Server automatically creates a unique, clustered index if a clustered index does not already exist on the table or view. However, you can override the default behavior and define a unique, nonclustered index on the primary key.
Unique: When you define a unique constraint, SQL Server automatically creates a unique, nonclustered index. You can specify that a unique clustered index be created if a clustered index does not already exist on the table.

Covering index: A type of index that includes all the columns that are needed to process a particular query. For example, your query might retrieve the FirstName and LastName columns from a table, based on a value in the ContactID column. You can create a covering index that includes all three columns.

Index Design

As beneficial as indexes can be, they must be designed carefully. Because they can take up significant disk space, you don’t want to implement more indexes than necessary. In addition, indexes are automatically updated when the data rows themselves are updated, which can lead to additional overhead and can affect performance. As a result, index design should take into account a number of considerations.

Database

As mentioned above, indexes can enhance performance because they can provide a quick way for the query engine to find data. However, you must also take into account whether and how much you’re going to be inserting, updating, and deleting data. When you modify data, the indexes must also be modified to reflect the changed data, which can significantly affect performance. You should consider the following guidelines when planning your indexing strategy:

For tables that are heavily updated, use as few columns as possible in the index, and don’t over-index the tables.
If a table contains a lot of data but data modifications are low, use as many indexes as necessary to improve query performance. However, use indexes judiciously on small tables because the query engine might take longer to navigate the index than to perform a table scan.
For clustered indexes, try to keep the length of the indexed columns as short as possible. Ideally, try to implement your clustered indexes on unique columns that do not permit null values. This is why the primary key is often used for the table’s clustered index, although query considerations should also be taken into account when determining which columns should participate in the clustered index.
The uniqueness of values in a column affects index performance. In general, the more duplicate values you have in a column, the more poorly the index performs. On the other hand, the more unique each value, the better the performance. When possible, implement unique indexes.
For composite indexes, take into consideration the order of the columns in the index definition. Columns that will be used in comparison expressions in the WHERE clause (such as WHERE FirstName = 'Charlie') should be listed first. Subsequent columns should be listed based on the uniqueness of their values, with the most unique listed first.
You can also index computed columns if they meet certain requirements. For example, the expression used to generate the values must be deterministic (which means it always returns the same result for a specified set of inputs).

Fulltext Search in SQL Server 2012

2012-07-22T00:27:00.001-07:00

Fulltext Search codebase has been significantly revamped to address both query performance and throughput on large scale (millions of documents) with concurrent updates. With SQL2008 they moved all the index storage in the database file and majority of population logic in to the core engine to make fulltext search an integral and fully manageable engine component. However there was work to be done to make it perform and scale against best of the fulltext engines out there in the industry. With Denali CTP1, they are pleased to deliver this improvement.

they looked at the entire code base from how queries block while waiting an ongoing index update to release a shared schema lock, from how much memory is allocated during index fragment population, to how they could reorganize the query code base as a streaming Table Value Function to optimize for TOP N search queries, how they could maintain key distribution histograms to execute search on parallel threads, all the way to how they could take better advantage of the processor compute instructions (scoring ranks for example)… End result is that they are able to significantly boost performance (10X in many cases when it comes to concurrent index updates with large query workloads) and scale without having to change any storage structures or existing API surface. All our customers going from SQL 2008 / R2 to Denali will benefit with this improvement.

Besides performance and scale improvement, they also added support for property scoped searches over documents with file system properties stored with-in a fulltext enabled table. One can now issue a CONTAINS query looking for all documents containing a particular term and authored by a particular author without having to maintain a separate column for the Author name in the database.

they also improved NEAR operator in the CONTAINS predicate to allow users to specify distance between two terms and if the order of the term matters. It is important to note that distances between two words in a single sentence are much smaller compared to the same across two sentences (even if words are placed next to each other with a period in between), or across paragraphs, or across bullet points or across spreadsheet columns or worksheets.

File Table in SQL Server 2012

2012-07-22T00:18:00.001-07:00

A SQL Server FileTable is a special table where you can store directory and files – that’s not special I know, the special thing is that you can access these files and directories from windows applications as if they were stored in the file system.

The data stored in the FileTable is exposed to windows through a windows share, and via this share it is possible to gain non-transactional access to the data.

If you create or change a file through the windows share the command is intercepted by a SQL Server component and the changes are applied to the corresponding data in the FileTable.

First we need to enable the FILE STREAM in SQL Server instance level. Follow the below mentioned steps.

1. Open the SQL Server Configuration Manager by Start -> All Programs -> Microsoft SQL Server 2012 -> Configuration Tools -> SQL Server Configuration Manager

2. Right click on the SQL Server instance under the SQL Server Services menu and select Properties.

3. SQL Server Instance Properties window will be opened. Select the FILESTRAEM tab and tick the below mentioned checkboxes and then click apply and ok

Enable FILESTREAM for Transact-SQL access
Enable FILESTREAM for file I/O access
Allow remote clients access to FILESTREAM data

4. Execute the below mentioned Transact-SQL code in the SSMS.

EXEC sp_configure filestream_access_level, 2

RECONFIGURE

5. Restart the SQL Server Instance.

Let’s create a demo database with a FILESTREAM filegroup, I’ll be setting the NON_TRANSACTED_ACCESS to FULL this gives me full non-transactional access to the share, this option can be changed later if you want to do that. Here is the code:

CREATE DATABASE MyFileTableTest

ON PRIMARY

(

NAME = N'MyFileTableTest',

FILENAME = N'G:\Demo\MyFileTableTest.mdf'

FILEGROUP FilestreamFG CONTAINS FILESTREAM

(

NAME = MyFileStreamData,

FILENAME= 'G:\Demo\Data'

)

LOG ON

(

NAME = N'MyFileTableTest_Log',

FILENAME = N'G:\Demo\MyFileTableTest_log.ldf'

)

WITH FILESTREAM

(

NON_TRANSACTED_ACCESS = FULL,

DIRECTORY_NAME = N'FileTable'

)

That’s the database done, now let’s create the magic table – that is simple done with the “AS FileTable” keyword. When creating a FileTable there is only two options that you can change, everything else is out of the box. Let’s have a look at how I created the table:

USE MyFileTableTest

go

CREATE TABLE MyDocumentStore AS FileTable

WITH

(

    FileTable_Directory = 'MyDocumentStore',

    FileTable_Collate_Filename = database_default

);

GO

If we take a look at the GUI after creating the table, we can see that the table is created under the new folder called FileTables

that is not the only new thing, if we right click at the table that we just created we will see an option called “Explore FileTable Directory” – click that and a windows opens with the content of your FileTable.

If you drop a file into this folder it will be accessible from SSMS, with a simple SELECT from the MyDocumentStore.

Here is how the folder looks:

And if you query the table you get the following: (This query only shows a few of the columns off the table)

Now you can do file manipulation with SSMS, something like renaming the files is just an update statement

UPDATE MyDocumentStore

SET name = 'FileNameChanged.txt'

WHERE stream_id = 'A124C74D-CCD3-E111-87F1-0019D1EE2D0C'

SSIS : Event Handlers

2012-07-15T01:44:00.002-07:00

SSIS is not just about control flows and dataflows, there are other options available in SSIS which extends functionality of SSIS package, and provides a great flexibility in desiging a successfull ETL solution. Lets take a deep insight of what are those options and how we can take benefit of them.

There are four options which extends functionality of SSIS packages.

1. Event Handlers
2. Variables
3. Expressions
4. SQL Queries

In this post i will be covering Event handlers, and in later series i will cover Variables, Expressions and SQL queries

Event Handlers:

Like any programming languages (C# or JAVA) SSIS also provides Event handlers, which provides functionality of performing some task on a specific event during runtime. At runtime of any SSIS task, series of event takes place, and SSIS Desginer has provided with one tab (Event Handler) to program any action that can be performed on occurance of that event.

Event handlers are created in the same manner as control flow but in different tab as shown.

How we create events?

Its as simple as creating control flow of package.

1. Select the executable container to which the handler will be assigned.
2. Select the event to which you wish the event handler to react to.
3. Drag control flow container and task and connect them together with precedence constraints.

How Events are actually gets handled in SSIS?

Events can be handled at task or container or package level, following is the order it will be handled.

1. Task
2. Container
3. Package

For example: If an event is triggered at the task level and no event handler is defined, the event is passed to container level, if no event handler is defined at container level then it will be passed to package level.

How many events are available?

There are 12 different events that can be handled in SSIS package. Below is the list..

OnError The OnError event is caused by the occurrence of an error.

OnExecStatusChanged The OnExecStatusChanged event occurs when an executable changes its status.

OnInformation When an executable reports information during validation or execution, the OnInformation event occurs.

OnPostExecute OnPostExecute is an event that occurs immediately after an executable has completed running.

OnPostValidate
The OnPostValidate event occurs immediately after an executable has finished its validation.

OnPreExecute The OnPreExecute event occurs immediately before an executable runs.

OnPreValidate The OnPreValidate event occurs immediately before an executable begins its validation.

OnProgress
OnProgress is an event that occurs when an executable makes measurable progress.

OnQueryCancel The OnQueryCancel event occurs automatically at regular intervals to determine if the package should continue running.

OnTaskFailed When a task has failed execution, the OnTaskFailed event occurs.

OnVariableValueChanged
The OnVariableValueChanged event occurs when a variable's value is changed.

OnWarning The OnWarning event is raised when a warning occurs.

Event handlers have a number of properties that allow you to

• assign a name and description to the event handler
• enable or disable the event handler
• determine whether the package fails if the event handler fails
• determine the number of errors that can occur before the event handler fails
• override the execution result that would normally be returned at runtime
• determine the transaction isolation level for operations performed by the event handler, and
• determine the logging mode used by the event handler