Setup a SCCM Data Warehouse Feature

This blog post is a step-by-step guide to help you with the setup of the new data warehouse service point feature in System Center Configuration Manager (SCCM).

What is the data warehouse (DWH) service point? This new feature in SCCM allows you to store long term details about your SCCM environment. It will NOT store everything, but some of the data it does store includes hardware and software inventory, detected malware and software deployments. According to the Microsoft Docs you can, “Use the data warehouse service point to store and report on long-term historical data for your Configuration Manager deployment.” By the way, the first release of this feature was in SCCM 1706 and prior to that it was a beta feature.

Getting Started

Prior to starting this guide, you should create an AD user called cm-dwh. This account will be used by SSRS to access the data warehouse (DWH) database.

After creating the AD user, I created a single, purpose-built server. This server will only house the SCCM DWH. My DWH server is a virtual machine (VM) with two drives (C: and E:). Both drives are 127 GB VHDs, which for now, should be more than enough space. Other than the size of the drives, there is nothing else special about this VM.

Following best practices, nothing should ever be installed on the C: drive. This drive should always be reserved for the Operating System (OS) only. As such, all default components of SQL Server 2016 were installed on the E: drive. Here’s how it maps out:

The C: drive houses the OS only.

The E: drive houses the SCCM role, SQL Server and the DWH database.

Next, in SQL Server, you need to grant local administrator access and SQL Server System Administrator (SA) access to the DWH server’s computer account. Generally, the local system account is also known as the computer account or NT AuthoritySystem. This is the account that needs this access.

If you don’t already have SSMS installed, you will need to install it in order to grant SA access in SQL Server. For more details see, Where is SQL Server Management Studio (SSMS)?

Setup the Data Warehouse

I used SCCM 1806 as the basis for this guide.

Starting in the SCCM console, open Administration | Overview | Site Configuration | Server and Site System Roles. On the home tab, click on Create Site System Server.

Click on the Browse… button.

Enter your new site server name and then click on Check Names. Once successful, click OK to continue.

Select your Site code, CB1 is my CAS, and then click Next.

Click Next.

Select Data Warehouse service point and then click Next.

Enter your SQL Server name and instance details. In my example, since both SQL Server and the DWH database are installed on the same server with the default instance, I’m using the DWH server name. Next, I reviewed and accepted the default for database name and port, before clicking on the Set… button.

Select New Account.

Enter the user name and password details, and then click OK.

Click Next.

I changed the start time from 3:00 a.m. to 3:49 a.m. in order to reduce the chances of another maintenance task running at the same time. Click Next to continue.

Click Next. Within a few seconds you will proceed to the Completion pane.

Click Close to complete the setup of the DWH service point role.

Data Warehouse Reports

Now that the DWH is created, you will find either on the Reporting Point or within the SCCM console, a new folder called Data Warehouse.

This folder currently houses seven reports that will provide you with historical data from the DWH database. Below is a list of the reports as found on Microsoft’s SCCM Document site:

Microsoft is offering a very limited number of reports, so what DWH information would you like to see in a report? What do you think Enhansoft should create in the way of DWH dashboards or reports? I want to know, so contact me via our info@recastsoftware.com email address.