EDW Dimensions

Last Updated: September 20, 2024

Enterprise Data Warehouse (EDW) is currently only available for use with Windows OS.

The EDW has several dimensions or tables, which are the primary "entry" point into querying the data. Their attributes define what parameters you can use to query, and they are joined to the fact tables with a primary identifier. The attribute names in the dimension table should be easily understandable and clear, so that a user building a report can easily know how to use them to slice and dice the data and create ad-hoc requests.

When stored in the database, most dimension tables (with the exception of the Date and Time Of Day dimensions) contain an md5_hash attribute that stores a hash of data in the row. The value consists of an MD5 hash of the JSON encoded array containing the row's attributes. This is used to efficiently query existing dimensional values when moving new data into the data warehouse, without having to match each attribute in the row. When rows are persisted this hash generation is handled automatically.

Star Schema Data Model

The EDW star schema data model diagram

Bridge Printer Dimension to Structure

This dimension has a granularity of printer-structure pairs, so there will be a row in the dimension table for each printer-structure pair.

EDW Printer Structure Pairs

Attribute Name

Example Value

Data Type

Possible Values

Description

PrinterSK

234

int

0 to 2,147,483,647, not null

The unique surrogate key for the printer/structure pair

StructureSK_Leaf

345

int

0 to 2,147,483,647, not null

The key for the structure leaf

StructureSK_L1

2

int

0 to 2,147,483,647, not null

The structure key for depth level 1

StructureSK_L2

14

int

Any int or null

The structure key for depth level 2

StructureSK_L3

3

int

Any int or null

The structure key for depth level 3

StructureSK_L4

789

int

Any int or null

The structure key for depth level 4

StructureSK_L5

5217

int

Any int or null

The structure key for depth level 5

StructureSK_L6

8,254

int

Any int or null

The structure key for depth level 6

StructureSK_L7

2147483647

int

Any int or null

The structure key for depth level 7

StructureSK_L8

1234

int

Any int or null

The structure key for depth level 8

StructureSK_L9

1234

int

Any int or null

The structure key for depth level 9

StructureSK_L10

1234

int

Any int or null

The structure key for depth level 10

Date Dimension

This dimension has a granularity of days, so there will be a row in the dimension table for each calendar day.

EDW Date Dimensions

Attribute Name

Example Value

Data Type

Possible Values

Description

Date

2022-11-25

date

2010-01-01 to 9999-12-31

Date in format YYYY-MM-DD

StandardDate

10-25-2022

char(10)

01/01/2010 to 12/31/9999

Date in format MM/DD/YYYY

Day

25

tinyint

1 - 31

Day of the month

DaySuffix

25th

varchar(4)

1st - 31st

Day of the month with suffix added

DayName

Thursday

varchar(9)

Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday

Full name of the day of the week in title case

DayOfWeek

5

tinyint

1 - 7

Day number in the week. 1 = Sunday, 7 = Saturday

DayOfWeekInMonth

4

tinyint

1 - 5

The 4th Thursday of this month

DayOfWeekInYear

47

tinyint

1 - 53

The 47th Thursday of this year

DayOfWeekInQuarter

8

tinyint

1 - 14

The 8th Thursday of this quarter

DayOfYear

329

smallint

1 - 366

The day number in this year

WeekOfMonth

4

tinyint

1 - 5

The 4th week of this month (including partial weeks)

WeekOfQuarter

8

tinyint

1 - 14

The 8th week of this quarter (including partial weeks)

WeekOfYear

48

tinyint

1 - 53

The 48th week of this year (including partial weeks)

Month

11

tinyint

1 - 12

The 11th month in this year

MonthName

November

varchar(9)

January, February, March, April, May, June, July, August, September, October, November, December

Full month name

MonthOfQuarter

2

tinyint

1 - 3

The 2nd month in this quarter

Quarter

4

tinyint

1 - 4

The 4th quarter of this year

QuarterName

Q4

varchar(9)

