All Data Warehouse Related Articles

Writing is my passion. Writing has opened me many avenues over the years. Thought of combining all the data warehouse related article into a one post in different areas in data warehousing. 

DESIGN

What is a Data Warehouse? (mssqltips.com)

Things you should avoid when designing a Data Warehouse (sqlshack.com)

Infrastructure Planning for a SQL Server Data Warehouse (mssqltips.com)

Why Surrogate Keys are Needed for a SQL Server Data Warehouse (mssqltips.com)

Create an Extended Date Dimension for a SQL Server Data Warehouse (mssqltips.com)

SQL Server Temporal Tables Overview (mssqltips.com)

Data Warehousing Best Practices for SQL Server (mssqltips.com)

 SSIS

•       Testing Type 2 Slowly Changing Dimensions in a Data Warehouse (sqlshack.com)

•       Implementing Slowly Changing Dimensions (SCDs) in Data Warehouses (sqlshack.com)

•       Incremental Data Extraction for ETL using Database Snapshots (sqlshack.com)

•       Use Replication to improve the ETL process in SQL Server (sqlshack.com)

•       Using the SSIS Script Component as a Data Source (sqlshack.com)

•       Fuzzy Lookup Transformations in SSIS (sqlshack.com)

•       SSIS Conditional Split overview (sqlshack.com)

•       Loading Historical Data into a SQL Server Data Warehouse (mssqltips.com)

•       Retry SSIS Control Flow Tasks (mssqltips.com)

•       SSIS CDC Tasks for Incremental Data Loading (mssqltips.com)

 SSAS

•       Multi-language support for SSAS (sqlshack.com)

•       Enhancing Data Analytics with SSAS Dimension Hierarchies (sqlshack.com)

•       Improve readability with SSAS Perspectives (sqlshack.com)

•       SSAS Database Management (sqlshack.com)

•       OLAP Cubes in SQL Server (sqlshack.com)

•       SSAS Hardware Configuration Recommendations (mssqltips.com)

•       Create KPI in a SSAS Cube (mssqltips.com)

•       Monitoring SSAS with Extended Events (mssqltips.com)

SSRS

•       Exporting SSRS reports to multiple worksheets in Excel (sqlshack.com)

•       Enhancing Customer Experiences with Subscriptions in SSRS (sqlshack.com)

•       Alternate Row Colors in SSRS (sqlshack.com)

  Cloud

•       Migrate On-Premises SQL Server Business Intelligence Solution to Azure (mssqltips.com)

Other

•       Dynamic Data Masking in SQL Server (sqlshack.com)

•       Data Disaster Recovery with Log Shipping (sqlshack.com)

•       Using the SQL Server Service Broker for Asynchronous Processing (sqlshack.com)

•       SQL Server auditing with Server and Database audit specifications (sqlshack.com)

•       Archiving SQL Server data using Partitions - SQL Shack

•       Script to Create and Update Missing SQL Server Columnstore Indexes (mssqltips.com)

•       SQL Server Clustered Index Behavior Explained via Execution Plans (mssqltips.com)

•       SQL Server Maintenance Plan Index Rebuild and Reorganize Tasks (mssqltips.com)

•       SQL Server Resource Governor Configuration with T-SQL and SSMS (mssqltips.com)

 

Data Warehouse in SQL Server

Data Warehouse is a comprehensive technology that provides the key people within an enterprise with access to any level of the required information within the enterprise. It is an enterprise-wide framework that permits the management of all enterprise information.

Let us see how we can utilise Microsoft technologies at varies stages of the Data Warehouse technologies. 

Let us look at how data design concepts can be used in Microsoft Technologies. First of all, you need to look at the infrastructure planning for a data warehouse. During the data warehouse design, it is important to include surrogate keys to dimension tables. Date dimensions is a special dimension that is used in data warehouse modelling. Historical data is an important aspect in a data warehouse that is used in Slowly Changing Dimensions (SCD).

SQL Server 2019 Reporting Services RC1 now available - here's all that's new

Last year, Microsoft announced SQL Server 2019, its new database management system (DBMS) that helps manage both relational and non-relational databases through a common service. It runs on-premise, in the cloud, and on Azure Stack as well.

Read more from https://www.neowin.net/news/sql-server-2019-reporting-services-rc1-now-available---heres-all-thats-new

SSRS Migration from SQL Server 2008 R2 to SQL Server 2017

As you are aware SQL Server 2008 R2 Microsoft support coming close to a shutdown, now people are starting to move to higher versions. 

