Difference Between COALESCE and ISNULL

ISNULL is a T-SQL function to replace NULL values as COALESCE does. At some cases these two functions get the same results but there are some differences. Below is a summary of their differences and I hope that it will help you decide which function to use when you need to determine the one with better performance or better concurrency control.

	COALESCE	ISNULL
Standard ANSI SQL?	Yes	No (Only for T-SQL)
Numbers of Parameters	Two or more parameters.	Two parameters only.
Data type determination of the resulting expression	Determined by the data type of parameters with the highest precedence	Determined by data type of the first parameter
Allow both inputs to be untyped NULLs?	No. For SELECT COALESCE(NULL, NULL); , it will return an error. You need to have at least one of the null values be a typed NULL.  SELECT COALESCE(CAST(NULL AS INT), NULL);	Yes. For SELECT ISNULL(NULL, NULL); it will return a NULL typed as an integer.
NULLability of the resulting expression	Considered to be NULL with non-null parameters.	Always considered NOT NULLable
Working with subquery as input parameters	When executing COALESCE((subquery), 1), the subquery is evaluated twice.  you can get different results depending on the isolation level of the query.	Evaluated only once.

How to: Create Hierarchies in PowerPivot for Excel 2013

Just like hierarchies in SSAS, hierarchies in PowerPivot for Excel 2013 provides a very convenient way to let you group data through different layers. Today we will use date dimension to demonstrate how to create hierarchies in PowerPivot.

First of all, you need to be in the Diagram View of the PowerPivot Window. Click Diagram View button under Home Tab.

Create Hierarchies

Select the table you would like to build hierarchies in. Here we choose DimDate table. Then click the “Create Hierarchies” button on the upper right corner of the table. After renaming the hierarchy you can drag the fields from the table down to the hierarchy that was just created. An alternative is to select a column first and then right click the column > Create Hierarchies.

Add Hierarchies

Right click the column that you would like to add to a hierarchy. A column can only be in one hierarchy. If a column already exists in a hierarchy, there won’t have an option to “Add to Hierarchy”.

If you would like to add a column from another table, you need to use RELATED DAX function to add columns from one table to another table. Before you can add columns, you must create a relationship between these tables.

Adjust Hierarchy Levels

If you would like to adjust the levels between hierarchies, right click any one and move up or down, or remove.

After hierarchies are created, you can directly drag it to PivotTable Fields just as columns.

How to: Enable PowerPivot Add-ins for Excel 2013

Power Pivot in Microsoft Excel 2013 is a build-in add-in you can use to perform powerful data manipulation and analysis in Excel 2013. However, it is hidden somewhere and you have to enable it first before using it.

Here are steps to dig it out:

1. Go to File > Options > Add-Ins.

   

2. Change In the Manage box, click COM Add-ins from the drop down list and click Go. Note that the default is Excel Add-ins.

   

3. Check Microsoft Office Power Pivot in Microsoft Excel 2013 box > OK. You can check Power View and Power Query in similar way as you can see they are also listed in the COM Add-ins list.

   

4. Bingo! You are ready to use Power Pivot for Excel 2013! PowerPivot tab should appear in your ribbon as shown below: 

   

How to: Extract only Date Part From DateTime

It is common practice to extract only date part from datetime format, such as for a datetime of 2014-05-30 15:31:58.860, only 2014-05-30 is returned. Today we will show three main methods to choose from.

Method 1:

SELECT convert(DATE, GETDATE() )

Method 2:

SELECT CONVERT(VARCHAR(10),GETDATE(),126)

Method 3:

SELECT DATEADD(D, 0, DATEDIFF(D, 0, GETDATE()))

will mask the time part as 00:00:00.000.  It sometimes is regarded as better performance here.

SSIS's BULK insert error msg

Problem

When you are trying Bulk insert from csv file, here is the error message you may run into:

“Could not bulk insert because file 'C:csv_filename.txt' could not be opened. Operating system error code 3(The system cannot find the path specified.).”

Solution

First of all, confirm that you are specifying the UNC (Universal Naming Convention) path and not just drive letters. If you are trying to create the file to a remote location then the path should follow UNC, i.e:

             \\Server_Name or IP_ADDRESS\Shared_Folder_PATH(Name)\FILE_NAME

