Reporting Schema

Project: SqlServer

Layout: Reporting

 

Tip: Click on a table to jump to the field-level descriptions for the table.

 


DIM_Date

DateKey

int NOT NULL

FullDate

datetime

DateNameUS

varchar( 11 ) NOT NULL

DateNameEU

varchar( 11 ) NOT NULL

DayOfWeekNumber

int

DayOfWeekText

nvarchar( 30 )

IsWeekDay

bit

DayOfYearNumber

int

DayOfMonthNumber

int

CalendarYear

int

CalendarQuarter

int

CalendarYearQuarter

int

CalendarMonth

int

CalendarYearMonth

int

CalendarMonthName

nvarchar( 30 )

LastDayInMonth

int

CalendarWeek

int

CalendarYearWeek

int

Indexes

PK_DIM_Date primary key

ON DateKey

 

DIM_User

UserKey

int NOT NULL AUTOINCREMENT

UserId

uniqueidentifier

UserName

nvarchar( 256 )

FirstName

nvarchar( 50 )

LastName

nvarchar( 50 )

RecordTimestamp

datetime NOT NULL

IsDeleted

bit NOT NULL DEFO 0

DeletedTimestamp

datetime

Indexes

PK_DIM_User primary key

ON UserKey

 

F_UserActiveAudit

UserActiveAuditId

int NOT NULL AUTOINCREMENT

UserKey

int NOT NULL

IsActive

bit NOT NULL

DateKey

int NOT NULL

RecordTimestamp

datetime NOT NULL DEFO getdate()

Indexes

IDX_F_UserActiveAudit_ByUserKey

ON UserKey

 

ParameterListDefinition

ParameterListDefinitionId

int NOT NULL AUTOINCREMENT

Name

nvarchar( 128 ) NOT NULL

StoredProcedure

nvarchar( 128 ) NOT NULL

RequiresCpeId

bit NOT NULL

ProductId

int NOT NULL

DisplayColumn

nvarchar( 128 ) NOT NULL

ValueColumn

nvarchar( 128 ) NOT NULL

Indexes

PK_ParameterListDefinition_ID primary key

ON ParameterListDefinitionId

UX_ParameterListDefinition_UniqueProductName unique

ON Name, ProductId

Foreign Keys

FK_ParameterListDefinition_Product

( ProductId ) ref Product (ProductId)

 

ReportDefinition

ReportDefinitionId

int NOT NULL AUTOINCREMENT

Name

nvarchar( 128 ) NOT NULL

ProductId

int NOT NULL

ReportTypeId

int NOT NULL

IsCommonReport

bit NOT NULL

ExecutionMetadata

nvarchar(max) NOT NULL

DisplayMetadata

nvarchar(max)

Description

nvarchar(max)

Indexes

PK_ReportDefinition_ID primary key

ON ReportDefinitionId

UX_ReportDefinition_UniqueName unique

ON Name

Foreign Keys

FK_ReportDefinition_Product

( ProductId ) ref Product (ProductId)

FK_ReportDefinition_ReportType

( ReportTypeId ) ref ReportType (ReportTypeId)

 

ReportDefinitionAttachment

Id

int NOT NULL AUTOINCREMENT

ReportDefinitionId

int NOT NULL

AttachmentId

int NOT NULL

IsWebImage

bit NOT NULL

Indexes

PK_ReportDefinitionAttachment_ID primary key

ON Id

Foreign Keys

FK_ReportDefinitionAttachment_Attachment

( AttachmentId ) ref Attachment (Id)

FK_ReportDefinitionAttachment_ReportDefinition

( ReportDefinitionId ) ref ReportDefinition (ReportDefinitionId)

 

ReportType

ReportTypeId

int NOT NULL

Name

nvarchar( 128 ) NOT NULL

DisplayIcon

nvarchar( 128 )

DisplayColor

nvarchar( 128 )

Indexes

PK_ReportType_ID primary key

ON ReportTypeId

UX_ReportType_UniqueName unique

ON Name

________________

Enterprise 2017.4.0