This client wants to move its 800+ SSRS from SQL Server 2008 R2. Followings are learnings from the exercise.

1. SQL Server 2017 SSRS does not come as a single bundle of SQL Server. This means that you need to install SSRS as a separate installation. 

2. Though there are several ways of SSRS upgrade, ReportsServer database backup and restore was chosen as the method. In this, it is important to note that ReportServerTemp database also needs to be restored along with the ReportServer. SSRS service should be stopped before the restoring and with the restart of SSRS, SSRS services are upgraded automatically. Also, subscriptions schedules will be created as well. 

Do you think, you have completed your work. NOPE. still, a few touches are needed.

3. Authentications will not be transferred as a security measure. Therefore, you need to configure all of your data sources. If you have not managed your data sources carefully like this client, you will be coping and pasting connection strings, user names and password for one million times. 

4. If you have set up subscriptions for reports then there are few more things to be done. In standard subscriptions with excel, in the SQL Server 2008 R2 it will be configured as Excel 2003. This will be moved to that new version as it is, but will not work. It has to be changed to Excel. 

5. If you think that it is a tedious task in standard subscription, hold on for a second. There is more to come. In the case of Data-Driven Subscription, all the Excel file types are changed to MHTL Archive. Well, something little difficult to understand but that is how it is 

Changing the SSRS Subscription Owner

When subscriptions are created, owner of that subscription will be the user who creates it. However, when that user account is disabled, subscriptions will be failed and there is no way from the user interface to change it. However these subscriptions are stored in subscriptions table of ReportServer database (default database of the SSRS. This may be different if you have changed it from the SSRS configuration manager). In the subscription table, user id is stored as a unique identifier which you should get from the users table. Update query is shown below.

USE ReportServer

GO

DECLARE @OldUserID uniqueidentifier

DECLARE @NewUserID uniqueidentifier

SELECT @OldUserID = UserID FROM dbo.Users WHERE UserName = 'DOMAIN\previous_user'

SELECT @NewUserID = UserID FROM dbo.Users WHERE UserName = 'DOMAIN \ new_user’

UPDATE dbo.Subscriptions SET OwnerID = @NewUserID WHERE OwnerID = @OldUserID

No restart is of SSRS or SQL Server services are required. Soon after you update the userids subscriptions will start to work.

Design A Hybrid Report in SSRS

 

Do you know what Hybrid Report is? It is some thing like this.

How do you design hybrid reports using SSRS. Here is an answer to this FAQ.

An ASP.NET setting has been detected that does not apply in Integrated managed pipeline mode

Following error is occurring while trying to access report server or report manager in SQL Server Reporting Service 2005.

An ASP.NET setting has been detected that does not apply in Integrated managed pipeline mode

Reporting Services in SQL Server 2005 runs as on IIS 7.0. After configuring Reporting Service, two virtual directories will be created by default their Application pool is DefaultAppPool.

Every Application pool has Pipeline mode and there are two types of Pipeline modes namely, Integrated and Classic.

If the Pipeline mode of the Application Poll attached to virtual servers is Integrated you will get the above error.

You need to change the Integrated to Classic as shown below in IIS.

You do not have to restart neither IIS nor SQL Server Reporting Services.

There is another method. You need to create new application pool with Classic and attached that Application pool to the both virtual servers from Reporting Server Configuration Tool.

Error when SSRS Report Running from a .Net Report Viewer

When report viewer report added to the .Net application and report path is set to a deployed report to the report server following error message will occur.

The permissions granted to user '<DOMAIN>\IUSR_<DOMAIN>' are insufficient for performing this operation. (rsAccessDenied)

1. Open Internet Explorer.
2. Type the following in the Address bar:
   http:// application-tier/Reports/Pages/Folder.aspx 
   You can find the name of the report server by opening Team Explorer, expanding the Reports node, and viewing the properties of a report.
3. Click the Properties tab and then click New Role Assignment.
4. In Group or User Name, and add the Windows logon name for the person you want to add to this group.
5. In Role, select Content Manager, and then click OK.

Report Builder is not Launching

In SQL Server Reporting Services (SSRS) 2005 clicking the Report Builder option in Report Manager does not launch Report Builder Tool

You need dotnet framework 2.0 in your client machine to launch Report Builder. Verify whether you have installed dotnet framework 2.0. If it is already installed check the application log from the event viewer and verify whether there are any errors relevant to the Report Builder.

Error When Configuring SSRS Database

Error is occurring when trying to configure SSRS Database Setup from the Reporting Service Configuration Manager. Following is the error you get.