Note that the path may be OK if you are trying to create the file on the SAME server running SQL Server.

Secondly, make sure that the SQL Server service account has permissions to SQL Server instance.
For doing this you can use a Domain user or, create a new local user and start the services with that account.
Go to the lower left corner of the desktop, click START, input “services.msc”, then choose “SQL Server” as shown below:

Check Log On AS, a new window will pop up.

The default setting is Local System account. Try changing it with a domain user account and restart SQL Server Service after new changes.

Last but not least, make sure that this account has Read and Write permissions on the folder where you are creating the file. To do so, right click on the folder --> sharing and security --> permissions.

That’s it! Now the error message should have been swept away.

SSAS error msg: Unable to retrieve data mining algorithms

Problem

When you tried to create a new mining structure in SSAS, you may notice a warning message appears in the window as shown below:

“Unable to retrieve a list of supported data mining algorithms. Make sure you are connected to the correct Analysis Services server instance and the Analysis Services server named localhost is running and configured properly. You can continue with a default list of data mining algorithms.”

Solution:

Step 1: Check Analysis Service has been started from SQL Server Configuration Manager.

Step 2: Check the deployment property to make sure that it has been set up correctly.

Snowflake Schemas Vs. Star Schemas

Today we will briefly discuss the difference between star and snowflake schemas, and what you should consider when you make decisions for a data warehouse design.

What are star schemas and snowflake schemas?

In dimensional data warehousing, star schemas represent a dimensional model which is composed of a central fact table and multiple “de-nomalized” dimension tables around the fact table. The hierarchy dimension are flattened into a single table. For example, in the sample data warehouse of AdventureWorks, the customer dimension may contain attributes about customers such as title, name, date of birth, address in one table. Or for a data warehouse in healthcare, the provider dimension would contain attributes about providers and also practices that providers belong to. Thus, there are some redundancy for star schemas.

On the other hand, a snowflake schema can be regarded as a special type of a star schema. It contains a central fact table and multiple normalized tables to represent one dimension. For example, the Customer dimension can be represented by Customer table and Geography table as shown below.

In the example of the provider dimension, it may have one Practice table containing all practices’ attributes and another table contains only providers’ attributes and a foreign key pointing to the Practice table.

Differences or trade-offs

In terms of disk space, star schemas cost more space because snowflake schemas contain more normalized dimension tables, and thus removes some of the attribute redundancy in dimension tables. (But compared to the size of fact tables, the dimension tables usually are negligible.)

However, in terms of performance, star schemas usually give better performance in most cases because if a query in snowflake schemas may need many joins to do a data aggregation.

Also, a dimension table in snowflake schema may have one or more parent tables. So the dimensions in a snowflake schema must be highly controlled and managed to avoid update and insert anomalies.

Thus, Kimball design tips recommended star schemas in regards of understandability and performance. Even when using snowflake schemas, views are suggested to be placed on top of the dimensions so that they appear to the user as a single dimension.

Cases to choose snowflake schemas

There are some user cases that snowflake schemas may be a better choices:

• Based on query frequency: Large customer dimensions such as Amazon or Facebook, where, for example, 90 percent of the fact table measurements involve anonymous visitors about whom you collect little detail, and 10 percent involve reliably registered customers about whom you collect much detailed data by tracking many dimensions. Moving the 10 percent registered customers as a separate dimension can improve performance.
• Sparsely populated attributes, where most dimension records are NULL, are moved to a sub-dimension.
• Low cardinality attributes that are queried independently. For example, a product dimension may contain thousands of products, but only a handful of product types.  So if the product types are often queried independently, it is better to have a separate product type dimension. Another example could be the provider dimension may contain a very large number of demographic attributes at the practice level. Rather than repeat a large number of attributes in the provider dimension, we might create a special type of normalized practice dimension called a “dimension outrigger” where the attributes are stored in the provider dimension.
• Attributes that are part of a hierarchy and are queried independently. Examples include the year, quarter, and month attributes of a date hierarchy; and multiple enterprise calendar dimensions.

In summary, before making decision on which schema to use, you should have business requirements defined about how this data warehouse is used. You should reflect how users want to aggregate and “slice and dice” historical fact table data. You must embrace the goal of enhancing business value as its primary purpose. Work closely with business users, uncover the needs of your business users, then design a system that meet their needs!