Q1, Q2, Q3 or Q4. Also customizable to any 0-9 character string

The quarter in Q# format

Year

2022

smallint

2010-9999

The 4-digit year number

YearName

CY 2022

char(7)

CY 2010 - CY 9999

"Calendar Year" 2022

MonthYear

Nov-2022

char(10)

Jan-2010 - Dec-9999

The date in MMM-YYYY format

MMYYYY

112022

char(6)

012010 - 129999

The date in MMYYYY format

YYYYMM

202211

int

201001 - 999912

The date in YYYYMM format

YYYYQ

20224

int

20101 - 99994

The date in YYYYQ format

FirstDayOfMonth

2022-11-01

date

2010-01-01 to 9999-12-31 and <= than Date

The first day of this month as a date

LastDayOfMonth

2022-11-30

date

2010-01-01 to 9999-12-31 and >= than Date

The last day of this month as a date

FirstDayOfQuarter

2022-10-01

date

2010-01-01 to 9999-12-31 and <= than Date

The first day of this quarter as a date

LastDayOfQuarter

2022-12-31

date

2010-01-01 to 9999-12-31 and >= than Date

The last day of this quarter as a date

FirstDayOfYear

2022-01-01

date

2010-01-01 to 9999-12-31 and <= than Date

The first day of this year as a date

LastDayOfYear

2022-12-31

date

2010-01-01 to 9999-12-31 and >= than Date

The last day of this year as a date

IsHoliday

1

bit

0 or 1

Is this day a holiday? (localization)

IsWeekday

1

bit

0 or 1

Is this day Mon-Fri?

Holiday

Thanksgiving Day

varchar(50)

Any string (max length 50) or NULL

Name of the holiday

Department Dimension

This dimension has a granularity of departments, so there will be a row in the dimension table for each department. This data comes from the print stats table, it represents the department of the user at time of printing.

EDW Department Dimensions

Attribute Name

Example Value

Data Type

Possible Values

Description

DepartmentSK

32148

int

0 to 2,147,483,647, not null

The unique surrogate key for the department

DWUpdateTime

2022-11-12 14:57:43.69

datetime2(2)

2010-01-01 00:00:00.00 through 9999-12-31 23:59:59.99

The last time the Data Warehouse was updated. Precise to 1/100th of a second or 10 ms.

Name

Gringots Loans

varchar(255)

Any string (max length 255)

The name of the department

Document Dimension

This dimension has a granularity of documents, so there will be a row in the dimension table for each document.

EDW Document Dimensions

Attribute Name

Example Value

Data Type

Possible Values

Description

DocumentSK

32148

int

0 to 2,147,483,647, not null

The unique surrogate key for the department

DWUpdateTime

2022-11-12 14:57:43.69

datetime2(2)

2010-01-01 00:00:00.00 through 9999-12-31 23:59:59.99

The last time the Data Warehouse was updated. Precise to 1/100th of a second or 10 ms.

Title

Fantastic Creatures and Where to Find Them

varchar(1022)

Any string (max length 1022)

The title of the document

Job Dimension

This dimension has a granularity of jobs, so there will be a row in the dimension table for each job.

EDW Job Dimensions

Attribute Name

Example Value

Data Type

Possible Values

Description

JobSK

32148

int

0 to 2,147,483,647, not null

The unique surrogate key for the print job

DWUpdateTime

2022-11-12 14:57:43.69

datetime2(2)

2010-01-01 00:00:00.00 through 9999-12-31 23:59:59.99

The last time the Data Warehouse was updated. Precise to 1/100th of a second or 10 ms.

Title

That TPS Report (with cover sheet this time)

varchar(255)

Any string (max length 255)

The title of the print job

Paper Cost Dimension

This dimension has a granularity of paper cost types, so there will be a row in the dimension table for each paper cost type.

EDW Paper Cost Dimensions

Attribute Name

Example Value

Data Type

Possible Values

Description

