Sizing the Server for the Data Warehouse Database

The following are the primary measurements that should be used when sizing a server for the Personify data warehouse database:

1.    Total concurrent users:  This is simply a total count of the number of DW users that will have access to the application. This includes all users, even the most occasional user.

2.    Total number of system records:  This defines the type of system modules will be store in the Data Warehouse database.

3.    How many years of historical data to keep: Number of years to keep historical data.

4.    What is the Personify yearly growth:  Number of records projected for yearly growth

 

The table below provides a reference point for sizing based upon the above criteria. 

Database Sizing Calculator for Personify Analytics Advantage

Data Mart

Query

Record Count
(Fill This column)

Factor

Fact Data
Record Size
(KB)

Index
Record Size
(KB)

History
Month

Monthly
Data + Index File Growth
(MB)

Total Data + Index file size
(MB)

Log files
(MB)

Backup
(MB)

Total Size MB

MBR

Number of customer from customer table

120000

12

0.9

0.2

60

168

10055

5027

10055

25137

REV

Number of Invoiced order from Order_Detail

3050000

12

0.5

0.2

60

38

2293

1147

2293

5734

MTG

Number of meeting products created in the past 5 years

500

1

0.4

0.2

60

322

19336

9668

19336

48340

FND

Number of Invoiced order from Order_Detail for FDN subsystem

25000

1

0.3

0.2

60

13

806

403

806

2014

 

 

 

 

 

 

 

 

 

 

 

 

Datamart database  size

32490

16245

32490

81224

Staging database  size (70% of Personify database size. Exclude temp and TRS tables)

25000

15000

25000

65000

Total space required for Datamart databases

 

 

 

146224

 

 

 

NOTES:

 

 

 

 

 

 

 

 

 

 

 

Does not include the custom tables data

 

 

 

 

 

 

 

 

Excluding the ETL repository databases since the size of the database is very small

 

 

 

 

 

Fill the columns highlighted in black

 

 

 

 

 

 

 

 

Queries used to DataMart count:

select count(*) as MBR from customer

select count(*) as REV from order_detail where invoice_no is not null and datediff(M,invoice_date ,getdate() ) <= 60

select count(*) as FND from order_detail where invoice_no is not null and datediff(M,invoice_date ,getdate() ) <= 60 and subsystem = 'FND'

select count(distinct product_code) as MTG from order_detail where invoice_no is not null and datediff(M,invoice_date ,getdate() ) <= 60 and subsystem = 'MTG' and product_type_code = 'M'

 

Server Classifications: see Personify DB Server classifications.