The database version (C.0.8.40) does not match your reporting services installation.  You must upgrade your reporting services database.
Couldn't generate the upgrade script.  There is no upgrade script available for this version.

Answer:

Error is occurring when it can't match with the database version of your reporting service installation. This is happening when you install you components in following order.

1. Install SQL Server 2005 Database Services.

2. Install SQL Server 2005 Service Pack 3.

3. Install SQL Server Reporting Services.

Because of this, SQL Server Report Services and Database Services versions are mismatching. Therefore, you need to install SQL Server Service Pack 3again so that both versions are equal.

SQL Server Reporting Server (SSRS) Service is Failing to Start

After a server reboots the SQL Server Reporting Server (SSRS) service is failing to start giving following two errors in the event log.

1. The SQL Server Reporting Services (MSSQLSERVER) service failed to start due to the following error:

The service did not respond to the start or control request in a timely fashion.

2. Timeout (30000 milliseconds) waiting for the SQL Server Reporting Services (MSSQLSERVER) service to connect.

This issue may occur if the service times out before it starts successfully. This issue is more likely to occur if your computer is heavily loaded.

To resolve this issue, increase the time-out value for service startup process. When you increase this value, the Microsoft ISA Server Storage service has more time to load when the computer starts. To increase the service startup time, create the following registry entry:

Subkey: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control

Name: ServicesPipeTimeout

Type: REG_DWORD

Data: The number of milliseconds that you want to give the services to start in

Typically, a data value of 35,000 is sufficient to keep the service from timing out. However, you can reduce or increase this value according to your specific startup requirements. For example, to use a time-out value of 60 seconds, assign a data value of 60,000 to the ServicesPipeTimeout registry entry. A larger data value does not decrease your computer's performance. To create this registry entry, follow these steps:

1. Click Start, click Run, type regedit, and then click OK.

2. Locate and then click the following registry subkey:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control

3. Right-click Control, point to New, and then click DWORD Value.

4. In the New Value #1 box, type ServicesPipeTimeout, and then press ENTER.

5. Right-click ServicesPipeTimeout, and then click Modify.

6. Click Decimal, type the number of milliseconds that you want to wait until the service times out, and then click OK.

For example, to wait 60 seconds before the service times out, type 60000.

7. Quit Registry Editor, and then restart the computer.

How Send the SSRS Report From SSIS?

This is a requirement when you need to send a SSRS report in Excel, PDF format to different users from SSIS after performing a data load.

First you need to create subscription to the report. You can create a SSRS report subscription from Report manager. At the report subscription you can mention the report format and the email address of the recipient. When you create a schedule for the SSRS report, SQL Server Agent Job will be created.

From the SSIS, by using sp_start_job and passing the relevant job name you can execute the SSRS report subscription.

dbfriend Video Center

I have started creating on SQL Server and those are published at http://www.dbfriend.magnify.net. To start with, I have created a short video on how to display SSRS tables with different row colors.

I am not a flashy presenter and these video may not be in super quality. However, I am trying to improve and sorry for any inconvenience.

Display only given number of records per page in SQL Server Reporting Services 2005 (SSRS)

Question:
In SQL Server Reporting Services 2005 (SSRS), how to display only given number of records per page?
Answer:
If you want to display only 20 rows per report page, it is not easy task as number of rows will depend on the font size and other printer defaults. Apart from this problem, if there are rows with lengthy text it tends to move to another line which will upset the number of rows in your report. So it is necessary to add some coding to get this feature to your reports.
Let us say, you want to have only 20 rows per page, add a group to the report with following expression.
=int((RowNumber(Nothing)-1)/20)

Then select page break at the end option for the group.

Convert Numeric Values into Words

This is very common question that you can find in SSRS forums as many users needs to display numeric data in words. Although there is a function in Crystal Reports, there is no function in SSRS. Nevertheless, you can do this in SSRS by writing your own function.

Lets us do this by an example using the following query:

SELECT Sales.SalesPerson.SalesPersonID AS SalesPerson,SUM(Sales.SalesOrderDetail.OrderQty * Sales.SalesOrderDetail.UnitPrice) AS AmountFROM Sales.SalesOrderDetailINNER JOIN Sales.SalesOrderHeader ON Sales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderIDINNER JOIN Sales.SalesPerson ON Sales.SalesOrderHeader.SalesPersonID = Sales.SalesPerson.SalesPersonIDGROUP BY Sales.SalesPerson.SalesPersonID

Follow the below steps to create a report.