PaperCostSK

32148

int

0 to 2,147,483,647, not null

The unique surrogate key for the paper cost

DWUpdateTime

2022-11-12 14:57:43.69

datetime2(2)

2010-01-01 00:00:00.00 through 9999-12-31 23:59:59.99

The last time the Data Warehouse was updated. Precise to 1/100th of a second or 10 ms.

Name

Color duplex

varchar(255)

Any string (max length 255)

The name of the paper cost type

Cost

0.078900

smallmoney

0 to 214,748.3647

The cost of a single page of this type at the time the job was printed

Paper Size Dimension

This dimension has a granularity of paper sizes, so there will be a row in the dimension table for each paper size.

EDW Paper Size Dimensions

Attribute Name

Example Value

Data Type

Possible Values

Description

PaperSizeSK

34

int

0 to 2,147,483,647, not null

The unique surrogate key for the paper size

DWUpdateTime

2022-11-12 14:57:43.69

datetime2(2)

2010-01-01 00:00:00.00 through 9999-12-31 23:59:59.99

The last time the Data Warehouse was updated. Precise to 1/100th of a second or 10 ms.

Name

Letter

varchar(100)

Any string (max length 100)

The name of the paper size

FormatStandard

ANSI

varchar(25)

Any string (max length 25)

The organization which defines this standard paper size

WidthInch

8.50

smallmoney

0 to 214,748.3647

The width of the paper size in inches

HeightInch

11.00

smallmoney

0 to 214,748.3647

The height of the paper size in inches

WidthMM

216.00

smallmoney

0 to 214,748.3647

The width of the paper size in millimeters

HeightMM

279.00

smallmoney

0 to 214,748.3647

The height of the paper size in millimeters

IsDefault

1

bit

0 or 1

Whether this paper size is the default size

Printer Dimension

This dimension has a granularity of printers, so there will be a row in the dimension table for each printer.

EDW Printer Dimensions

Attribute Name

Example Value

Data type

Possible Values

Description

PrinterSK

14

int

0 to 2,147,483,647, not null

The unique surrogate key for the printer

DWUpdateTime

2022-11-23 09:45:57.76

datetime2(2)

2010-01-01 00:00:00.00 through 9999-12-31 23:59:59.99

The last time the Data Warehouse was updated. Precise to 1/100th of a second or 10 ms.

PrinterID

4

int

Any int (up to 2,147,483,647)

The ID number of the printer

SourceDatabaseID

1

tinyint

0 to 255

The ID number of the source database

CreatedDate

2022-10-06

date

0001-01-01 to 9999-12-31

The date the printer was created

CreatedTime

19:49:18

time(0)

00:00:00 through 23:59:59

The time the printer was created

ModifiedDate

2022-07-11

date

0001-01-01 to 9999-12-31

The last date the printer was modified

ModifiedTime

21:50:15

time(0)

00:00:00 through 23:59:59

The time the printer was modified. Precise to 1 second

Name

CEO Printer

varchar(255)

Any string (max length 255)

The name of the printer

PrinterTypeID

1

smallint

0 to 32,767

The type ID of the printer

PrinterType

Managed TCP/IP

varchar(100)

Any string (max length 100)

The connection type for the printer

Location

Boss Office

varchar(255)

Any string (max length 255)

Where the printer is located (manually specified)

Description

Lexmark CX510de

varchar(275)

Any string (max length 275)

A description of the printer

Make

Lexmark

varchar(255)

Any string (max length 255)

The make of the printer

IPAddress

10.1.0.212

varchar(128)

Any valid IPv4 address

The printer's IP address

PortNumber

9100

smallint

0 to 32767

The printer's port number

MacAddress

~Unknown

varchar(200)

Any string (max length 200)

The printer's mac address

SerialNumber

~Unknown

varchar(200)

Any string (max length 200)

The printer's serial number

IsColor

2

varchar(20)

0 = Unknown, 1 = Yes, Other (ie 2) = No

