Install Data Warehouse Service Point in SCCM ConfigMgr

In this post I will cover the steps to install Data Warehouse service point in SCCM ConfigMgr. Data Warehouse service point role was first introduced in Configuration Manager current branch version 1702. You can read my about it in my post on SCCM current branch 1702 features. And then in SCCM version 1810, we saw some improvements made to data warehouse service point. Microsoft added option to synchronize more tables from the site database to the data warehouse.

What is Data Warehouse Point role in SCCM

So what is Data Warehouse point role in SCCM ?. The purpose of data warehouse service point is to store and report on long-term historical data for your Configuration Manager deployment. Wait a minute, should i install this role on my SCCM server ?. The answer is – probably not unless you need long term historical data to be preserved.

Configuration Manager comes with built-in Site Maintenance tasks. These are maintenance tasks that are important to ensure correct site operations. As the tasks run, the amount of data collected by Configuration Manager grows. Hence the database performance and the free storage space in the database is reduced. Most of the maintenance tasks periodically remove out-of-date data from the Configuration Manager database.

What if you wanted to regain that data which was deleted from SCCM database ?. There is no way to retrieve the data back. And this was exactly a worry for admins using SCCM versions prior to version 1702. Starting with SCCM 1702, a new role was introduced in Configuration Manager called Data Warehouse service point role.

When you install data warehouse service point, it synchronizes data from SCCM site database to the data warehouse database. You can then access the information from your reporting service point. Most of all when you install Data warehouse role, it also installs additional reports that can be used by business to retrieve historical data.

Data synchronized to the data warehouse database is kept for three years. Furthermore there is a built-in task that removes data that’s older than three years. Now that you know what data warehouse role is for, it’s up to you whether you want to install it or not.

Data Warehouse Point role Prerequisites

Before you decide to install Data Warehouse point role, there are many prerequisites that you need to consider. I will list all of them below.

• You must install data warehouse site system role only at the top-tier site of your hierarchy. It’s either a central administration site or standalone primary site. In this post I will be installing Data Warehouse point role for my primary site.
• If you have decided to install the role on a separate computer, ensure that .NET Framework 4.5.2 or later is installed.
• You must grant the “Reporting Services Point Account” the db_datareader permission on the data warehouse database.
• The data warehouse database requires at least SQL Server 2012 or later. The SQL server edition can be Standard, Enterprise, or Data-center.
• SQL Server that hosts the database for the warehouse can be local to the site system role or remote. I also prefer to install SQL server on a different server than using site database server.
• The SQL Server version for the data warehouse doesn’t need to be the same as the site database server. However I prefer to keep both the SQL server versions same.
• The warehouse database supports the following SQL Server configurations: default or named instance, SQL Server Always On availability group and SQL Server fail over cluster.

Here is a very important prerequisite out of all. You might encounter issues while installing data warehouse role if you don’t meet the below conditions.

To synchronize data with the data warehouse database, Configuration Manager uses the computer account of the site system role. This account requires the following permissions :-

1. Administrator on the computer that hosts the data warehouse database.
2. DB_Creator permission on the data warehouse database.
3. Either DB_owner or DB_reader with execute permissions to the top-tier site’s database.

SQL Server Installation for Data Warehouse point role

As mentioned earlier, SQL Server that hosts the database for the warehouse can be local to the site system role or remote. In this post I will be installing SQL server on a different server. The data warehouse database will be located on this server.

The SQL server installation is quite similar to the one that you installed during your SCCM installation. You can follow this guide where I have documented the steps to install SQL server for Configuration Manager.

Some additional points to remember here :-

• You don’t need to install all the features that come with SQL server. Install only the following features – Database Engine Services, Reporting Services-Native and Management Tools – Complete.
• Ensure the collation is SQL_Latin1_General_CP1_CI_AS. Else you will need to refer to this post to change collation.
• While installing SQL server, I have decided to go with a default instance rather than named instance. You can go with either default instance or named instance.

Creating Data Warehouse Database

When you install SQL server for Data Warehouse role, there is no database created. During the Data warehouse service point role installation, configuration manager can create a database for you. However I prefer to create the database manually and use it to store the data.

Launch SQL server management studio. Right click Databases and create a new Database. Specify a name to the database and click OK. Ensure that the important prerequisites that I mentioned above are taken care of.

Install Data Warehouse Service Point

To install Data Warehouse service point role for SCCM :-

• Launch Configuration Manager console.
• Navigate to Administration > Overview > Site Configuration > Sites.
• Select the site and right click. Now click Add Site System Roles.
• On the General page, click Next.

I am not using any proxy server in my setup, so I will click Next.

On System role selection page, check the box “Data Warehouse Service point”. Click Next.

On this page you need to specify Data Warehouse service points settings.

• Specify the SQL FQDN – In this case, it is my SQL server that hosts data warehouse database.
• If you have created a named instance during SQL installation, specify it in step 2. If it’s a default instance, leave it blank.
• Specify the database name that you created in the above step. If you do not specify the database name, configuration manager will create one for you.
• The SQL server port used for connection is port 1433.
• In the next step specify Data Warehouse service point account. Click Set and specify the right account that SQL Reporting Service will use when connecting to Data Warehouse database. Click Next.

In this step you can configure the data synchronization schedule. By default when you install the data warehouse service point role, only for once the synchronization is run automatically. Post then it the sync runs based on schedule that you specify here.

In addition to that, you see an option to select additional tables. You can select more tables if you want to. This was recently added as an improvement to Data Warehouse point in SCCM 1810.

Click Next.

Finally on Completion page, click Close.

Data Warehouse Service Point Reports

After you install a data warehouse service point, several reports become available on the reporting services point for the site. In case you uninstall reporting services point role and add it back, the data warehouse reports are added automatically.

Here are the list of reports that are created when you install Data Warehouse service point role.

• Application Deployment – Historical
• Endpoint Protection and Software Updates Compliance – Historical
• General Hardware Inventory – Historical
• General Software Inventory – Historical
• Infrastructure Health Overview – Historical
• List of Malware Detected – Historical
• Software Distribution Summary – Historical

Data Warehouse Service Point Log Files

There are three log files that are created when you install Data Warehouse Service Point. In case you want to verify Data Warehouse role installation or troubleshoot issues related to Data Warehouse synchronization, these log files will help you.

• DWSSMSI.log
• DWSSSetup.log
• Microsoft.ConfigMgrDataWarehouse.log

DWSSSetup.log – Use this log file to verify the installation of data warehouse service point.

DWSSMSI.log – Pretty much the same as DWSSSetup.log. But you clearly see a message in this log file about role installation. Windows Installer installed the product. Product Name: Data Warehouse Service Point. Product Version: 5.00.8740.1000. Manufacturer: Microsoft Corporation. Installation success or error status: 0.

Microsoft.ConfigMgrDataWarehouse.log – Records information about data synchronization between the site database and the data warehouse database. Very useful to verify if there are any sync issues.

So we have successfully installed Data Warehouse Service Point in SCCM ConfigMgr. But wait it’s not over yet.

Error while opening reports

It is a known issue what when you open a data warehouse report, it returns the following error :-

An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'AutoGen__39B693BB_524B_47DF_9FDB_9000C3118E82_'. (rsErrorOpeningConnection)
A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.)

And Microsoft has got a workaround to deal with this issue. I will be covering it in my next post. For now you can use this workaround to access the data warehouse reports.