1. Create a SSRS project.

2. Add new report the project.

3. Add a data source in which database is pointed to adventureworks.

4. Create a data set with the above query.

5. Drag and drop a table to layout from the toolbox and drag and drop the fields to the table.The next step is to create a function to convert numeric values into words.

Go to the Report menu option and select report properties. Select the Code tab in that dialog box and copy and paste the following code:

SHARED suffixes AS String() =

_{"Thousand ", "Million ", "Billion ", "Trillion ",

_"Quadrillion ", "Quintillion ", "Sextillion "}
SHARED units AS String() = _

{"","One ", "Two ", "Three ", "Four ", "Five ",

_ "Six ", "Seven ", "Eight ", "Nine "}

SHARED tens AS String() =

_{"Twenty ", "Thirty ", "Forty ", "Fifty ", "Sixty ",

_"Seventy ", "Eighty ", "Ninety "}

SHARED digits AS String() =

_{"Ten ","Eleven ", "Twelve ", "Thirteen ", "Fourteen ",

_"Fifteen ", "Sixteen ", "Seventeen ", "Eighteen ", "Nineteen"}

SHARED expr AS NEW

_System.Text.RegularExpressions.Regex("^-?d+(.d{2})?$", _System.Text.RegularExpressions.RegexOptions.None)

PUBLIC Function ExpandPrice(Price AS Double,

_Optional pSeparator AS String = ".")

_AS String

Dim pPrice As StringpPrice = FORMAT(Price,"##############.00")
Dim temp AS New System.Text.StringBuilder()

If Not expr.IsMatch(pPrice) Then

' temp.Append(pPrice) or whatever you want to do here

Else

Dim parts AS String() = pPrice.Split(pSeparator)

Dim dollars AS String = parts(0)

Dim cents AS String = parts(1)

If CDbl(dollars) > 1 Then

temp.Append(ExpandIntegerNumber(dollars) & "Dollars ")

If CInt(cents) > 0 Then

temp.Append("And ")

End If

ElseIf CDbl(dollars) = 0 Then

temp.Append(ExpandIntegerNumber(dollars) & "Zero Dollars ")

If CInt(cents) >= 0 Then

temp.Append("And ")

End If

ElseIf CDbl(dollars) = 1 Then

temp.Append(ExpandIntegerNumber(dollars) & "Dollar " )

End If
If CDbl(cents) > 1 Then

temp.Append(ExpandIntegerNumber(cents) & "Cents")

ElseIf CDbl(cents) = 0 Then

temp.Append(ExpandIntegerNumber(cents) & "Zero Cents ")

ElseIf CDbl(cents) = 1 Then

temp.Append(ExpandIntegerNumber(cents) & "Cent " )

End If

End If

RETURN temp.ToString()

End Function

Function ExpandIntegerNumber(pNumberStr AS String) AS String

Dim temp2 AS New System.Text.StringBuilder()

Dim number AS String =

_StrDup(3 - Len(pNumberStr) Mod 3, "0") & pNumberStr

Dim i AS Integer, j AS Integer = -1

Dim numPart AS String

For i = Len(number) - 2 To 1 Step -3

numPart = Mid(number, i, 3)

If Clng(numPart > 0) Then

If j > -1 Then

temp2.Insert(0,suffixes(j),1)

End If

End If

temp2.Insert(0,GetNumberUnder1000Str(numPart),1)

j += 1

Next

RETURN temp2.ToString()

End Function

Function GetNumberUnder1000Str(pNumber AS String) AS String

Dim temp1 AS New System.Text.StringBuilder()

If Len(pNumber) = 3 Then

If CLng(Left(pNumber, 1)) > 0 Then

temp1.Append(GetNumberUnder100Str(Left(pNumber, 1)) & "Hundred ")

End If

End If

temp1.Append(GetNumberUnder100Str(Right("0" & pNumber, 2)))

RETURN temp1.ToString()

End Function

Function GetNumberUnder100Str(pNumber AS String) AS String

If pNumber > 19 Then

RETURN tens(Left(pNumber, 1) - 2) & units(Right(pNumber, 1))

ElseIF pNumber >= 10 and pNumber <= 19 Then

RETURN digits(Right(pNumber, 1))

Else

RETURN units(Right(pNumber, 1))

End If

End Function

The above code is adopted from web site in the code snippet.Next, you have to call this function in your table. You need to enter the following function call at the column in which you need to have your number in word.

=Code.ExpandPrice(Fields!Amount.Value,".")

Finally you will see following screen.