Whether the printer can print in color or not

SupportsDirectEmail

1

tinyint

Always 1

Whether the printer supports direct email

DirectEmailEnabled

0

tinyint

0 = No, 1 = Yes. Global can override to Yes

Whether direct email is enabled

DirectEmailGuestEnabled

0

tinyint

IsSharedPrinter Global can override to Yes

Whether direct email is enabled for guests

DirectEmailAddress

ceoprinter

varchar(255)

Any string (max length 255)

The email address for direct emailing

MobilePrinting

0

tinyint

0 = No, 1 = Yes. Global can override to Yes

Whether the printer supports mobile printing

Duplex

Unspecified

varchar(20)

0 = Unspecified, 1 = Yes, Other (ie 2) = No

Whether the printer supports duplex printing (on both sides of a page)

SupportsSecureRelease

1

tinyint

Always 1

Whether the printer supports secure release printing

SecureReleaseEnabled

1

tinyint

0 = No, 1 = Yes. Global can override to Yes

Whether secure release printing is enabled

CanOverrideSecureRelease

1

tinyint

0 = No, 1 = Yes. Global can override to Yes

Whether secure release printing can be overridden

SupportsPullPrint

1

tinyint

Always 1

Whether the printer supports pull printing

PullPrintEnabled

0

tinyint

0 = No, 1 = Yes. Global can override to Yes

Whether pull printing is enabled

ConsolePrintingEnabled

0

tinyint

0 = No, 1 = Yes

Whether printing from the console is enabled

ConsoleVersion

16.1.5.63

varchar(25)

Any string (max length 25)

The version number for console printing

GlobalLoginConsole

0

tinyint

0 = No, 1 = Yes

Whether the printer uses global login for console printing

SupportsConsoleSSO

0

tinyint

0 = No, 1 = Yes. Yes if make is in ['hp','toshiba','xerox','konica-minolta','ricoh']

Whether the printer supports console SSO (single sign on) authentication

ConsoleSSOEnabled

0

tinyint

0 = Enabled, 1 = Disabled (counterintuitive). Global can override to Disabled

Whether console SSO authentication is enabled

IsBadgeScanEnabled

0

tinyint

0 = No, 1 = Yes

Whether badge scanning is enabled

IsSimpleBadgeReleaseEnabled

0

tinyint

0 = No, 1 = Yes

Whether simple badge release is enabled

SimpleBadgeReleaseDevice

RFIDeas Ethernet 241

varchar(25)

0='RFIDeas Ethernet 241', 1='Elatec TCPConv 2', Other='~None'

The name of the simple badge release device

SimpleBadgeReleaseType

All

varchar(25)

0='Latest', 1='All', Other='~Not Applicable'

The type of simple badge release

IsSharedPrinter

0

tinyint

0 = No, 1 = Yes

Whether this is a shared printer

Printer Custom General Dimension

This dimension has a granularity of printers, so there will be a row in the dimension table for each printer.

EDW Printer Custom Dimensions

Attribute Name

Example Value

Data Type

Possible Values

Description

PrinterSK

32148

int

0 to 2,147,483,647, not null

The unique surrogate key for the printer

DWUpdateTime

2022-11-12 14:57:43.69

datetime2(2)

2010-01-01 00:00:00.00 through 9999-12-31 23:59:59.99

The last time the Data Warehouse was updated. Precise to 1/100th of a second or 10 ms.

Print Job Type Dimension

This dimension has a granularity of job types, so there will be a row in the dimension table for each job type.

EDW Job Type Dimensions

Attribute Name

Example value

Data Type

Possible Values

Description

PrintJobTypeSK

32148

int

0 to 2,147,483,647, not null

The unique surrogate key for the print job type

DWUpdateTime

2022-11-12 14:57:43.69

datetime2(2)

2010-01-01 00:00:00.00 through 9999-12-31 23:59:59.99

