Dinesh's Blog :::: Being Compiled ::::

TABLE Type and MERGE Statement

2012-01-22T16:45:00.001-08:00

As I get many offline questions regarding the presentation “Reducing RoundTrips” I did, thought to summarize the content we discussed as a post. Session spoke about how to reduce multiple database calls from client to database application when an updated data set (Ex. data table bound to a grid) is required to send from client application to database. In addition to that we discussed the way of handling all three operations: INSERT, UPDATE, and DELETE using a single statement. This is what the session focused on;

MERGE
MERGE statement is used to insert data that does not exist but to update if it does exist. It is an extension of UPSERT which is a known term in other database management systems for performing both UPDATE and INSERT statement using a single technique. The main benefit of this is the facility to manipulate many number of records in a single table using a source as an atomic operation. This eliminates individual statements for INSERT, UPDATE and DELETE operations, one statement which is MERGE handles all three operations. MERGE statement has following elements;

Target: This the table or view (destination) which needs to be updated.
Source: This contains updated data (modified, newly added) and will be used for updating the target. This can be a table, view, derived table, CTE, or table function.
WHEN MATCHED [AND] ...... THEN: This is where the action which needs to be performed when the row in the source is found in the target, should be written. Two WHEN MATCHED clauses are allowed, limiting one for UPDATE as an action and other for DELETE as an action.
WHEN NOT MATCHED [BY TARGET] ...... THEN: The action which performs when the row in the source is not found in the target, has to be written with this. It is usually the INSERT action.
WHEN NOT MATCHED [BY SOURCE] ...... THEN: This is to perform an action when the row in the target is not supplied with the source. Usually it is DELETE.
OUTPUT clause and $Action: The standard inserted and deleted virtual tables are available with MERGE. In addition to that $Action provides the operation performed; INSERT, UPDATE, DELETE. This is normally used for logging/auditing.

In order to use MERGE, a source must be defined and filled. Usually the source is defined and filled by the client but for various limitations and less facilities, we used to iterate the filled-source at the client site itself and send one row at a time to database, which makes many calls to database for updating all records in the source.

TABLE Type
This can be overcome using TABLE data type. TABLE data type can be created as a user-defined data type definition and can be used as either variable or parameter. Following are the key elements of creating a type;

CREATE TYPE: This is used for creating the user defined type. It has to be created as a TABLE and structure has to be defined.
READONLY keyword: If the type is used as a parameter, READONLY keyword has to be used. It is as an OUTPUT parameter is not supported.
SqlDbType.Structured: When a data table from a .NET application is sent, parameter data type must be declared as System.Data.SqlDbType.Structured.

Here is a sample code for all;

This code creates a table called Customer and inserts 4 rows. In addition to that it creates a log table too, for recording operations.

USE tempdb

GO

-- create a table for customer

CREATE TABLE dbo.Customer

    CustomerId int identity(1,1) not null

    , CustomerCode char(4) not null

    , FirstName varchar(25) null

    , LastName varchar(25) not null

    , CreditLimit money not null

    , IsLocal bit not null

    , LastModified smalldatetime not null

GO

-- insert four records

INSERT INTO Customer

    (CustomerCode, FirstName, LastName, CreditLimit, IsLocal, LastModified)

VALUES

    ('C001', 'Dinesh', 'Priyankara', 10000, 1, getdate())

    , ('C002', 'Jane', 'K', 10000, 1, getdate())

    , ('C003', 'Martinie', 'Ku', 10000, 0, getdate())

    , ('C004', 'Joe', 'Anderson', 10000, 0, getdate())

GO

CREATE TABLE dbo.CustomerLog

    CustomerCode char(4) not null

    , DateModified datetime not null

    , Action varchar(15) not null

GO

Here is the stored procedure for accepting an updated data set using TABLE type and updating Customer using MERGE.

-- create a stored procedure for accepting created TABLE type as a parameter

-- note that READONLY has to be used

CREATE PROC dbo.InsertCustomer @CustomerType dbo.CustomerType READONLY

AS

BEGIN

    INSERT INTO dbo.CustomerLog

    (CustomerCode, DateModified, [Action])

    SELECT o.CustomerCode, getdate(), o.[Action]

    FROM

        (MERGE INTO dbo.Customer AS c

        USING @CustomerType AS t

            ON c.CustomerCode = t.CustomerCode

        WHEN MATCHED AND t.IsLocal = 0 THEN

            UPDATE SET c.FirstName = t. FirstName

                    , c.LastName = t.LastName

                    , c.CreditLimit = t.CreditLimit

                    , c.LastModified = getdate()

        WHEN NOT MATCHED THEN

            INSERT (CustomerCode, FirstName, LastName, CreditLimit, IsLocal, LastModified)

                VALUES

                    (t.CustomerCode, t.FirstName, t.LastName, t.CreditLimit, t.IsLocal, getdate())

        WHEN NOT MATCHED BY SOURCE THEN

            DELETE

        OUTPUT isnull(inserted.CustomerCode, deleted.CustomerCode) as CustomerCode, $ACTION AS Action) AS o

END

GO

Everything is ready from database level. Here is a sample code for .NET application.

SqlConnection connection = new SqlConnection(@"Server=.\SQL2008R2;Integrated Security=SSPI;Database=tempdb");

DataTable customer = new DataTable("Customer");

customer.Columns.Add("CustomerCode", typeof(string));

customer.Columns.Add("FirstName", typeof(string));

customer.Columns.Add("LastName", typeof(string));

customer.Columns.Add("CreditLimit", typeof(decimal));

customer.Columns.Add("IsLocal", typeof(bool));

customer.Rows.Add("C001", "Dinesh", "Priyankara", 10000, true); // no change and no update

customer.Rows.Add("C002", "Jane", "Kani", 25000, true); // should not update

customer.Rows.Add("C004", "Joe", "Andrew", 35000, true); // update all

customer.Rows.Add("C005", "Kate", "Neo", 35000, true); // new record

SqlCommand command = new SqlCommand("InsertCustomer", connection);

command.CommandType = CommandType.StoredProcedure;

SqlParameter parameter = new SqlParameter("@CustomerType", System.Data.SqlDbType.Structured);

parameter.Value = customer;

command.Parameters.Add(parameter);

connection.Open();

command.ExecuteNonQuery();

connection.Close();

Enjoy!

Is SQL Server @@IDENTITY a Global Function?

2011-11-01T02:10:00.001-07:00

The @@IDENTITY is a system function that returns last generated identity value. But the question is, is it the last identity generated by my code? Last generated by my session? Or last generated by the system for any user?

Understanding what exactly it returns help you to code your database modules accurately. Let’s try this with a small example.

CREATE TABLE T1 (Id int IDENTITY(1,1), Value varchar(20))

CREATE TABLE T2 (Id int IDENTITY(100,1), Value varchar(20))

GO

CREATE PROC InsertT2

AS

BEGIN

    INSERT INTO T2 (Value) VALUES ('Test')

END

GO

CREATE PROC InsertT1

AS

BEGIN

    INSERT INTO T1 (Value) VALUES ('Test')

    EXEC InsertT2

    SELECT @@IDENTITY

    SELECT SCOPE_IDENTITY()

END

GO

EXEC InsertT1

The result of InsertT1 execution will 100 and 1. As you see in the result, @@IDENTITY returns the last generated identity value for my session, not for the scope I am in. If you use this function to get the last generated identity value for your insert, result of it may be wrong unless no other code is executed that inserts a record to another table with IDENTITY property enabled. Note that SCOPE_IDENTITY returns the identity value for the scope, hence if the requirement is for get the value generated for current scope, use it.

There is another function that can be used for getting generated identity value, which is called IDENT_CURRENT. This function accepts the table name as a parameter and returns the last generated identity value for given table, by any scope, any session, any connection.

Reporting Services Parameters: Adding “All” item.

2011-09-19T18:00:00.001-07:00

There are some instances we have to create parameters with multiple items, including an item representing all items in the list, mostly called as “All”. There are various ways of doing it. If the list is dynamic and data source is OLAP, you get this item automatically. If it is not OLAP, and loading from OLTP database, this item has to be manually added and programmatically handled.

Here is an easy way to handle it. Have this additional item in a new query and use UNION for joining it to main query.

SELECT  'All Years' AS OrderYear

    , 0 AS OrderYearValue

UNION

SELECT DISTINCT

    CONVERT(varchar(100), YEAR(OrderDate)) AS OrderYear

    , YEAR(OrderDate)  as OrderYearValue

FROM Sales.SalesOrderHeader

ORDER BY 2

Now data source contains the item. In order to get this handled with query parameter, follow below code;

SELECT {your columns}

FROM Sales.SalesOrderHeader

WHERE YEAR(OrderDate) = @Year

    OR @Year = 0

Happy Coding .

PIVOT and UNPIVOT: Integration Services

2011-08-31T21:23:00.001-07:00

As you all know, Pivoting and Unpivoting are supported with SSIS but the implementation of PIVOTing is bit difficult when compared with UNPIVOTing. This post explains the implementation of both PIVOT and UNPIVOT transformations that come with SSIS with all possible explanations.

Pivot allows us to convert normalized dataset into less normalized format, transferring (pivoting) row values into columns. In simple term, it identifies distinct values in a designated column and makes columns based on identified values (note that it is not fully dynamic and automated). Here is an example.

Example shows how PIVOT works, you can see that values (months) in OrderMonth column have been converted into columns, combining with OrderAmount column. Now let’s see how we can do this with SSIS. The code below is the source for pivoting that returns Orders placed by customers for months and years.

   1:  SELECT

   2:    YEAR(OrderDate) OrderYear

   3:     , p.LastName Customer

   4:    , DATENAME(mm, OrderDate) OrderMonth

   5:    , SUM(SubTotal) OrderAmount

   6:  FROM Sales.SalesOrderHeader h

   7:    INNER JOIN Sales.Customer c

   8:      ON c.CustomerID = h.CustomerID

   9:    INNER JOIN Person.Person p

  10:      ON p.BusinessEntityID = c.PersonID

  11:  GROUP BY

  12:    YEAR(OrderDate)

  13:    ,p.LastName

  14:    ,DATENAME(mm, OrderDate)

  15:  ORDER BY 1, 2

In order to do this in SSIS, create a new project and have a Data Flow Task in Control Flow. Place an OLE DB Data Source in Data Flow Task and set the above query, making the connection to AdventureWorks2008R2 database. Then, drag PIVOT transformation on to Data Flow and connect them using Connection Line.

Pivot configuration is little bit tricky, not as easy as T-SQL PIVOT. When you open the Editor of it, you get three tabs. Second tab allows you to marks columns for pivoting, selecting them from input flow.

Next tab which is Input and Output Properties, is for instructing SSIS the way of using input columns for pivot operation. The Pivot Default Input node in left pane allows us to set the usage of input columns for pivoting. The Pivot Default Output allows us to form the output columns based on input columns and their usage. Let’s first have a look on Pivot Default Input node.

If you expand the next level of Pivot Default Input, which is Input Columns, you can see all columns available for the operation. Selecting one column loads its properties to right pane, which contains a property called PivotUsage that accepts a numeric value from 0 to 3. Here is the way of deciding the value for PivotUsage property.

