Ad Manager Data Warehouse: Setup and configuration

From Dot2DotCommunications
Revision as of 14:10, 3 August 2018 by Ivan.belenski (Talk | contribs)

Jump to: navigation, search

Contents

Prerequisites

A SQL Server instance for the data warehouse

The server needs to be provisioned with adequate disk space for data and transaction logs. The data disk space requirements depend on the size of the snapshots and the number of snapshots that will be retained. The transaction log disk space requirements depend on the size of each snapshot. The size of the snapshots in turn depends on the number and size of the source Ad Manager databases and the cut-off date of the snapshot.

Since it's hard to know the disk space requirements in advance it would be best to use dynamic disks that can be expanded as required.

Ideally the SQL server machine will have a dedicated partition for data and a dedicated partition for transaction logs.

A Windows machine to run the data pump on

The data pump is a Windows executable and requires network access to both the source Ad Manager database server(s) and the data warehouse server.

This could be the data warehouse server itself.

SQL Server database users with read access in each Ad Manager database

On each SQL Server hosting an Ad Manager database, create a login for the data pump and map it to the Ad Manager database with the db_datareader role. (See Managing Logins, Users, and Schemas.)

SQL Server Management Studio and admin access to the data warehouse SQL Server

Install the data pump

Run the provided installer (AnalyticsDataPump-r####.msi) and follow the prompts.

The installer will create a Start menu folder named "Dot2Dot Analytics Data Pump".

Adp-01.png

Create the data warehouse database

Create the SQL Server database

Use SQL Server Management Studio to create a new database on the data warehouse SQL Server.

We suggest the name "amdw".

If you have provisioned a dedicated transaction log partition, change the path of the database log file (General page).

Set the recovery model of the database to "Simple" and enable the "Auto Shrink" Automatic option (Options page).

Create the database tables and views

Open the folder containing the data pump SQL scripts in Windows explorer (Start : Dot2Dot Analytics Data Pump : Sql).

Drag the file named "create-tables.sql" into SQL Server Management Studio and execute it against the data warehouse database.

Drag the file named "create-views.sql" into SQL Server Management Studio and execute it against the data warehouse database.

Create a database user with write access to the database

Create a login for the data pump and map it to the data warehouse database with the db_datawriter role.

Configure the data pump

Schedule the execution of the data pump

Monitor the data warehouse

Examine the data pump logs

Examine the data

Monitor disk space