The last time the Data Warehouse was updated. Precise to 1/100th of a second or 10 ms.

TypeName

Secure Release

varchar(50)

Any string (max length 50)

The security type for the print job

Source Database Dimension

This dimension has a granularity of source databases, so there will be a row in the dimension table for each source database.

EDW Source Database Dimensions

Attribute Name

Example Value

Data Type

Possible Values

Desription

SourceDatabaseID

1

tinyint

0 to 255, not null

The unique database ID

CreatedTime

2022-11-12 14:57:43.69

datetime2(2)

2010-01-01 00:00:00.00 through 9999-12-31 23:59:59.99

The last time the Data Warehouse was updated. Precise to 1/100th of a second or 10 ms.

Name

mysql

varchar(100)

Any string (max length 100)

The name of the source database

Source Machine Dimension

This dimension has a granularity of source machines, so there will be a row in the dimension table for each source machine.

EDW Source Machine Dimensions

Attribute Name

Example Value

Data Type

Possible Values

Description

SourceMachineSK

2

int

0 to 2,147,483,647, not null

The unique surrogate key for the source machine

DWUpdateTime

2020-11-12 14:57:43.69

datetime2(2)

2010-01-01 00:00:00.00 through 9999-12-31 23:59:59.99

The last time the Data Warehouse was updated. Precise to 1/100th of a second or 10 ms.

Name

JS-SVR2019

varchar(160)

Any string (max length 160)

The name of the source machine

IPAddress

192.168.11.115

varchar(30)

Any valid IPv4 ip address

The IP address of the source machine

Structure Dimension

This dimension has a granularity of structures (companies, folders, printers), so there will be a row in the dimension table for each structure.

EDW Structure Dimensions

Attribute

Example Value

Data Type

Possible Values

Description

StructureSK

5

int

0 to 2,147,483,647, not null

The unique surrogate key for the structure

DWUpdateTime

2022-11-12 14:57:43.69

datetime2(2)

2010-01-01 00:00:00.00 through 9999-12-31 23:59:59.99

The last time the Data Warehouse was updated. Precise to 1/100th of a second or 10 ms.

SourceDatabaseID

1

tinyint

0 to 255

The source database for this structure

StructureName

Printer Nest

varchar(255)

Any string (max length 255)

The name of the structure

TypeID

24

int

0 to 2,147,483,647

The type ID of the structure

Time of Day Dimension

This dimension has a granularity of minutes, so there will be a row in the dimension table for each minute.

EDW Time of Day Dimension

Attribute Name

Eample Value

Data Type

Possible Values

Description

DimTimeOfDaySK

00:17:00

time(0)

00:00:00 through 23:59:59

The unique surrogate key for the time of day

CivilianTime

12:17

char(5)

12:00 through 11:59

A string for the time of day in civilian time

MilitaryTime

00:17

char(5)

00:00 through 23:59

A string for the time of day in military time

HourOfDay

0

tinyint

0-23

The hour of the day as an integer

AMPM

AM

char(2)

AM or PM

Whether the time of day is in the first or second half of the day

User Dimension

This dimension has a granularity of users, so there will be a row in the dimension table for each user.

EDW User Dimensions

Attribute Name

Example Value

Data Type

Possible Values

Description

UserSK

3

int

-1 to 2,147,483,647

The unique surrogate key for the user

DWUpdateTime

2022-11-12 14:57:43.69

datetime2(2)

2010-01-01 00:00:00.00 through 9999-12-31 23:59:59.99

The last time the Data Warehouse was updated. Precise to 1/100th of a second or 10 ms.

UserName

CS\X149248

varchar(160)

Any string (max length 160)

The user name used to log in

FullName

Jane R Doe

varchar(160)

Any string (max length 160)

The full name of the user

DomainUser

X149248

varchar(160)

Any string (max length 160)

The network domain of the user

Domain

CS

varchar(160)

Any string (max length 160)

The network domain

Email

~No Email

varchar(160)