Value	Meaning
0	Column does not participate in PIVOTing, it is passed through unchanged to the output. If PIVOTing identifies multiple values in an output row, only the first value of them will be used with the output row. In our example, we do not have any column to be marked as 0.
1	Column is the key (Set key) for generating the pivot output rows. Values in this column will be used to identify unique values for rows. We can mark one or more columns as 1 but need to make sure that combination of them make record unique without providing any duplicates. In our example, we have to column to mark as 1: OrderYear and Customer.
2	Values in this column (Pivot Column) will be used for creating columns in the pivot output. In our example, OrderMonth is the column which should be marked as 2.
3	Values in this column will be used to generate the values for columns created from pivot column. In our example, OrderAmount should be set as 3.

Here is the way of setting it;

Next step is configuring Pivot Default Output. If you expand the node, Output Columns node is appeared. In order to defines columns, including columns created through pivot column, we have to create them. Make sure you have selected Output Columns node and click on Add Column for creating 14 columns. Once the columns are created, name them with Name property as OrderYear, Customer, January, February, March, April, May, June, July, August, September, October, November, and December.

Next is, linking output columns with input columns. This will be done through a property called SourceColumn in output columns. The value of the SourceColumn has to be taken from relevant input column’s property called LineageID. Find the LineageID of OrderYear input column (in my case it is 17, see above image) and set it in SourceColumn of OrderYear output column. Do the same for Customer column too.

Next columns are month columns. The property SourceColumn of all month columns has to be set with LineageID of OrderAmount. In addition to that, a property called PivotKeyValue should be filled too. This property should be filled with values coming from PivotColumn. For example, January column’s property should be filled with “January”, and February column’s property should be filled with “February”.

Package is done. Let’s send the output to an Excel file for testing purposes. Here is the package execution and the output of it.

SSIS UNPIVOT is not as tricky/difficult as PIVOT. Let’s see how UNPIVOTing can be done with SSIS. Easiest way to understand this is, reversing the process, unpivoting Excel sheet created with previous example. Let’s have a Data Flow Task and Excel Source on it, connecting to the Excel we created. Add an Unpivot transformation and connect it with the source.

Let’s configure UNPIVOT transformation. Open the Unpivot Transformation Editor and configure it as follows;

Select Pass Through checkbox only for OrderYear and Customer.
Select all month columns. This loads all months into the grid.
Give a destination column name (Eg. OrderAmount) for month columns. Make sure that same name is set for all columns. This column will hold values related to months.
Make sure Pivot Key Value has been properly set. This value will be set as a row value for column which will be unpivoted.
Finally, give a name for Pivot Key Value column (OrderMonth).

Done. Have a destination as you want and see the result.

Default Logging: Microsoft SQL Server Integration Services

2011-07-10T20:39:00.001-07:00

Everybody knows how important logging is, when it comes for troubleshooting. It applies for SSIS packages too. Unfortunately, most of developers, pay not much attention for this, and later regret for not having any records on package executions. If you have not implemented any mechanisms for logging and need to know how often the package gets executed or whether the package is started, you are not stranded. Integration Services has done the needful for you.

Integration Services has a default logging behavior. It captures starting and ending events, and adds two log entries to Windows Event Viewer. If you run the package with Business Intelligence Studio while it is being designed, you should see two entries in Windows Event Viewer as follows;

This behavior cannot be stopped. It logs not only when the package is run with BIDS, with DTEXEC too. As per my experience this is not enough for troubleshooting if it is a complex package, so, make sure you have used other out-of-the-box logging features for recoding your package processes.

How the language “SQL” is born?

2011-07-05T20:04:00.001-07:00

Simple thing that you may not know ……….

Structured Query Language was born in 1970, supporting Edgar F. Codd’s Relational Database Model. It was invented at IBM by Donald D. Chamberlin and Raymond F. Boyce (who introduced 3.5 NF) for IBM’s RDBMS called System R. The initial name of it was Structured English Query Language (SEQUEL) but changed to SQL later as SEQUEL was a trademark of another company.

The first commercially available implementation of SQL was released by Relational Software Inc. (now known as Oracle Corporation). It was in June 1979, for Oracle V2. Relational Software Inc. started developing their own RDBMS based on Codd’s theories in 1970s.

Not only SQL, there were other RDBMS and SQL related languages. In 1970, University of California, Berkeley created a RDBMS named Ingres (Known as Open Source RDBMS) and QUEL was the language created for managing its data. With various different implementations, later it evolved into PostgreSQL.

IBM continued with its System R and SQL Implementation, making it as a commercial product named System38. It was in 1979. Now it has been evolved into DB2 which was released in 1983.

SQL has many extensions now. Some of them are;

Oracle – PL/SQL
IBM – SQL PL
Microsoft – T-SQL

SQL was standardized by American National Standard Institute (ANSI) in 1986 as SQL-86. In 1987, it was standardized by International Organization for Standardization (ISO) too. It has been revised in many times, starting with SQL-86 to SQL:2008.

Script Task cannot be debugged: Integration Services

2011-07-03T21:00:00.001-07:00

If you run on 64-bit environment and try to debug a script task after placing a breakpoint, it is not going to work as you expect. The reason for this is 64-bit environment. There are few things that do not support on 64-bit mode but you can still configure them with 32-bit SSIS designer; Business Intelligence Development Studio. If you experience problems such as connecting to Excel via Microsoft OLE DB Provider for Jet or debugging Script task, while running in 64-bit mode, all you have to do is, changing the SSIS runtime into 32-bit. This is done by changing a property called Run64BitRuntime on the Debugging page.

Note that this property is used and applied only at design time. When you execute the package in a production server, environment for it is based on installed dtexec utility. The dtexec utility is available in 64-bit mode. If you execute the package in 64-bit environment, 64-bit dtexec will be automatically selected and run. If need it to be executed in 32-bit mode, use command prompt for running it. When you install 64-bit Integration Services, it installs both 32-bit and 64-bit. As command prompt uses PATH environment variable for finding directories, and 32-bit version path (C:\Program Files(x86)\....) appears before 64-bit version path, it uses 32-bit dtexec for executing packages.

If the package needs to be scheduled with SQL Agent and run in 32-bit mode, that has to be specifically instructed as SQL Agent uses 64-bit version without using PATH environment variable. It can be done by setting Use 32 bit runtime on the Execution Options of the Job Step.

How the term “Business Intelligence” is born?

2011-05-15T18:33:00.001-07:00

This is not about processes or usage of Business Intelligence solutions, it is about usage of this term. Unfortunately it looks like this is unknown to many (Unawareness for this is reasonable but I can remember that when a picture of Charles Babbage is shown at a session, many did not recognize or aware of him).

The term “Business Intelligence” firstly appeared in 1958. It was used by a IBM researcher, Hans Peter Luhn, in one of his articles. His definition for it was;

“The ability to apprehend the interrelationships of presented facts in such a way as to guide action towards a desired goal”

Although Hans introduced (or used) this word for processes related to business intelligence, it was not a widespread term until late 1990s. It was the time for Decision Support Systems. In 1989, Howard Dresner proposed “Business Intelligence” as an umbrella term but it looks like the real usage of the term started in 1996. Later Howard became an Analyst for Gartner Group, and it is Gartner Group (Gartner, Inc.) who labeled this on collective technology used for DSSs. This is how it was published;

“Data analysis, reporting, and query tools can help business users wade through a sea of data to synthesize valuable information from it—today these tools collectively fall into a category called ‘Business Intelligence.’”

PerformancePoint web-parts cannot be connected (linked)

2011-05-12T10:33:00.000-07:00

Few days back, I faced for a strange issue with PerformancePoint web-parts. It was a simple thing, all I had was, a web page created with SharePoint and couple of web-parts. Once the page is open in design mode and web-parts are placed, tried to link two web-parts (a report web-part and a filter) through a connection as below;

Funny thing was, though it allowed me to create the connection (link) and save, it did not get saved permanently. If I open the connection window again, the created connection is missing.

I could not find the issue immediately but it was a terrible thing. Thanks for my colleagues, they have found the issue. It is with Internet Explorer. If you try to do this with Internet Explorer 8.x, it is not going to work. For some of my colleagues, older versions of IE have been worked. For some, older version of FireFox has been worked. Finally what I did was, installed FireFox 3.6 and got it done. Note that it is not going to work with FireFox 4.x too.

Anyone faced this issue? Why SharePoint gives an issue with latest browsers? If you know anything on this, please share with me.

Object Name Resolution – SQL Server

2011-05-01T20:16:00.001-07:00

Understanding how SQL Server resolves objects’ names will be an effective remedy against performance issues. This name resolution happens if securables are not fully qualified. A statement like SELECT SalesOrderID FROM SalesOrderHeader will be a candidate for this. As you see, the SalesOrderHeader is not written as a fully qualified name, hence SQL Server tries to resolve it by adding Schema owned by user connected. If SQL Server cannot find an object like ConnectedUser’sSchema.SalesOrderHeader then it tries to find an object called dbo.SalesOrderHeader as the second step. Error is thrown, if it is not successful too. Here is an example for it;

     USE AdventureWorks2008

    GO

    -- creating a new table with dbo schema

    SELECT TOP (1) * 

    INTO SalesOrderHeader

    FROM Sales.SalesOrderHeader

    -- Create two logins for testing

    CREATE LOGIN HR_Manager WITH PASSWORD = '123'

        , CHECK_POLICY = OFF;

    CREATE LOGIN Sales_Manager WITH PASSWORD = '123'

        , CHECK_POLICY = OFF;

    GO

    -- Adding them as users with default schemas

    CREATE USER HR_Manager WITH DEFAULT_SCHEMA = HumanResources

    CREATE USER Sales_Manager WITH DEFAULT_SCHEMA = Sales

    GO

    -- Given users permission on data reading

    sp_addrolemember 'db_datareader', 'HR_Manager';

    sp_addrolemember 'db_datareader', 'Sales_Manager';

    -- Execute as HT_Manager and see

    EXECUTE AS USER = 'HR_Manager'

    GO

    -- This shows records from newly created

    -- table, not from original table

    SELECT * FROM SalesOrderHeader

    -- This throws an error

    SELECT * FROM SalesOrderDetail

    REVERT

    GO

    -- Execute as Sales_Manager

    EXECUTE AS USER = 'Sales_Manager'

    GO

    -- Both statements will work

    SELECT * FROM SalesOrderHeader

    SELECT * FROM SalesOrderDetail

    REVERT

    GO

As you see with the code, when HR_Manager executes SELECT statements, as the first step, names of them will be resolved as HumanResources.SalesOrderHeader and HumanResources.SalesOrderDetail. SQL Server does not find any objects and do the next step, resulting dbo.SalesOrderHeader and dbo.SalesOrderDetail. A match will be found for first one as we have created one with dbo schema but not for the second. In that case, second statement throws an error.

When Sales_Manager executes, SQL Server resolves objects’ names as Sales.SalesOrderHeader and Sales.SalesOrderDetail. In that case, both statements are successfully executed.

Even though Sales_Manager executes statements without any error, it would be always better to make securables as fully qualified objects, that helps SQL Server to quickly execute the code without performing an additional task.

Database Option: CURSOR DEFAULT {LOCAL | GLOBAL} - Is CURSOR Global?

2011-04-30T21:33:00.001-07:00

Setting the value of this option as GLOBAL has been understood wrongly by many developers. All you have to remember is, this “global” does not refer all connections, it refers the current connection. If either database option or at the declaration of cursor, GLOBAL is set, it means that scope of the cursor is global to the connection. Simply it can be referenced in any stored procedure or batch executed by same connection. If LOCAL is used, it is only for the batch which it was created. See the code below;

     -- Parent SP with a cursor

    CREATE PROC TestCursorParentSP

    AS

    BEGIN

        DECLARE @SalesOrderID int

        DECLARE cur CURSOR GLOBAL

        FOR

        SELECT SalesOrderID 

        FROM Sales.SalesOrderHeader 

        OPEN cur

        FETCH NEXT FROM cur INTO @SalesOrderID

        SELECT @SalesOrderID

        EXEC TestCursorChildSP

        CLOSE cur

        DEALLOCATE cur

    END

    -- Child SP that accesses cursor declared by parent

    CREATE PROC TestCursorChildSP

    AS

    BEGIN

        DECLARE @SalesOrderID int

        FETCH NEXT FROM cur INTO @SalesOrderID

        SELECT @SalesOrderID

    END

    -- Executing parent sp

    -- Get values from both SPs

    EXEC TestCursorParentSP

If the cursor is declared as LOCAL, child SP will not be able to access it.

New Prices for Microsoft Certifications Exams

2011-04-26T17:34:00.001-07:00

If you plan for doing MS exams, better hurry up. Following certifications are slated for a price increase:

Microsoft Certified Technology Specialist (MCTS)
Microsoft Certified IT Professional (MCITP)
Microsoft Certified Professional Developer (MCPD)
Microsoft Certified Desktop Support Technician (MCDST)
Microsoft Certified Systems Administrator (MCSA)
Microsoft Certified Systems Engineer (MCSE)
Microsoft Certified Application Developer (MCAD)
Microsoft Certified Solution Developer (MCSD)
Microsoft Certified Database Administrator (MCDBA)

See more detail:
http://www.microsoft.com/learning/en/us/certification/cert-pricing-FAQ.aspx

TOP WITH TIES: Have you shown your TOP 10 Customers properly?

2011-04-24T10:06:00.001-07:00

Think about a scenario where you have to show your top 10 customers based on their purchases. If the record set is as below;

and if you show first 10 records using TOP 10, have you shown all best customers? See the 11th record, it is same as 10th order, hence he should be added to top-10 list. In this case, top-10 list becomes top-11 list. But we cannot change the query as TOP 11 because it is not always guaranteed that 11th record is same as 10th record. In that case, what would be the way?

The solution is, TOP 10 WITH TIES. It adds additional rows to the resultset if any tied rows for 10th are there. See the code below;

Note that WITH TIES can be only used with SELECT statement and ORDER BY must be used too.

SQL Server 2008 R2 SP1–CTP is available for downloading

2011-04-23T19:53:00.001-07:00

Microsoft announces the availability of Service Pack 1 as Community Technology Preview for SQL Server 2008 R2. Here are some of enhancements come with SP1;

Dynamic Management Views for increased supportability
ForceSeek for improved querying performance
Data-tier Application Component Framework for improved database upgrades
Disk space control for PowerPivot

Here is the link for downloading: http://www.microsoft.com/downloads/en/details.aspx?FamilyID=bd200f8e-ba8a-45e3-af59-e28a9e2d17df&displaylang=en

Note that this is CTP, not for installing in live environment.

Does Referential Integrity improve performance?

2011-04-21T18:51:00.001-07:00

I believe that everybody knows that Referential Integrity is for maintaining the relationship between two entities; Parent and Child. It makes sure that no child records is exist without a parent. Referential Integrity is implemented with Foreign Keys. The foreign key column can be set as Nullable, in that case it allows to have a child without a parent .

While I was going through my one of my old presentations, I found a slide related to this (yes, it is good to keep old presentations, human forgets things). Let me explain it with a code;

The above code is executed against AdventureWorks2008 database. Note that it gets records only from Detail table even though Header table is joined. You may say that this join is useless because it does not get data from Header. True, but see, though we have linked the header to detail, as per the plan, SQL Server does not access the header. It knows that referential integrity is implemented and existence of parent records is guaranteed, hence it does not need to access the header. Now let’s remove the relationship between header and detail, and execute the same.

Query is same but in this case, SQL Server scans the header table. The reason is it does not know whether records are exist in header for every child, it accesses the header though there is no request for values in header. Can you see, referential integrity is not only for making sure that database is consistence, it improves the performance too.

Ordering Attribute Members of Dimensions in Analysis Services

2011-04-17T10:52:00.001-07:00

As each attribute in a dimension can have a key column and optionally name column, attribute can be sorted by either key or name column. Sorting is done with OrderBy property. Once the property is set, attribute is sorted in ascending when it is displayed. This is okay with most cases but in some cases sorting is needed in a different way. Examples cane be, sorting in descending order, sorting by not its key or name, by some other attribute. This post discusses these exceptional cases.

Sort by some other attribute
Good scenario for this is Month attribute in a Month dimension. Usually we set Month attribute’s Key Column as MonthKey and its Name Column as MonthName. If the sorting is set as Key with OrderBy property, months will be ordered chronologically only if you have inserted them in source table in an order. If they are not inserted in an order, months will not be ordered as we want. Again, if OrderBy property is set as Name, months will be displayed in alphabetical order which is not the way we usually want. See the image below.

Note that records are not inserted in an order. Month attribute’s key is set with MonthKey and its name is set with MonthNameWithYear. You can see how they are displayed with OrderBy set as key and name.

In order to have the proper ordering on Month attribute, we need an additional column to be used for sorting. Best candidate is MonthNumberOfYear column. It can be used as sorting key for Month. One requirement for doing this is, have a relationship between Month and MonthNumberOfYear, in other words, making MonthNumberOfYear as Related Attribute for Month. By default, when the dimension is created, wizard adds all the dimension’s attributes as related attributes of the key. In this case, it has been added too. If not, you need to manually add this through Attribute Relationships (a tab) in BIDS’s Dimension designer.

Once the column is set as a related attribute, change the OrderBy to AttributeKey and set OrderByAttribute as MonthNumberOfYear.

Once processed, months will be displayed in an order as we want. There are two things to be considered in this setting. If MonthNumberOfYear column is not required for querying, you can hide it by setting AttributeHierarchyEnabled property to false. Other thing is, Relationship Type between an attribute and related attribute. It can be set as either Rigid or Flexible. If the relationship is fixed over time, not going to change (Type 2), rigid is the best option. If the relationship can change over time (Type 1), type has to be set as Flexible.

Descending Order
Once the sorting is set for an attribute, it is sorted in ascending order. That is the only possible sorting order supported by Analysis Services. If descending order is required, just like the previous one, support is needed from another attribute that maintains value in descending order. For example, if months need to be sorted in descending, MonthNumberOfYear has to be filled with values 12 to 1, not 1 to 12.

Logical Query Processing Phases in SELECT statement

2011-04-12T21:37:00.001-07:00

The SELECT statement can be constructed with various clauses as per the requirement. It can be as simple as just a set of columns from a table or as complex as a set columns from multiple tables with aggregate functions. Understanding how SQL Server executes added statements will be worthwhile for constructing the SELECT quickly and correctly as per the requirement. Here is the logical processing order in SELECT statement;

Note that all clauses have not been used with above code. Here is the order of all possible phases;

As you see, it is not the order we have written the query, hence keeping this order in mind is absolutely helpful.

SQL Server could not create a subscription for Subscriber: MSDTC on server is unavailable.

2011-03-08T03:53:00.001-08:00

When I configured a subscription for Transactional replication with updatable subscription, the following error occurred.

SQL Server could not create a subscription for Subscriber 'DINESH-PC\SQL2008Instance2'. MSDTC on server 'DINESH-PC\SQL2008INSTANCE2' is unavailable.

I did not want to set the subscription for Immediate Updating mode, so set to Queue changes and commit when possible.

I am aware that MSDTC is used for Immediate Updating (Simultaneously commit changes). Then why I am getting such error? I did not select that option.

The reason is the wizard. When the subscription is created through the wizard, whatever the selection you make, it makes both Immediate Updating and Queued Updating available for subscription. If you do not want both, do not use the wizard, create the subscription manually using stored procedures. Note that you can use the wizard for generating scripts for the whole task without creating the subscription. The generated script contains four stored procedures;

sp_addsubscription: This adds a subscription to publication and it has to be run in publisher. You need to make sure that @update_mode is set as ‘queued tran’.
sp_addpullsubscription: This adds a pull subscription to subscriber, hence needs to run in subscriber. Just like previous one, make sure that @update_mode is set as ‘queued tran’.
sp_link_publication: This is to use with Immediate Updating, hence you do not need to execute this.
sp_addpullsubscription_agent: This adds a scheduled agent job to synchronize pull subscription with transactional publication. Run it in subscriber.

Once you run everything, you have a subscription with queued updating mode.

SQL Server Updatable Subscriptions: What is the best replication type for this?

2011-03-07T16:23:00.001-08:00

Replication is used for copying data and database objects from one location to another on a recurring basis. The topology of replication is vary, it can be a simple implementation with two servers or a complex implementation with many local and remote servers and mobile devices. Servers involved are designated with server roles such as Publisher, Distributor and Subscriber and their responsibilities are publishing, distributing, and finally subscribing published data. In some scenarios, subscribers use the subscription only for reading data. In other scenarios subscribers make modification to subscription (change data) and send back to publisher. Such subscriptions are called as Updatable Subscription. SQL Server provides many ways to implement a replication with updatable subscription. This post discusses some considerations and guide lines for selecting the best replication type with updatable subscription suit for your requirement.

The general overview of Replication can be illustrated as below.

Publisher: Publishes data with publication.
Distributor: Distributes data. This server holds metadata, historical data and replicated data for some replication types.
Subscriber: Receives data from publications. They can make changes to subscribed data too.

Updatable Subscription
SQL Server provides three different types of Replications. They are:

Snapshot replication
Transactional replication
Merge replication

Changing data at subscriber and sending changes back to publisher facility is not available with all types. Transactional replication and Merge provides facilities for it.

Transactional Replication is mainly used in Server-to-Server scenario. Subscribers are initiated with a snapshot of publication and then transactions occurred are sent to them in incremental manner. This replication type allows subscribers to update data and send back to publisher. If it is required, Transactional Replication with updatable subscription option is available at creation of publication. This works in two ways:

Immediate Updating: Uses MSDTC for sending data from subscribers to publisher and applying. It is done through triggers added in published tables in subscription.
Queued Updating: Changes are sent to a queue. The Queue reader agent pull changes and apply to publisher. It is done with triggers too.

Read more on these two at: http://msdn.microsoft.com/en-us/library/ms151794.aspx

Now, where we can use Transactional replication with updatable subscription? Here are some guidelines for selecting this as your replication type:

Scenario is Server-to-Server.
Number of subscribers are less than 10.
Subscribers update data infrequently.
Subscribers, distributor and publisher are connected most of the time (For Immediate updating, regular connection is required as update happens synchronously).
Subscribers need all changes, not the last change (Eg. if there are 100 transactions for a record, all are required by the subscriber, not the final state of the record)
Expect less update conflicts with queued updating. This type does not have rich facilities for handling conflicts.

**Note that MSDN says that this feature will be removed in a future version of SQL Server, so it is not advisable to use this if you plan to upgrade your system with future versions.