Any string (max length 160)

The email of the user

EmailDomain

CS

varchar(160)

Any string (max length 160)

The domain of the user's email

IsManager

0

bit

0 or 1

Whether the user is a manager

Print Stats Facts

This fact has a granularity of print jobs, so there will be a row in the dimension table for each print job.

EDW Print Stats Dimensions

Attribute Name

Example Value

Data Type

Possible Values

Description

PrintStatsSK

5

int

0 to 2,147,483,647

The unique surrogate key for the print stat

DWUpdateTime

2022-12-02 14:05:00.90

datetime2(2)

2010-01-01 00:00:00.00 through 9999-12-31 23:59:59.99

The last time the Data Warehouse was updated. Precise to 1/100th of a second or 10 ms.

PrintStatsID

5

int

0 to 2,147,483,647

The print stats ID

SourceDatabaseID

1

tinyint

0 to 255

The key for the source database

PrinterSK

8760

int

0 to 2,147,483,647

The key for the printer

DateSK_Submitted

2022-11-26

date

2010-01-01 to 9999-12-31

The key for the date submitted

TimeSK_Submitted

13:38:07

time(0)

00:00:00 through 23:59:59

The key for the time submitted

DateSK_Printed

2022-11-26

date

2010-01-01 to 9999-12-31

The key for the date of printing

TimeSK_Printed

13:38:09

time(0)

00:00:00 through 23:59:59

The key for the time of printing

UserSK_Printed

2219

int

-1 to 2,147,483,647

The key for the user. -1 = '~No User'

UserSK_Manager

10860

int

-1 to 2,147,483,647

The key for the user's manager. -1 = '~No User'

DepartmentSK

42

int

-1 to 2,147,483,647

The key for the department. -1 = '~No Department'

PaperSizeSK

34

int

0 to 2,147,483,647

The key for the paper size

PrintJobTypeSK

0

tinyint

0 to 255

The key for the print job type

SourceMachineSK

2120

int

0 to 2,147,483,647

The key for the source machine

JobSK

943

int

0 to 2,147,483,647

The key for the job

DocumentSK

178179

int

0 to 2,147,483,647

The key for the document

MonoSimplexCount

53

int

0 to 2,147,483,647

The number of single sided black and white pages

ColorSimplexCount

4

int

0 to 2,147,483,647

The number of single sided color pages

MonoDuplexCount

0

int

0 to 2,147,483,647

The number of double sided black and white pages

ColorDuplexCount

0

int

0 to 2,147,483,647

The number of double sided color pages

TotalPages

57

int

0 to 2,147,483,647

The total number of pages

MonoSimplexCost

0.0789

smallmoney

-214748.3648 to 214,748.3647

The cost of each single sided black and white page

ColorSimplexCost

0.1523

smallmoney

-214748.3648 to 214,748.3647

The cost of each single sided color page

MonoDuplexCost

0.00

smallmoney

-214748.3648 to 214,748.3647

The cost of each double sided black and white page

ColorDuplexCost

0.00

smallmoney

-214748.3648 to 214,748.3647

The cost of each double sided color page

MonoSimplexTotalCost

4.1817

smallmoney

-214748.3648 to 214,748.3647

The total cost of all single sided black and white pages added together

ColorSimplexTotalCost

0.6092

smallmoney

-214748.3648 to 214,748.3647

The total cost of all single sided color pages added together

MonoDuplexTotalCost

0.00

smallmoney

-214748.3648 to 214,748.3647

The total cost of all double sided black and white pages added together

ColorDuplexTotalCost

0.00

smallmoney

-214748.3648 to 214,748.3647

The total cost of all double sided color pages added together

TotalCost

4.7909

money

0 to 922,337,203,685,477.5807

The total cost of the print job

WasPrinted

1

tinyint

(0 = No, 1 = Yes)

Whether the print was printed

IsColor

1

tinyint

(0 = No, 1 = Yes)

Whether the print is color or black and white