Merge replication is mainly for Server-to-Client environment. This allows subscribers to make modification on replicated data and send them back to publisher. Use following guidelines when selecting Merge as your replication type for updatable subscription:

Scenario is Server-to-Client.
Many number of updating subscribers (Merge is designed for this).
Expect conflicts (Good support on conflict detection and resolving)
Subscribers need the final state of the change, not all transactions.
Subscribers, distributors and publisher are not always connected.
Subscribers need a portion of publication, not the entire one (This is done with dynamic filters in publication).

Okay, you can consider given guidelines and select the best type for your requirement. How about Peer-to-Peer replication? Can we use it too for handling this?

Peer-to-peer replication is scale out and high availability solution. It maintains copies of data across multiple servers, referred as nodes. Its foundation is Transactional replication and works in similar manner. The main difference is, nodes are not designated as publisher or subscribers, all are publishers and subscribers. Here are some guidelines for selecting it:

Requires high availability (with other types, if publisher goes down, replication topology is down too).
Less number of nodes (would be better to have 10 or less).
Filtering of publication is not required.
Do not expect much conflicts.

Self-Service Business Intelligence with SQL Server 2008 R2 – Speaking engagement in SQL Server Sri Lanka User Group

2011-03-04T00:37:00.001-08:00

The February meeting of SQL Server Sri Lanka User Group meeting was held on last Wednesday and sessions were done by Dinesh Asanka and me. My session was on Self-Service Business Intelligence with SQL Server 2008 R2, focusing on PowerPivot. The agenda for the sessions was:

What’s new in SQL Server 2008 R2?
What is Business Intelligence?
Introduction to New Paradigm
Introduction SQL Server PowerPivot
Future Releases
Q & A

The presentation is available for downloading: http://www.sqlserveruniverse.com/v2/SSSLUG/Downloads.aspx

Guest user account

2011-02-20T19:46:00.001-08:00

The Guest user is an account which is automatically created when a database is created. This account is used when a login connects to a database without having a user account created in it. By default, guest is disabled, to enable and disable, use the codes below;

     -- to enable

    GRANT CONNECT TO GUEST

    -- to disable

    REVOKE CONNECT FROM GUEST

The recommended way of managing guest account is, have it in disabled status. If you have enabled it for some reason, permission can be granted either using graphical interface or through T-SQL.

    -- Give data read permission to guest on Customer table

    GRANT SELECT ON Sales.Customer TO GUEST

If guest is enabled in AdventureWorks2008 database and above permission is granted, when a login (eg. Jane) connects to AdventureWorks2008, she will be able to read data from Sales.Customer though she has no user account created in AdventureWorks2008.

Template Explorer in SQL Server

2011-02-07T19:49:00.001-08:00

Although this is not much popular among DBEs, it is one of the facilities should be used. The Template Explorer gives us various type of templates for constructing objects. For an example, if you need to create a database using T-SQL, the template of the CREATE DATABASE can be taken from the Template Explorer and altered as you need. This definitely speeds up coding though it discourages remembering syntax.

The Template Explorer can be opened using View –> Template Explorer (or Ctrl+Alt+T) menu. Templates are grouped into three: SQL Server, Analysis Server, SQL Server Compact. Templates in a group are categorized too. For an example, if you want to create a database with multiple file groups, CREATE DATABASE ON MULTIPLE FILEGROUPS in DATABASE category under SQL SERVER group can be used.

Template can be brought into query window by dragging and dropping or double-clicking. Once placed, need to replace parameter values with your values. Although you can manually do it, easiest way is, using Specify Values for Template Parameters window. It can be accessed using Query –> Specify Values for Template Parameters menu.

You can create your own templates and have them in Template Explorer. Start it with creating a folder or add new ones in given folders (categories). This facility is open through right-clicking on the folder in Template Explorer and selecting New. One thing you need to remember is the way of adding parameters. Parameters should be enclosed with “<” and “>” and should contain three parts: Parameter name, type, value. You can have the same for both Parameter name and value, allowing user to replace the “value” with their value. For an example, parameter for database name would be: <Database_Name, sysname, Database_Name>.

sys.database_files: size column and why 128?

2011-02-06T16:55:00.001-08:00

It came up while my class was being conducted. We were discussing of finding the size of files attached to a database and available free space of files. As usual I used my sample codes to show few things, this is one of them:

     SELECT name, size/128 size FROM sys.database_files

Now, why 128? It did not come into my mind immediately . Result of being a designer.

Size column shows current size of the file in 8KB pages. It is the number of pages that have been allocated. So, why 128? It is for converting to MB. There are 128 8KB pages in 1MB. Here is my altered code;

    SELECT name, size/128 SizeInMB

        , FILEPROPERTY(name, 'SpaceUsed')/128 SpaceUsedInMB 

    FROM sys.database_files

    -- or

    SELECT name, size*8 SizeInKB

        , FILEPROPERTY(name, 'SpaceUsed')*8 SpaceUsedInKB 

    FROM sys.database_files

Installing and Configuring PowerPivot for SharePoint 2010

2011-02-02T17:17:00.001-08:00

Microsoft PowerPivot is slowly becoming an essential technology for Business Users addressing Self-Service Business Intelligence. It is innovated by Microsoft SQL Server team as a part of Microsoft SQL Server 2008 R2 release and comes in two flavors; PowerPivot for Excel and PowerPivot for SharePoint. I have already written a post on Excel add-in (Creating Self-Service Business Intelligence dashboard with PowerPivot – Part I) which discusses dashboard creation with Excel and PowerPivot. This post discusses about Installing PowerPivot for SharePoint 2010 with following sections;

Excel and SharePoint
Installing PowerPivot for SharePoint 2010
Error: Could not load assembly
‘Microsoft.AnalysisServices.SharePoint.Integration.dll’
SQL Server Analysis Services Installation – VertiPaq
Deploying PowerPivot solution packages to Web Application
Other Services required
Creating PowerPivot Service Application
PowerPivot Feature Integration for Site Collections
Configuring PowerPivotUnAttendedAccount for Data Refresh

Excel and SharePoint
Why two technologies? We can consider that Excel is for authoring PowerPivot applications. It allows to create an application with one or many data sources and then facilitates data analysis in a multi-dimensional structure. It does not need a constant connection to used data sources, everything will be stored inside Excel Workbook and if want, data can be refreshed. SharePoint can be considered as a hosting environment for PowerPivot applications. We usually go for SharePoint if collaboration, sharing and reporting are required.

Installing PowerPivot for SharePoint 2010
There are two components need to be installed. PowerPivot System Service and Analysis Services in VertiPaq mode which is a local, in-process Analysis Services engine. Let’s start with Analysis Services.

Installing Analysis Services for PowerPivot is straight forward. I have already set up my SharePoint 2010 farm. It uses SQL Server 2008 R2 that exist in same box. Note that my installation contains full SQL Server product suite, including SQL Server Analysis Services, hence new instance of Analysis Services for PowerPivot will be a named instance. There is a known issue you might face if SQL Server installation is already exist. It is;

Could not load assembly ‘Microsoft.AnalysisServices.SharePoint.Integration.dll’
You get this error if you have a prior installation of SQL Server 2008 R2. Unfortunately it comes at the completion of the installation.

Solution for this is simple. Create a text file as below and save it as Setup100.exe.config.

     <?xml version="1.0" encoding="utf-8" ?>

    <configuration>

      <runtime>

        <disableCachingBindingFailures enabled="1" />

      </runtime>

    </configuration>

Then move the file into %ProgramFIles%\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2\x64. Once the file is placed, you need to start the installation again. For more information, see this: http://support.microsoft.com/kb/2261507 .

SQL Server Analysis Services Installation – VertiPaq
Before starting the installation, make sure that a domain account is created for new Analysis Services instance for running the service. In my case, newly created account for this is PowerPivotAnalysisServices.

Now run the SQL Server 2008 R2 setup. It would be better to run the setup as Administrator (Right click on Setup.exe and select “Run as Administrator”). If you have configured more than one server for the farm as Application Servers, the installation should be done for all servers (Generally we set up two servers one for WFE and another for applications. This installation should be done only on Application Servers).

Once the installation is started, select New installation or add shared features as per the screen below.

Continue with screens until you get a screen which allows you to select SQL Server PowerPivot for SharePoint. Select Existing Farm for Add PowerPivot for SharePoint to (Note that assumption here is Farm is already configured).

Next screen is Feature Selection. It is a read-only screen, you will not be able to change anything on it. Continue with the wizard until you get Instance Configuration screen. You will notice that the instance has already been named ad POWERPIVOT. Do not change it, continue the wizard.

At the Server Configuration screen, set newly created domain account and its password for SQL Server Analysis Services. When you have Analysis Services Configuration screen, make sure that accounts that need to be administrators for Analysis Services are added.

Everything is set now, continue the wizard and complete the installation. Once it is done, go to SharePoint Central Administration –> System Settings –> Manage Servers in this Farm. It shows all the servers in the farm and services running. If you see SQL Server Analysis Services and SQL Server PowerPivot System Service, your installation is successful.

Deploying PowerPivot solution packages to Web Application
Next part is deploying PowerPivot solution packages to Web Applications (to WFE Servers). There are two solution packages. Installation we performed earlier deploys solution packages for Central Administration but not for our web application. Go to SharePoint Central Administration –> System Settings –> Farm Management –> Manage Farm Solutions. You should see two solutions;

powerpivotfarm.wsp is deployed globally, no need to deploy again.
powerpivotwebapp.wsp, you can see that it has been deployed to my default web application (http://w2008r2-sp:10000). This is the solution we need to deploy manually to Web Application which suppose to hold PowerPivot data.

Let’s deploy powerpivotwebapp.wsp to a web application. Click on powerpivotwebapp.wsp. It opens Solution Properties. Click on Deploy Solution. Select the web application from the drop-down you need to use under Deploy to? and click OK. Once the Solution Properties page is open, make sure that added web application is listed as below.

Other Services required
Next step is starting all other necessary services required by PowerPivot. Make sure following services are running;

Excel Calculation Service
Secure Store Service
Claims to Windows token Service
SQL Server Analysis Services
SQL Server PowerPivot System Service

These services are listed under SharePoint Central Administration –> System Settings –> Manage Services on Servers.

Creating PowerPivot Service Application
This helps to establish the communication between WFE and PowerPivot service via PowerPivot Service Application Proxy. Let’s create it. Go to SharePoint Central Administration –> Application Management –> Manage Service Applications. Click on New and select SQL Server PowerPivot Service Application.

With Create New PowerPivot Service Application window, set the name as PowerPivotServiceApplication. Do not use any existing pool, create a new one and assign a domain account for it. Set the database server and database name then. Finally select the checkbox Add the proxy for this PowerPivot service application to the default proxy group. Click OK to create it.

PowerPivot Feature Integration for Site Collections
This is appeared under Site Collection Features and must be activated for each of the site collections. Go to your site collection and click on Site Actions –> Site Settings –> Site Collection Administration –> Site Collection Features. When the page open, find PowerPivot Services feature integration for Site Collections and activate it.

Configuring PowerPivotUnAttendedAccount for Data Refresh
PowerPivot unattended account is used for refreshing data in the workbook if user credentials are not exist with the workbook. This account is stored in SharePoint Secure Store Service. In order to continue, you need to make sure that Secure Store Service is up and running, and master key is created. I have already configured Secure Store Service for PerformancePoint. If you have not, see this post for configuring it: http://dinesql.blogspot.com/2010/07/configuring-performancepoint-2010.html.

Let’s open the Secure Store Service Application. Go to SharePoint Central Administration –> Application Management –> Manage Service Applications. Click on Secure Store Service (or select it and click on Manage) link;

Secure Store Service uses Target Applications for holding accounts. In my case, I have an application set up for PerformancePoint and that is what has been listed. If you have note, nothing will be listed. Once a Target Application is created for PowerPivot, it has to be associated with PowerPivot Service Application. Let’s start creating the Target Application. Click on New for opening Create New Secure Store Target Application. Set ID and name for the application. Appropriate ID and name would be PowerPivotDataRefresh and Power Pivot Data Refresh. Enter a valid email for Contact Email and select Individual for Target Application Type.

Next screen for specifying fields (columns) for the application. Accept the default as we need only user name and password and continue.

Next screen for setting admins for the application. Make sure that you have added the account which is used for PowerPivot Service Application pool. In addition to that, any account need to be an admin can be added. Continue the wizard and complete. Finally you are back on Secure Store Service. You should see the newly created application listed now.

Next is setting credentials for PowerPivotDataRefresh application (or PowerPivot unattended account). Select the check box next to newly created application and click on Set Credentials button. It opens the Set Credentials for Secure Target Application (Individual). Settings for this screen will be;

Credential Owner: This is the owner of the credential. It will be used by PowerPivot unattended account but this is not the account used as the unattended account.
Windows user name and password: This is the actual account used as the unattended account.

Click OK. Now we need to associate the application with PowerPivot Service Application. Go to SharePoint Central Administration –> Application Management –> Manage Service Applications. Find PowerPivot Service Application and click the link (or select it and click on Manage button). This opens PowerPivot Management Dashboard. Click on Configure Service Application Settings in Actions frame. It opens PowerPivot Settings. Find PowerPivot Unattended Data Refresh Account input box under Data Refresh. Type the ID of the application created in Secure Store Service, in my case, it is PowerPivotDataRefresh.

Done. To complete the process, few setting have to be done in Excel Services.

Settings in Excel Services
Go to SharePoint Central Administration –> Application Management –> Manage Service Applications. Find Excel Services Application and open it. It opens Manage Excel Services Application. Click on Trusted File Locations.

You will see that SharePoint has been added as a Trusted Location (Address is http://). In order to access a workbook by both Excel and PowerPivot services, this is required.

Get the menu of http:// and select Edit. Change following items in Edit Trusted File Location.

Workbook Properties: Maximum Workbook Size. Set as you want. Note that maximum file size accepted by SharePoint for both Excel and PowerPivot is 2GB.
External Data: Select Trusted data connection libraries and embedded under Allow External Data.

Click OK to save settings. Now, You should be able to upload an Excel file that contains PowerPivot data into the web application and see.

Will discuss more on this in another post.

PPS Reports: Integrating Reporting Services Reports with PerformancePoint 2010 Dashboards

2011-01-21T21:25:00.001-08:00

The success of Business Intelligence solution can be measured with Visualization of data, hence Visualization in proper manner is important. It is handled through many ways, providing easier access for information to business users. One of the key components in Microsoft Business Intelligence suite is PerformancePoint 2010 which supports visualizing data in number of ways. PPS allows business user to interact with data, organize as he wants and change the content as he wants with its reporting features. Some of reporting facilities available with PPS are Reporting Services reports, Analytic charts, grids, and Decomposition trees. This post focuses on Reporting Services reports, basically, how a Reporting Services report can be added to PPS dashboard.

Microsoft Report Viewer 2008
In order to use Reporting Services report with PPS Dashboard Designer, you need to make sure that Microsoft Report Viewer is installed in your machine. If not, you may face errors like “Unable to connect with Report Server” or “Could not load file or assembly ‘Microsoft.ReportViewer.WinForms’…..”. It is available for 2005, 2008 and 2010 now. It looks like PPS Dashboard Designer works only with 2008 (I have 2008 SP1) but you can try 2010 too. Here are links for downloading it:
Microsoft Report Viewer Distributable 2008
Microsoft Report Viewer Distributable 2008 SP1
Microsoft Report Viewer Distributable 2010

Getting Started
As usual, you have to start with PPS Dashboard Designer. If you have configured Business Intelligence Web Application and site properly with SharePoint 2010, there will a link available for downloading this ClickOnce application (see Configuring PerformancePoint 2010 for more details). Click on Run Dashboard Designer for downloading the designer.

Once open, Save it with a meaningful name. Then select PerformancePoint Content folder in left-pane and go to Create tab in the ribbon. Click on Reporting Services.

Report Setting
You need to set few settings in order to have a Reporting Services report in your dashboard.

Server Mode: This allows you to set the mode of Reporting Server you are going to connect with. If it is hosted in SharePoint, select SharePoint Integrated, else select Report Centre. In my case, it is SharePoint Integrated mode.

Report Server URL: This is the URL of Report Server Web Service which you set of Reporting Services Configuration Manager.

Report URL: This is the exact URL of the report. If it is hosted in SharePoint, make sure that you have given the proper URL (including the extension). If you select Report Centre, you will be able to browse the report and set.

Show toolbar, Show Parameters, Show DocMap: You can set the visibility of these items. Note that Document map will not be available for all reports. It has to be created with either Report Builder or BIDS.

Zoom: Allows you to set the viewing size.

Format: Default is HTML 4.0. You can select other types such as Excel or PDF. Based on the type you select, report will be either shown in the web page or will be downloading to client machine.

Section: This allows you to set the page to be displayed. If you set 3 for this, 3rd page will be open as the default page.

DocMap ID: It is supposed to show the element set with this as the default page. I could not figure it out the way of setting it, for me, it did not work.

Report Parameters: This allows to set parameters default values.

Once settings are done, add a dashboard and place the report, and publish to SharePoint site. SharePoint site can be set with File Button –> Designer Options –> Server –> SharePoint URL. Make sure it is set before publishing.

Here is the published dashboard.

PerformancePoint 2010: Decomposition Tree is not available

2011-01-21T18:54:00.001-08:00

Are you unable to find the Decomposition Tree in the menu? Is it not visible?

Reason is simple, make sure SilverLight 3 (or 4) is installed. Once it is installed, it will be available for you. Thanks for Dinusha for reminding me on this.

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

2011-01-20T00:33:00.001-08:00

Configuring Reporting Services 2008 R2 in SharePoint 2010 Integrated mode is straight forward and simple. If you have read my post Configuring Reporting Services 2008 R2 in SharePoint 2010 Integrated Mode – Part I and Configuring Reporting Services 2008 R2 in SharePoint 2010 Integrated Mode – Part , then you know the way of configuring it. You will not face much issues after configuring if it is done with Standalone SharePoint 2010 architecture. If it is a SharePoint farm which components are distributed, few things should be considered. Here are some of points you need to consider when Reporting Services is configured in SharePoint 2010 Farm. Note that this was tested with a Web Application which Authentication is set as Classic and NTLM is set for Authentication Provider.

Domain accounts created
Reporting Services Account: Domain account, no admin privileges. Usually name it as a Domain\ReportingServices. Set this with Reporting Service Configuration Manager.

Application Pool Account: Domain account, no admin privileges. Usually name as Domain\SP_SSRSApp. Use this when the Web Application is created with SharePoint.

Site Collection (or Site) account: Domain account, need local admin rights. Usually name as Domain\SP_Admin.

** All accounts are maintained as Managed Accounts in SharePoint.

Reporting Services Windows Service Account
This should be a domain account. Once it is set with Reporting Services Configuration Manager, you need add the same account to SharePoint too. First, have it as a Managed Account (Central Administration –> Security –> General Security –> Configure Managed Accounts). Then add the account to the Site where you have configured Reporting Services Libraries. You can add this account to Home Members group. Errors you may get if it is not added are “The permissions granted to user ‘Domain\User’ (what ever account you used for logged in) are insufficient for performing this operation.” and “rsAccessDenied”. Note that error has no link with the service account but it is related to it. You may get this error with Report Builder or BIDS when deploying reports, or viewing reports.

In addition to that, you need to make sure that the account is added to Web Application content database which is created for Web Application. If not, you may get and error “Report Server has encountered a SharePoint error”.

Other than that, this account is in ReportServer database too. It is automatically added, but make sure that it is there.

Allow Anonymous Setting
Make sure that Allow Anonymous is set as false when the Web Application is created. I faced an issue related deployment from BIDS if this is not set to false (read my post on it). Once the application is created, double-check with IIS (Site –> Authentication) and see whether it is disabled. As per the Authentication and Authentication Provider we set, Only ASP.NET Authentication and Windows Authentication should be enabled.

SharePoint Integrated Reporting Services: BIDS pops up Login Dialog box indefinitely

2011-01-19T08:31:00.001-08:00

Annoying…. This is all about SharePoint integrated Reporting Services deployment via BIDS. Web application for Reporting Services has been created with SharePoint 2010 and all necessary libraries are added (if you need to see the way of configuring SSRS 2008 R2 in SharePoint 2010, please see my posts Configuring Reporting Services 2008 R2 in SharePoint 2010 Integrated Mode – Part I and Configuring Reporting Services 2008 R2 in SharePoint 2010 Integrated Mode – Part II).

Now the problem is, popping up login dialog box indefinitely in BIDS when try to deploy reports, regardless of what account is supplied. As usual, went through the internet, found many solution but none of them worked for me. One of picked solutions by me is, disabling anonymous user. But it did not work too.

Solution
After all unsuccessful attempts, I created a new web application and set the Anonymous Access to No when creating the Web Application. Then I pointed the project’s target URL to newly created site, Wow, no login dialog box. I tried the same with old site too (by disabling Anonymous Authentication in IIS) but did not work. So, not sure about this solution but it worked with the new site. If you face the same, try a new site and see.

PerformancePoint 2010 Error: The URL is not available, does not reference a SharePoint site, or you do not have permission to connect.

2011-01-16T07:50:00.001-08:00

If you face for this error when connecting to SharePoint site through PerformancePoint designer;

… then check the domain account which has been used for PerformancePoint service application has been added to site content database as an owner. Once added, you are not going to see this error .

PerformancePoint 2010 Error: The URL must be in one of the following zones: Local, Intranet, or Trusted

2011-01-16T07:42:00.001-08:00

If you face for this error when connecting to SharePoint server from Dashboard Designer;

… simply add the site for Trusted Sites in your Internet Explorer.

MCTS: SQL Server Database Administrator – Maintaining a MS SQL Server Database 2008 – 6231A Course

2011-01-14T18:41:00.001-08:00

Course 6231A: Maintaining a SQL Server 2008 Database – This is my first class for 2011. Class will be started on Sunday, 23rd January 2011. If you are a DBA or interest on SQL Server Administration, join with us. Some topics that are covered with this course are Installing and Configuring, Security, Back up strategies, Disaster Recovery, High Availability, Replication and data transferring (see more on http://www.microsoft.com/learning/en/us/course.aspx?id=6231A).

Good news! Microsoft gives 40% discount for students for Microsoft exams. All you have to do is, go to an authorized Centre and prove that you are a student of any university (both private and government), then you get 40% discount for any exam. Good Luck!

Cannot login to SharePoint 2010 site when the Host Header is set

2011-01-10T03:01:00.001-08:00

Everybody prefers a good URL for sites rather than server name with ports. Same for SharePoint Web Applications (SharePoint sites). When a Web Application is created, it allows us to set the URL for the site as we want through Host Header as well as the port. For example, when a Web Application is created as http://SPServerName:3000 , URL can be set as http://MyTestWeb.com with Host Header input box. Once created you need to make sure that a DNS record is created and pointed to SharePoint server.

My environment is set up with two Windows 2008 R2 Enterprise Editions. Once server is configured as the Domain Controller and other has SharePoint 2010 and SQL Server 2008 R2.

Here is the way I set;

Add URL I need for the Web Application:

Set DNS record in DNS Server, in my case it is DC:

Done. I should be able to navigate my site (after creating a Site Collection for the Web Application) using the URL set with Host Header (in my case, it is http://bi.andromeda.com) without using http://W2008R2-SP:30000 . But I am unable….

My Problem
When I open the browser for http://bi.andromeda.com , browser prompts me the authentication dialog box and asks for an user id and a password. It prompts three times even with correct user id and password and then opens a blank page. Could not figure out the reason for a while, finally Prabath sent me an URL that contains a solution for this. The solution is given by Tech Battle Field and post is http://spiderwool.blogspot.com/2010/09/access-denied-host-header-sharepoint.html.

In summary, solution is adding a new Multi-String registry key named BackConnectionHostNames in SharePoint server at HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\MSV1_0 and set the host names with values (Read the post for more details). This is what I did:

Finally it worked. Thanks for Tech Battle Field for sharing this and thanks for Prabath for guiding me onto it.

Awarded Microsoft MVP again for 6th time

2011-01-04T03:30:00.001-08:00

MVP again!

Microsoft has awarded the title again for the 6th time, thanks for Microsoft, Microsoft Sri Lanka, Wellignton and Lilian for recommending me.

Two new MVPs are introduced; Prabath Fonseka (SharePoint) and Preethiviraj (SQL), Congratulation guys!

Analysis Services Security: Dimension Data – Select all members or Deselect all members?

2010-11-24T18:08:00.001-08:00

While configuring Analysis Services security on Dimension Data, I did not pay much attention on two options available with the screen; Select all members and Deselect all members. However the importance of them was raised up when my colleague Asanka Padmakumara showed a point on them. Which one should we select? See the screen below;

I have selected the first option which is Select all members and granted permission on Accessories and Bikes. I can do the same, select the other option too. Let’s move into Advanced section and see how it has been set;

Note that other two categories (Clothing and Components) have been added under Denied member set. What we have set? Yes, we granted permission on Accessories and Bikes but we have done indirectly is, denying permission on Clothing and Components. Main thing we need to consider is, permission on future categories. Since the items we did not select have gone to Denied member list, all future items will be available for this role. If you need to let this role to access future categories without explicitly setting them, use Select all members for setting permission.

Now let’s use the other option; Deselect all members;

I have given same permission set but the option selected is Deselect all members. Have a look on Advanced section now;

Now you can see the items we selected have been added to Allowed member set. This means all other items we did not select are denied to this role. What will happen to future categories? This is the difference. Since we have explicitly added Accessories and Bikes to Allowed member set, future items will NOT be available under Allowed member set. If you need to disallow this role to access future categories without explicitly setting them, use Deselect all members for setting permission.

Analysis Services Security: DENY does not override GRANT

2010-11-22T07:06:00.001-08:00

Is the famous rule of thumb broken? I am sure that you all are aware the rule and you know that DENY always wins over GRANT. If you want to see an exception (or a situation) read one of my previous posts “Column GRANT overrides DENY TABLE, Avoiding with Common Criteria Compliance”.

This post discusses another situation, it is not with SQL Server Relational Engine, it is will SQL Server Analysis Services.

Analysis Services behaves bit differently. As you know, if an object of SQL Server database is denied explicitly, it is always denied even with an explicit grant. For example, if you are in a Role that grants SELECT on Table1 and in another role that denies SELECT on Table1, you will not be able to execute SELECT on Table1. Analysis Services authorizes on items differently. It unions all grants and authorizes. If you are in a Role that grants on Item1 and Item2 (Note that in Analysis Services, once items are granted, all other items are denied, no need to explicitly deny them as SQL Server database engine objects) and in another Role that grants Item2 and Item3, you are granted for Item1, Item2, and Item3.

Let me show you a sample on this. The below image shows a Role in Analysis Services Adventure Works DW 2008R2 database. This is how it is created;

Name: Business Users
Membership: Dinesh
Cubes: Adventure Works – Read
Dimension Data:
- Product Dimension, Category – granted for Accessories and Bikes
- Other Dimensions – granted for all

Second image shows another role in same database. Its properties are as follows;

Name: Coordinators
Membership: Dinesh
Cubes: Adventure Works – Read
Dimension Data:

Product Dimension, Category – granted for Bikes and Clothing
Other Dimensions – granted for all

Business Users role denies for Clothing and Components. Coordinators role denies for Accessories and Components. When Dinesh sees data, he sees union of both; Accessories, Clothing, and Bikes. The reason for this is, Analysis Services uses all GRANTs from all Roles for connected user and then authorizes. That is why he sees Accessories and Clothing even though they are denied from each role. This is how he sees when Categories are loaded with Excel.

Note: This type of security cab be tested with Management Studio too. Start Cube Browser and click on Change User icon. The opened window allows you to set the context with Current User, Other User, or Roles.

Way to Visualize: Project Crescent; What is it?

2010-11-16T18:02:00.001-08:00

The Era that Business User is used to carry an armload of tools for analyzing and visualizing data, has come to an end!!!!! Yes, Microsoft never stops making business user’s life easier. Microsoft never stop supporting business user to manage and visualize data in a rich way. Microsoft has come out with an exciting facility; The Project Crescent.

What is Project Crescent?

Project Crescent is the recent Microsoft Business Intelligence team innovation that helps to manage and visualize large amount of data quickly in very rich format. It has been built entirely on Silverlight and will be available as a BI client. It allows to show the insights of the business with reports, with highly interactive visualization and animations.

The releasing date of this is unknown. Most probably it will be released with Denali, at the end of 2011 or beginning of 2012. Long time to wait…..

Here is a sneak peak of project Crescent, in Amir Netz’ demo at PASS Summit 2010:

Here is a post from SQL Server Reporting Services team blog: http://www.silverlightshow.net/news/Project-Crescent-built-entirely-on-Silverlight.aspx

Reporting Services: Difference between RDL and RDLC files

2010-11-12T01:31:00.001-08:00

Microsoft Reporting Services is an enterprise-capable reporting solution that can be used to create all types of reports such as Production, Analytical, and Dashboard. Building reports is done through Business Intelligence Development Studio which is an extension of Visual Studio. Reports are created as RDL (Report Definition Language) files. Once the reports are created with BIDS, they (RDLs) are hosted in Reporting Services and users can be accessed them in many ways.

What are RDLC files?
RDLC: Report Definition Language, Client Side files are files that are generated with either ReportViewer Control that comes with Visual Studio 2008 Windows or ASP.NET project templates, or Reporing –> Report Application project template. It is a XML file, just like the RDL. It uses the same XML schema that is used by RDL files but RDLC does not require to have values for all elements such as query text. If RDLC file is needed to be hosted in Reporting Services, it needs to be renamed as RDL and all empty elements must be filled manually.

Reporting with ReportViewer Control
The ReportViewer Control allows us to embed a report to ASP.NET web page or Windows form. It supports two modes: Remote Processing mode and Local Processing mode. The Remote Processing mode allows to connect with reports that have been already deployed to SSRS instance. In this case, all the processing take place at the SSRS server. The Local Processing mode allows to create reports within Visual Studio itself. The extension of them are RDLC. This does not require an instance of Reporting Services, hence processing is handled by the client application.

Here are some of important points to remember about these two modes:

Local Processing mode does not require an instance of Reporting Services, hence a license for Reporting Services is not required.
Local Processing mode is not recommended for large reports. It is used for small, OLTP types of reports. They may run infrequently.
Remote Processing can be scaled out but Local Processing.
Local Processing mode supports following extensions only:

Visual Studio 2008: PDF and Excel
Visual Studio 2010: PDF, Excel and Word

Local Processing mode with Visual Studio 2008 does not support Tablix data region. Visual Studio 2010 supports.
Mapping parameters with query parameters has to be done manually with Local Processing mode.

Future version of SQL Server: “Denali”, CTP available for downloading

2010-11-11T20:37:00.001-08:00

Microsoft announced that Community Technology Preview of SQL Server code-named “Denali” available for downloading. This CTP contains enhancements mainly on SQL Server high availability and engine not with BI components such as Analysis Services and Reporting Services.

To see details: http://www.microsoft.com/sqlserver/en/us/product-info/future-editions.aspx

To download: http://www.microsoft.com/downloads/en/details.aspx?FamilyID=6a04f16f-f6be-4f92-9c92-f7e5677d91f9&displaylang=en

To see the enhancements: http://msdn.microsoft.com/en-us/library/bb500435(SQL.110).aspx

Ready-made Virtual Machine for SharePoint 2010

2010-11-04T04:34:00.001-07:00

Are you looking for pre-built virtual machine that includes SharePoint 2010? If yes, here it is… it is available for downloading.

Not only SharePoint 2010, it contains:

Microsoft Office Communication Server 2007 R2
Microsoft Visual Studio 2010
Microsoft SharePoint Server 2010 Enterprise Edition
Microsoft Office Web Applications
Microsoft FAST Search for SharePoint 2010
Microsoft Project Server 2010
Microsoft Office Professional Plus 2010
Microsoft Visio 2010
Microsoft Project 2010
Microsoft Office Communicator 2007 R2

For more details and downloading: 2010 Information Worker Demonstration and Evaluation Virtual Machine.

SharePoint Videos: SharePoint 2010 Developer Training Course

2010-11-02T18:02:00.001-07:00

Looking for resources to learn SharePoint 2010 development? So many books, articles, and blog posts? Here is an easy way to learn it;

Site: SharePoint 2010 Developer Training Course
Download link: SharePoint 2010 Developer Training Kit

This training kit provides videos and hands on labs which are very useful to understand how SharePoint 2010 works and how it can be used.

We must thank Chris Mayo, Donovan Follette, Girish Raja, Paul Stubbs, and Steve Fox for preparing something like this for developers.

SSIS: Replacing Slowly Changing Dimension (SCD) wizard with the MERGE statement

2010-11-01T03:25:00.001-07:00

Slowly Changing Dimensions are business entities in DW/BI systems that have attributes that do not change very often. In order to handle loading of SCDs, we use SQL Server Integration Services Data Flow component: Slowly Changing Dimension Component. It is a wizard that helps us to handle changing attributes.

If you are a SSIS developer and have used SCD wizard, you have already seen the bad performance on it. It works fine with a dataset that contains records less than something around 5000 (this is what I have seen, please do not consider it is as a benchmark) but gives very slow performance on beyond. Recently, I noticed that one of the projects done had used SCD wizard for almost all data loading and the time it takes for completing the load has gone from 5 minutes for 5-6 hours. It significantly increases the time when the data load is getting higher and higher.

How to improve the performance of loading of SCDs?
I had to give a solution for this…. two things came into my mind, one is Kimball Method SSIS Slowly Changing Dimension Component (will make a separate post on this) and the other is MERGE T-SQL statement. Although the MERGE implementation is bit complex, I thought to recommend the MERGE because many have accepted that the performance it gives is superb. The below table shows a comparison made on three different approaches on SCDs loading with some criteria (which is quoted from Matt Masson’s presentation named Performance Design Pattern).

You can easily notice that MERGE gives the best performance though there are drawbacks.

Type of Slowly Changing Dimensions
Slowly Changing Dimensions are categorized into three types named: Type 1, Type 2, and Type3. The Type 1 SCD does not maintain the history of changing attributes, it overwrites values of the attributes. Type 2 maintains historical values for changing attributes. Type 3 that we do not use much (I have not used it at all) maintains separate columns for changed attributes. SSIS SCD wizard supports both Type 1 and Type 2.

Using MERGE instead of SCD wizard
Replacement of SCD wizard with MERGE is not a straightforward technique. If the SCD has both Type 1 and Type 2 types attributes, they need to be handled separately. Let’s create a simple table with following set of data, in order to see the way of handling them with MERGE.
Following attributes in this table are Type 1:

FirstName
LastName

Following attributes are Type 2:

MaritalStatus
Country

Following script creates the Dimension table and inserts data;

     CREATE TABLE dbo.DimCustomer

    (

        CustomerKey int IDENTITY(1,1) PRIMARY KEY

        , SourceCode char(5) NOT NULL

        , FirstName varchar(50) NOT NULL -- Type 1

        , LastName varchar(50) NOT NULL -- Type 1

        , MaritalStatus char(1) NOT NULL -- Type 2

        , Country  varchar(50) NOT NULL -- Type 2

        , StartDate date NOT NULL

        , EndDate date NOT NULL

        , CONSTRAINT IX_DimCustomer_SourceCode UNIQUE (SourceCode)

    )

    GO

    INSERT INTO dbo.DimCustomer

        (SourceCode, FirstName, LastName, MaritalStatus, Country

        , StartDate, EndDate)

    VALUES

        ('AAAA1', 'Dinesh', 'Priyankara', 'M', 'Sri Lanka'

            , '01/01/2009', '12/31/9999')

        , ('AAAA2', 'Yeshan', 'Senthush', 'S', 'Sri Lanka'

            , '01/01/2009', '12/31/9999')

        , ('AAAA3', 'Jane', 'Alvarez', 'S', 'France'

            , '01/01/2009', '12/31/9999')

        , ('AAAA4', 'Shannon', 'Carlson', 'M', 'India'

            , '01/01/2009', '12/31/9999')

    GO

When data loading of SCDs are handled with SSIS,what we usually do is, once the data set is captured, we send it through SCD component. The SCD component inserts new data, updates Type 1 data, and finally inserts Type 2 data. If we are to use MERGE, we need to send the captured data to a temporary table and then use Execute SQL Task for executing the MERGE statement. Assume that following table is the temporary table and it is loaded with captured data.

This code creates the table and populates data;

    CREATE TABLE dbo.TempDimCustomer

    (

        SourceCode char(5) NOT NULL

        , FirstName varchar(50) NOT NULL

        , LastName varchar(50) NOT NULL

        , MaritalStatus char(1) NOT NULL

        , Country  varchar(50) NOT NULL

    )    

    GO

    INSERT INTO dbo.TempDimCustomer

        (SourceCode, FirstName, LastName, MaritalStatus, Country)

    VALUES

        ('AAAA1', 'Dinesh', 'Priyankara', 'M', 'Sri Lanka')

        , ('AAAA2', 'Yeshan', 'Santhush', 'S', 'Sri Lanka')

        , ('AAAA3', 'Jane', 'Alvarez', 'M', 'France')

        , ('AAAA4', 'Shannon', 'Carlson', 'M', 'German')

        , ('AAAA5', 'Jon', 'Yang', 'M', 'Japan')

    GO

Handling Type 1 attributes
The below codes shows the MERGE statement that updates Type 1 attributes. Note that it goes through all records and updates.

    MERGE INTO dbo.DimCustomer c

    USING dbo.TempDimCustomer tc

    ON (c.SourceCode = tc.SourceCode)

    WHEN MATCHED 

        AND 

            (c.FirstName != tc.FirstName

            OR c.LastName != tc.LastName)

        THEN UPDATE

            SET c.FirstName = tc.FirstName

                , c.LastName = tc.LastName;

Handling Type 2 attributes
Handling Type 2 is tricky. If a record is changed, we need to update the old record, setting the EndDate as current date, and then insert a new record for it, with StartDate as current date. See the code below;

    INSERT INTO DimCustomer

        (SourceCode, FirstName, LastName, MaritalStatus, Country

        , StartDate, EndDate)

    SELECT Modified.SourceCode, Modified.FirstName

        , Modified.LastName, Modified.MaritalStatus

        , Modified.Country, Modified.StartDate

        , Modified.EndDate

    FROM

        (MERGE INTO dbo.DimCustomer c

        USING dbo.TempDImCustomer tc

        ON (c.SourceCode = tc.SourceCode)

        WHEN NOT MATCHED 

            THEN INSERT

                (SourceCode, FirstName, LastName, MaritalStatus, Country

                , StartDate, EndDate)

                VALUES

                (tc.SourceCode, tc.FirstName, tc.LastName, tc.MaritalStatus, tc.Country

                , GetDate(), '12/31/9999')

        WHEN MATCHED 

            AND c.EndDate = '12/31/9999'

            AND (c.MaritalStatus != tc.MaritalStatus

                OR c.Country != tc.Country)

            THEN UPDATE

                SET c.EndDate = getDate()

            OUTPUT 

                $Action Action, tc.SourceCode, tc.FirstName, tc.LastName

                , tc.MaritalStatus, tc.Country

                , '10/28/2010' StartDate, '12/31/9999' EndDate) AS Modified

    WHERE Modified.Action = 'UPDATE';

See the inner MERGE first. With first NOT MATCHED section, it inserts new records. Next is for finding MACTHED and Type 2 changes. Note that the check is done only on latest records (EndDate = ‘12/31/9999’). If found, EndDate is updated and records are returned as a OUTPUT of the MERGE. This output is captured by outer INSERT statement and inserts them as new records. Done!

Please do test both approaches before deciding the technique. If the data set is large, it would be better to use MERGE instead of SSIS SCD component.

Dimension has no attributes…Can I make it as Degenerated Dimension?

2010-10-28T22:33:00.001-07:00

One of my friends recently came up with an interesting topic where he has faced an issue with his Data Warehouse design related to Insurance industry. He has come across a situation where he needs to build a dimension for Insurance Claim numbers that holds ONLY claim numbers. He has already build other related dimensions such as Customer and Product, and has built the Fact table too.

This particular Insurance Claim number is one of the attributes set at grain level of the fact. In addition to that, no common claim numbers, all are unique, and number of claim numbers are grown at the same rate that fact table grows. He wanted my opinion on this, thought degenerate the claim number without maintaining a separate dimension. The below image explains what I suggested;

As you see, first diagram has a dimension for maintaining claim numbers. The second has no dimension for claim numbers and claim number has been added to the Fact table. The best option I see is, making it as a degenerated dimension because;

Number of records in the ClaimNumber dimension grows at the same rate that Fact table grows.
Claim number has no other descriptive attributes.
Claim number does not exist outside the transaction (the claim).

I am facing a similar issue on Banking related DW, the Fact focuses on Deposits and the transaction related deposits contain Comments. Comments are not available for all transactions and they are not common among transactions too. I do not like to see NULLs in my Fact table but thought to degenerate into Fact table. I wish I could argue on this with someone……… :)

If you need to read more on Degenerated Dimension, read Kimballl Design Tip #46 at http://www.rkimball.com/html/designtipsPDF/DesignTips2003/KimballDT46AnotherLook.pdf.

Is Microsoft's SQL Server really cheaper than Oracle?

2010-10-26T04:16:00.001-07:00

Sean McCown has written an article on “http://www.networkworld.com/community/node/63390”. Good article, worth to read.

Do not forget to read comments too.

SSIS Error: Named Pipes Provider: The specified network name is no longer available.

2010-10-25T23:06:00.001-07:00

A perfectly working package suddenly throws an error;

This package is a kind of complex one, it contains three Data Flow tasks and couple of other tasks. It basically loads a Fact Table in one of Data Warehouses, goes through set of LookUps which have set for Dimension tables.

The error raises with one of the LookUps. The lookUp is supposed to go through around 40,000 records but stops at around 6,000, throwing the error. Once this started giving the issue, the only difference we can thought about current execution and early executions is, the amount of data, it has been increased.

Solving the issue by changing the protocol TCPIP
After searching for solutions, going through many posts, based on some recommendations, we decided to force SSIS to use TCPIP instead of Named Pipes. We disabled the Named Pipe protocol in SQL Server through SQL Server Configuration Manager. We did the same with where we run the package. Finally we ran the package and ended up with same point;

“[Lookup for - Pending Duration [7825]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Login timeout expired".
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "TCP Provider: Only one usage of each socket address (protocol/network address/port) is normally permitted.
".”

We realized that the issue is not exactly related to protocols used by the package hence, went through some of the implementations we have done where we have slightly derailed the track of standardization. One implementation was related to transactions, where we had used explicit BEGIN TRAN and COMMIT TRAN to control the transactions without using the built-in facility. This type of implementation has been explained here if you need to know how to do it. The reasons for using this is arguable but some of them were, MS DTC issues, Firewalls, and company policies.

In order to set the transaction like above, we need to set the RetailSameConnection as True in OLE DB connection. That was the culprit. Since this forces SSIS to maintain one connection through out the package execution, it seems that using one connection for all lookups is not efficient.

Solving the issue
One easiest way to solve the issue is, adding Timeout=0 to the connection string. But this slows down the package execution vastly, though it succeeds. The next way is, handling the transactions in different way. We thought to use the second way because the time takes for the first one is not acceptable.

The above mentioned errors may occur for some other reasons too but this could be the one if you have already face :).

Plan Caching in SQL Server 2008

2010-10-25T01:22:00.001-07:00

I got many questions on the post I published “Do we need to recompile Stored Procedures when an index is added or parameter value is vary greatly?”, seems many are interesting on it. While looking for some info related to it, I found an interesting technical article that speaks about Plan Caching written by Greg Low. Read it, it explains everything well;

http://msdn.microsoft.com/en-us/library/ee343986.aspx

Do we need to recompile Stored Procedures when an index is added or parameter value is vary greatly?

2010-10-21T23:42:00.001-07:00

While Preethi was presenting his presentation on Query Plans at the SQL Server Sri Lanka User Group meeting, he showed a situation where query plan gets recompiled when a value set for WHERE clause is vary. Although it recompiles, I doubt that it will not be the same if the query is a parameterized query with a stored procedure. There were couple of arguments on this favoring both sides; thought to test it and see.

Let me take simple two queries. The queries below are same except the value passed for the WHERE clause.

     SELECT * FROM Sales.SalesOrderDetail 

        WHERE SalesOrderDetailID <= 1

    SELECT * FROM Sales.SalesOrderDetail 

        WHERE SalesOrderDetailID <= 1000

SQL Server selects one plan for the first one but it does not use the same for the next though it is same.

This shows clearly that the statement has been recompiled since the value was different. But this is not going to happen if the logic is implemented with a stored procedure;

    CREATE PROC GetOrderDetails @Number int

    AS

        SELECT * FROM Sales.SalesOrderDetail 

        WHERE SalesOrderDetailID <= @Number

    GO

The plan for the procedure will be generated at the first execution. Once generated, the generated plan will be used for next executions even though the value of parameters are vary greatly. See the code below;

    EXEC GetOrderDetails 1

    EXEC GetOrderDetails 1000

You can see that the same plan which was generated for the first execution has been used for second execution even though it is not the best plan for the value 1000. All we have to remember is, stored procedures are not getting re-compiled when values of parameters are vary. If you expect a situation like this, use WITH RECOMPILE with the stored procedure. This instructs SQL Server to not to stored the plan generated, hence it creates a plan at every execution. If it is rare, use WITH RECOMPILE when you call the stored procedure. This creates the plan again when it is executed.

    ALTER PROC GetOrderDetails @Number int

    WITH RECOMPILE

    AS

    ...

    -- OR

    EXEC GetOrderDetails 1000

    WITH RECOMPILE

The next is whether the stored procedure gets recompiled when an index is added to a table used in it. See what BOL says;

It clearly says that the stored procedure will not get recompiled when an index is added to a table used with it. But what I found is the opposite of it. Sometime one of the statements gets compiled, sometime the entire stored procedure gets compiled. This is how I tested;

This code creates a new table and then creates a stored procedure. The execution of the stored procedure creates a plan and it is used for subsequent execution.

    SELECT *

    INTO TempSalesOrderDetail

    FROM Sales.SalesOrderDetail

    CREATE PROC GetTempOrderDetails

    AS

        SELECT SalesOrderDetailID FROM TempSalesOrderDetail 

        ORDER BY SalesOrderDetailID

    GO

    EXEC GetTempOrderDetails

Here is the generated plan for it.

As per the BOL, stored procedure does not get recompiled when an index is added…

    CREATE INDEX IX_1 ON TempSalesOrderDetail (SalesOrderDetailID)

    GO

    EXEC GetTempOrderDetails

Here is the plan now.

Now the plan is different, means the stored procedure has been recompiled. There might be a situation where the behavior as same as BOL says, will see whether we can find a such situation.

TechInsights – Malaysia – Session Ratings

2010-10-21T09:40:00.001-07:00

I just received ratings for sessions I delivered in Malaysia, glad to see that the sessions have been useful and valuable for participants. Ratings received are as follows;

Session: Strengthening DW/BI system loading
Overall Rating: 7.0

Session: Simplifying Master Data Management with SQL Server 2008 R2
Overall Rating: 6.8

Thanks for Ervin and Lilian for inviting me for TechInsights, thanks for the support given by all event organizer, and thanks for all feedbacks from participants.

Understanding Ownership Chaining and Impersonation

2010-10-20T20:46:00.001-07:00

Being a DBA or DBE, understanding how Ownership Chains work and how impersonate another principal for changing the execution context are really important. This topic was discussed while I was conducting the course 6232A on last Saturday, thought to blog on it as it would be useful to you all.

What is Ownership Chain?
When we perform an action with set of statements, objects used are accessed sequentially and this sequence is called as a Chain. Once the chain is established, permissions on objects in the chain are evaluated differently, not the way it checks permissions when they are separately accessed. If the objects accessed in the chain are owned by a same owner, Ownership Chain is established. Within the ownership chain, SQL Server checks the permission for the first object and skips checking on others. This is the advantage of Ownership Chain because it offers slight performance benefit skipping permission checks on other objects. If SQL Server finds a broken ownership chain, it starts evaluating permission on them.

What is Impersonation?
SQL Server Impersonation means, allowing to change the execution context with another principal. Simply, perform some actions (execute set of queries) using someone else privileges. This can be done explicitly by using the stand-alone EXECUTE AS statement or implicitly by using the EXECUTE AS clause on modules.

Let’s first check with Ownership Chaining and then go for Impersonation. I did this in a TestDatabase, you can do the same. The first code snippet creates two logins and two users. Note that SalesPerson is added to db_owner role.

     -- Create two logins

    CREATE LOGIN SalesPerson WITH PASSWORD = '123'

    , CHECK_POLICY = OFF

    GO

    CREATE LOGIN MarketingPerson WITH PASSWORD = '123'

    , CHECK_POLICY = OFF

    GO

    -- Create users for logins.

    USE TestDatabase

    GO

    CREATE USER SalesPerson FROM LOGIN SalesPerson

    GO

    sp_addrolemember 'db_owner', 'SalesPerson'

    GO

    CREATE USER MarketingPerson FROM LOGIN MarketingPerson

    GO

With next code you can see that Impersonation has been done explicitly using EXECUTE AS. What I do here is, impersonate SalesPerson and then create a schema and a table. Note that SalesPerson becomes the owner of these objects.

    EXECUTE AS USER = 'SalesPerson'

    GO

    CREATE SCHEMA Sales

    GO

    CREATE TABLE Sales.Sales

    (

        Date date

        , Amount money

    )

    GO

    REVERT

    GO

Next code impersonates MarketingPerson and sees whether he can access the table Sales.Sales. Since he has no permission on this table, error is thrown for SELECT query.

“The SELECT permission was denied on the object 'Sales', database 'TestDatabase', schema 'Sales'.”

    EXECUTE AS USER = 'MarketingPerson'

    GO

    SELECT * FROM Sales.Sales

    REVERT

    GO

Let’s check Ownership Chaining. With next code, SalesPerson creates a stored procedure that returns data from Sales.Sales, and he grants EXECUTE permission to MarketingPerson.

    EXECUTE AS USER = 'SalesPerson'

    GO

    CREATE PROC Sales.GetSales

    AS

        SELECT * FROM Sales.Sales

    GO

    GRANT EXECUTE ON Sales.GetSales TO MarketingPerson

    REVERT

    GO

We know that MarketingPerson has no read permission on Sales.Sales table but with next code, you can see that he can read the table through Sales.GetSales procedure.

    EXECUTE AS USER = 'MarketingPerson'

    GO

    -- MarketingPerson can access the table

    -- through the SP

    EXEC Sales.GetSales

    -- This throws the same error

    SELECT * FROM Sales.Sales

    REVERT

    GO

The reason for this is, the Ownership Chain. As MarketingPerson has EXECUTE permission, he can execute the SP. Although he has no permission on Sales.Sales, SQL Server does not evaluate permission on it for MarketingPerson because it is owned by the same owner who owns Sales.GetSales. Note that we have not explicitly set owners for both Sales.GetSales and Sales.Sales, hence owners of them are considered as Sales schema’s owner; SalesPerson. Below code shows it;

    SELECT USER_NAME( OBJECTPROPERTY(OBJECT_ID('Sales.Sales'), 'OwnerId'))

If the SP contains an object owned by someone else, it breaks Ownership Chain. Let’s test this too. Below code adds a new table, note that no impersonation is established, so, it is created by dbo. Since it is under Sales schema too, the owner of the schema (SalesPerson) becomes the owner of the new table.

    CREATE TABLE Sales.SalesCommisions

    (

        SaleNumber int

        , Amount money

    )

    GO

Next, the code of the SP is altered by SalesPerson. Now it contains both tables but still MarketingPerson can execute the SP without any issues even though he has no permission on new table.

    EXECUTE AS USER = 'SalesPerson'

    GO

    ALTER PROC Sales.GetSales    

    AS

    BEGIN

        SELECT * FROM Sales.Sales

        SELECT * FROM Sales.SalesCommisions

    END

    GO

    REVERT

    GO

    EXECUTE AS USER = 'MarketingPerson'

    GO

    -- Can see both tables

    EXEC Sales.GetSales

    -- Both statement throw errors

    SELECT * FROM Sales.Sales

    SELECT * FROM Sales.SalesCommisions

    REVERT

    GO

Still the Ownership Chain works. Let’s break it. Let’s change the owner of the new table Sales.SalesCommisions as dbo.

    ALTER AUTHORIZATION ON OBJECT::Sales.SalesCommisions TO dbo

Now if MarketingPerson executes the SP, he gets an error for Sales.SalesCommisions table:
”The SELECT permission was denied on the object 'SalesCommisions', database 'TestDatabase', schema 'Sales'.”

The reason is, the second object, Sales.SaleCommisions is not owned by the same person who owns the SP, hence SQL Server checks and sees whether MarketingPerson has permission on Sales.SaleCommissions.

How can we let MarketingPerson to execute the SP without giving explicit permission on Sales.SaleCommissions? Impersonation is the answer. We can use EXECUTE AS explicitly with the stored procedure, instructing SQL Server to execute the stored procedure with someone else privileges without using CALLER’s privileges. See the code below;

    EXECUTE AS USER = 'SalesPerson'

    GO

    ALTER PROC Sales.GetSales    

    WITH EXECUTE AS 'SalesPerson'

    AS

    BEGIN

        SELECT * FROM Sales.Sales

        SELECT * FROM Sales.SalesCommisions

    END

    GO

    REVERT

    GO

Now if MarketingPerson executes the SP, he will see the result without seeing an error because the execution context for the SP’s execution is set with SalesPerson not with MarketingPerson.

HP Business Intelligence Sizer for Microsoft SQL Server

2010-10-04T18:30:00.001-07:00

One of the challenges in Business Intelligence solutions is sizing the infrastructure. Many go with various calculations and assumptions and decide the infrastructure but later find that the capacity selected is not sufficient. One reason could be, most try to define the required hardware spec at the planning or proposal stage, without considering the workloads and processing them. If you plan for HP box for your BI solution, you can use the HP Business Intelligence Sizer for determining the spec. It interviews you for gathering factors related to your solution and provides you a recommendation;

I feel that this is not for small type of BI solution and suitable for large-scale solutions but you can try and see.

Here is the link for downloading the tool: http://h71019.www7.hp.com/activeanswers/us/en/sizers/microsoft-sql-bi.html

Looking for a BI Product suite? Have a look on “The BI Survey 9”

2010-10-03T18:28:00.001-07:00

Business Application Research Center – BARC publishes various types of survey reports; has published “The BI Verdict”, the latest BI survey report that contains comprehensive collection of in-depth BI product evaluations and market analysis. Here is a quote from the site:

“The BI Survey 9 uncovers the truth about why real-world organizations select BI products, what they do with them, and why they eventually abandon them. It also compares the leading products on the market across a number of key criteria such as performance, scalability and vendor support.”

You need to spend around USD 4995 for purchasing this survey. It will be worthwhile to spend for it if you are planning for buying a BI suite for your organization.

Here is the link: http://www.bi-survey.com/home.html

PivotViewer Extension for SQL Server Reporting Services

2010-09-30T18:17:00.001-07:00

You might have already seen Microsoft Silverlight PivotViewer for Excel (see my post on it). Just like that you are going to see PivotViewer for SSRS. Good news? Yes, but there is a bad news too, it is not yet available for downloading, it a kind of prototype, here how it goes;

Anyway, have a look on this video, this shows it.

SQL Server 2008 Service Pack 2 is available for downloading

2010-09-29T18:26:00.001-07:00

SQL Server 2008 SP 2 is available for downloading now. Enhancements includes;

15K partitioning Improvement
Reporting Services in SharePoint Integrated Mode
SQL Server 2008 R2 Application and Multi-Server Management Compatibility with SQL Server 2008
Data-tier Application (DAC) Support

Read more info on this and download from here.