Skip to content
English
❔ Sign in
Log into Projectworks
  • There are no suggestions because the search field is empty.

SQL Views schema

Projectworks has an extensive set of SQL views available for reporting building. Connect PowerBI, Tableau, Excel - or anything that uses SQL.

We are working on documenting each of the views that are available, including a description of what they contain. Until then, below is a list of dimensions, facts and views that are available. 

Dimensions

Dimensions provide the “who, what, where, when, why, and how” context surrounding a business process event. Dimension tables contain the descriptive attributes used for filtering and grouping the facts.

With the grain of a fact table in mind, all the possible dimensions can be identified. Whenever possible, a dimension will be single-valued when associated with a given fact row.

Dimension tables contain the entry points and descriptive labels for business analysis using the custom reports.


Facts

Facts are the measurements that result from a business process event and are almost always numeric. A single fact table row has a one-to-one relationship to a measurement event as described by the fact table’s grain. Thus a fact table corresponds to a physical observable event, and not to the demands of a particular report. Within a fact table, only facts consistent with the declared grain are allowed.

For example, in a retail sales transaction, the quantity of a product sold and its extended price are good facts, whereas the store manager’s salary is not.


List of Views

BI.DimAccountingSystemOrganisationAccountingCodes

id name type length
1 AccountingSystemOrganisationAccountingCodeID bigint 8
2 AccountingSystemOrganisationID int 4
3 AccountingSystem nvarchar MAX
4 AccountingSystemReferenceName nvarchar MAX
5 GLCodeID int 4
6 GLCode nvarchar MAX
7 GLCodeDescription nvarchar MAX
8 GLCodeStatus varchar 8
9 GLCodeDefault varchar 11
10 GLCodeType nvarchar MAX
11 AccountingSystemReference nvarchar MAX

BI.DimAccountingSystemOrganisationOfficeAccountingCodes

id name type length
1 AccountingSystemOrganisationOfficeAccountingCodeID bigint 8
2 AccountingSystemOrganisationAccountingCodeID bigint 8
3 AccountingSystemOrganisationID int 4
4 AccountingSystem nvarchar MAX
5 AccountingSystemReferenceName nvarchar MAX
6 GLCodeID int 4
7 GLCode nvarchar MAX
8 GLCodeDescription nvarchar MAX
9 GLCodeStatus varchar 8
10 GLCodeDefault varchar 11
11 GLCodeType nvarchar MAX
12 AccountingSystemReference nvarchar MAX
13 OfficeID int 4
14 OfficeName nvarchar MAX
15 ReferenceField varchar 10
16 BlankLineType varchar 14

BI.DimAccountManager

id name type length
1 AccountManagerUserID int 4
2 AccountManager nvarchar MAX

BI.DimAgreement

id name type length
1 AgreementID int 4
2 Name nvarchar MAX
3 AgreementTypeID int 4

BI.DimBillingContact

id name type length
1 BillingContactID int 4
2 ClientID int 4
3 ContactName nvarchar MAX
4 ContactEmail nvarchar MAX
5 PostalAddress1 nvarchar MAX
6 PostalAddress2 nvarchar MAX
7 PostalAddress3 nvarchar MAX
8 CityOrState nvarchar MAX
9 Postcode nvarchar MAX
10 Country nvarchar MAX
11 ExternalReference nvarchar MAX
12 IsActive bit 1

BI.DimBudgetType

id name type length
1 BudgetTypeID int 4
2 Name nvarchar MAX
3 Active bit 1

BI.DimCertification

id name type length
1 CertificationID int 4
2 CertificationCategoryID int 4
3 CertificationCode nvarchar MAX
4 CertificationName nvarchar MAX
5 Expired bit 1
6 Active bit 1

BI.DimCertificationCategory

id name type length
1 CertificationCategoryID int 4
2 CertificationCategory nvarchar MAX
3 Active bit 1

BI.DimClient

id name type length
1 ClientID int 4
2 ClientName nvarchar MAX
3 OfficeID int 4
4 AccountManagerUserID int 4
5 ClientTypeID int 4
6 DefaultRateCardID int 4
7 FinanceEmail nvarchar MAX
8 FinancePhone nvarchar MAX
9 FinanceNotes nvarchar MAX
10 FinancePaymentTermTypeID int 4
11 PaymentTermDays int 4
12 ExternalReference nvarchar MAX
13 IsActive bit 1

BI.DimClientOffice

id name type length
1 ClientOfficeID int 4
2 OfficeName nvarchar MAX

BI.DimCompetency

id name type length
1 CompetencyID int 4
2 CompetencyName nvarchar MAX
3 Description nvarchar MAX

BI.DimContractorUserTaskCostRate

id name type length
1 ClientID int 4
2 ProjectID int 4
3 ModuleID int 4
4 TaskID int 4
5 UserID int 4
6 IsActive bit 1
7 HoursAllocated decimal 5
8 BillableRateCardID int 4
9 BillableRate decimal 9
10 CostRateCardID int 4
11 CostRate decimal 9
12 CostRateSourceID int 4
13 CostRateCurrencyID int 4
14 CostRateCurrencyCode nvarchar 6

BI.DimCostCategory

id name type length
1 CostCategoryID int 4
2 Name nvarchar MAX
3 IsActive bit 1

BI.DimCostFrequency

id name type length
1 CostFrequencyID int 4
2 Name nvarchar MAX

BI.DimCostRateCard

id name type length
1 CostRateCardID int 4
2 CurrencyID int 4
3 CardName nvarchar 400
4 IsActive bit 1
5 IsDailyRate bit 1
6 HoursPerDay decimal 5
7 HourlyRate decimal 9
8 DailyRate decimal 9
9 Rate decimal 9

BI.DimCostType

id name type length
1 CostTypeID int 4
2 Name nvarchar MAX
3 CostCategoryID int 4
4 IsGrossMargin bit 1
5 IsBenefit bit 1
6 IsActive bit 1

BI.DimCurrency

id name type length
1 CurrencyID int 4
2 CurrencyCode nvarchar MAX
3 CurrencyName nvarchar MAX
4 ActiveStatus varchar 8
5 AvailableStatus varchar 13

BI.DimDate

id name type length
1 Date date 3
2 Year int 4
3 Month int 4
4 MonthName nvarchar 60
5 Day int 4
6 MonthStart date 3
7 MonthEnd date 3
8 YearStart date 3
9 YearEnd date 3

BI.DimDateExtended

id name type length
1 Date date 3
2 Day int 4
3 DayOfYear nvarchar 60
4 DayOrdinal varchar 4
5 DayName nvarchar 60
6 IsWeekend varchar 7
7 IsWeekendFlag bit 1
8 DayState varchar 11
9 Week int 4
10 FirstOfWeek date 3
11 LastOfWeek date 3
12 DayOfWeek tinyint 1
13 DaysRemainingInWeek int 4
14 DaysPerWeek int 4
15 WeekOfMonth tinyint 1
16 WeeksInYear int 4
17 WeekState varchar 12
18 Month int 4
19 MonthName nvarchar 60
20 FirstOfMonth date 3
21 LastOfMonth date 3
22 DaysRemainingInMonth int 4
23 DaysInMonth int 4
24 FirstOfNextMonth date 3
25 TheLastOfNextMonth date 3
26 MonthState varchar 13
27 Quarter int 4
28 FirstOfQuarter date 3
29 LastOfQuarter date 3
30 DaysRemainingInQuarter int 4
31 DaysInQuarter int 4
32 QuarterState varchar 15
33 Year int 4
34 FirstOfYear date 3
35 LastOfYear date 3
36 DaysRemainingInYear int 4
37 DaysInYear int 4
38 YearState varchar 12
39 IsLeapYear varchar 11
40 IsLeapYearFlag bit 1
41 HoursPerDay decimal 5
42 WorkDaysPerWeek decimal 5
43 WorkHoursPerWeek decimal 9
44 FinancialWeekState varchar 12
45 FinancialMonth int 4
46 FinancialYear nvarchar MAX
47 StartOfFinancialYear date 3
48 EndOfFinancialYear date 3
49 FinancialDayOfYear bigint 8
50 DaysRemainingInFinancialYear bigint 8
51 DaysInFinancialYear int 4

BI.DimExpenseClaimStatus

id name type length
1 ExpenseClaimStatusID int 4
2 Status nvarchar MAX

BI.DimExpenseClaimType

id name type length
1 ExpenseClaimTypeID int 4
2 Name nvarchar MAX
3 IsActive bit 1
4 GLCodeID int 4

BI.DimFeedbackType

id name type length
1 FeedbackTypeID int 4
2 Name nvarchar MAX

BI.DimFinancialYear

id name type length
1 FinancialYearID int 4
2 Name nvarchar MAX
3 StartDate datetime 8
4 EndDate datetime 8

BI.DimFinancialYearMonth

id name type length
1 MonthID int 4
2 MonthNumber int 4
3 MonthName nvarchar 60

BI.DimFX

id name type length
1 Date date 3
2 From nvarchar 6
3 To nvarchar 6
4 Rate decimal 9

BI.DimGLCode

id name type length
1 GLCodeID int 4
2 Description nvarchar MAX
3 Code nvarchar MAX
4 GLCodeStatus varchar 8
5 GLCodeDefault varchar 11
6 GLCodeType nvarchar MAX

BI.DimGLCodeType

id name type length
1 GLCodeTypeID int 4
2 Description nvarchar MAX

BI.DimHoliday

id name type length
1 HolidayID int 4
2 Name nvarchar MAX
3 ObservedDate date 3
4 ActualDate date 3

BI.DimHolidayCalendar

id name type length
1 HolidayCalendarID int 4
2 Name nvarchar MAX

BI.DimInvoiceStatus

id name type length
1 InvoiceStatusID int 4
2 StatusCode nvarchar MAX

BI.DimLeaveStatus

id name type length
1 LeaveStatusID int 4
2 LeaveStatusName nvarchar MAX

BI.DimLeaveType

id name type length
1 LeaveTypeID int 4
2 LeaveTypeName nvarchar MAX
3 LeaveCode nvarchar MAX

BI.DimLocation

id name type length
1 LocationID int 4
2 Name nvarchar MAX
3 DefaultHolidayCalendarID int 4
4 TimeZone nvarchar MAX
5 IsActive bit 1

BI.DimModule

id name type length
1 ModuleID int 4
2 ModuleName nvarchar MAX
3 Budget decimal 9
4 ProjectID int 4
5 ClientID int 4
6 GLCodeID int 4
7 BuyPrice decimal 9
8 ModuleBudgetTypeID int 4
9 ModuleBudgetFrequencyID int 4
10 IsActive bit 1
11 ExternalReference nvarchar MAX

BI.DimMonth

id name type length
1 Date date 3
2 FinancialYearID int 4
3 Year int 4
4 Month int 4
5 MonthName nvarchar 60
6 WorkingDays int 4
7 WorkingHours decimal 9

BI.DimOffice

id name type length
1 OfficeID int 4
2 OfficeName nvarchar MAX
3 FullName nvarchar MAX
4 PhoneNumber nvarchar MAX
5 Website nvarchar MAX
6 DefaultTaxTypeID int 4
7 CurrencyID int 4
8 Active bit 1

BI.DimOfficeFinancialMonthStatus

id name type length
1 OfficeID int 4
2 OfficeName nvarchar MAX
3 OfficeStatus varchar 15
4 FinancialYearID int 4
5 FinancialYear nvarchar MAX
6 FirstOfMonth date 3
7 LastOfMonth date 3
8 FirstOfFinancialYear date 3
9 LastOfFinancialYear date 3
10 InvoiceLockStatus varchar 17
11 TimesheetsLockStatus varchar 19

BI.DimPosition

id name type length
1 PositionID int 4
2 PositionName nvarchar MAX

BI.DimProject

id name type length
1 ProjectID int 4
2 ProjectName nvarchar MAX
3 ClientID int 4
4 ProjectManagerUserID int 4
5 OfficeID int 4
6 ProjectTypeID int 4
7 ProjectStatusID int 4
8 CurrencyID int 4
9 AccountManagerUserID int 4
10 ProjectSelfServiceModeID int 4
11 HolidayCalendarID int 4
12 DefaultRateCardID int 4
13 DefaultInvoicesToForecastAmounts bit 1
14 SendClientFinanceEmail bit 1
15 SendBillingContactEmail bit 1
16 Active bit 1
17 ExternalReference nvarchar MAX

BI.DimProjectManager

id name type length
1 ProjectManagerUserID int 4
2 ProjectManager nvarchar MAX

BI.DimProjectOffice

id name type length
1 ProjectOfficeID int 4
2 OfficeName nvarchar MAX

BI.DimProjectStatus

id name type length
1 ProjectStatusID int 4
2 ProjectStatus nvarchar MAX

BI.DimProjectType

id name type length
1 ProjectTypeID int 4
2 ProjectType nvarchar MAX

BI.DimRank

id name type length
1 RankID int 4
2 RankName nvarchar MAX

BI.DimResourceType

id name type length
1 ResourceTypeID int 4
2 TypeName nvarchar MAX

BI.DimRiskCategory

id name type length
1 RiskCategoryID int 4
2 RiskCategoryName nvarchar MAX

BI.DimRiskImpact

id name type length
1 RiskImpactID int 4
2 Description nvarchar MAX

BI.DimRiskLikelihood

id name type length
1 RiskLikelihoodID int 4
2 Description nvarchar MAX

BI.DimRiskProximity

id name type length
1 RiskProximityID int 4
2 Description nvarchar MAX

BI.DimRiskTreatment

id name type length
1 RiskTreatmentID int 4
2 Description nvarchar MAX

BI.DimTask

id name type length
1 TaskID int 4
2 TaskName nvarchar MAX
3 ModuleID int 4
4 ProjectID int 4
5 ClientID int 4
6 TaskTypeID int 4
7 TaskStatusID int 4
8 StartDate datetime 8
9 EndDate datetime 8
10 PercentComplete int 4
11 IsTimesheetTask bit 1
12 IsEnforceTimesheetDates bit 1
13 CommentRequired bit 1
14 IsScheduledTask bit 1
15 IsAutoSchedule bit 1
16 IsUseDefaultRate bit 1
17 DefaultRate decimal 9
18 ExternalReference nvarchar MAX

BI.DimTaskStatus

id name type length
1 TaskStatusID int 4
2 TaskStatusName nvarchar MAX

BI.DimTaskType

id name type length
1 TaskTypeID int 4
2 TaskTypeName nvarchar MAX

BI.DimTaxType

id name type length
1 TaxTypeID int 4
2 CurrencyID int 4
3 CurrencyCode nvarchar MAX
4 TaxName nvarchar MAX
5 Jurisdiction nvarchar MAX
6 TaxRate decimal 9
7 TaxPercentage decimal 9
8 IsActive bit 1
9 ExcludeTaxFromCost bit 1

BI.DimTeam

id name type length
1 TeamID int 4
2 TeamName nvarchar MAX

BI.DimUser

id name type length
1 UserID int 4
2 Name nvarchar MAX
3 FirstName nvarchar MAX
4 LastName nvarchar MAX
5 Email nvarchar 512
6 Active bit 1

BI.DimUserOffice

id name type length
1 UserOfficeID int 4
2 OfficeName nvarchar MAX

BI.DimUserPostingDay

id name type length
1 Date date 3
2 UserID int 4
3 UserName nvarchar MAX
4 StartDate date 3
5 EndDate date 3
6 OfficeID int 4
7 OfficeName nvarchar MAX
8 LocationID int 4
9 LocationName nvarchar MAX
10 TeamID int 4
11 TeamName nvarchar MAX
12 PositionID int 4
13 PositionName nvarchar MAX
14 RankID int 4
15 RankName nvarchar MAX
16 ReportsToUserID int 4
17 ReportsToName nvarchar MAX
18 IsBillable bit 1
19 Recoverable decimal 9
20 Rate decimal 9
21 AgreementTypeID int 4
22 AgreementType nvarchar MAX
23 HolidayCalendarID int 4
24 HolidayCalendar nvarchar MAX
25 WorkWeekID int 4
26 CurrencyID int 4
27 CurrencyCode nvarchar MAX
28 WorkWeekHours decimal 17
29 DaysInPosting bigint 8

BI.DimUserTask

id name type length
1 ClientID int 4
2 ProjectID int 4
3 ModuleID int 4
4 TaskID int 4
5 UserID int 4
6 HoursAllocated decimal 5
7 RateCardID int 4
8 Rate decimal 9
9 IsActive bit 1

BI.DimWeek

id name type length
1 WeekStart date 3
2 WeekEnd date 3
3 FinancialYearID int 4
4 Year int 4
5 Month int 4
6 WeekState varchar 8

BI.FactContractorUserTaskCostValue

id name type length
1 ClientID int 4
2 ProjectID int 4
3 ModuleID int 4
4 TaskID int 4
5 UserID int 4
6 ClientName nvarchar MAX
7 ProjectName nvarchar MAX
8 ModuleName nvarchar MAX
9 TaskName nvarchar MAX
10 UserName nvarchar MAX
11 TaskIsActive bit 1
12 HoursAllocated decimal 5
13 BillableRateCardID int 4
14 BillableRate decimal 9
15 CostRateCardID int 4
16 CostRateCardName nvarchar 400
17 StampedCostRate decimal 9
18 CurrentCostRate decimal 9
19 StampedCostValue decimal 9
20 CurrentCostValue decimal 9

BI.FactDailyCapacity

id name type length
1 UserID int 4
2 OfficeID int 4
3 LocationID int 4
4 TeamID int 4
5 IsBillable bit 1
6 AgreementTypeID int 4
7 AgreementType nvarchar MAX
8 MonthStart date 3
9 WeekStart date 3
10 Date date 3
11 DayOfWeekID int 4
12 Hours decimal 5
13 WorkHours decimal 17
14 LeaveHours decimal 17
15 DailyFTE decimal 9

BI.FactDailyCapacityV2

id name type length
1 UserID int 4
2 OfficeID int 4
3 LocationID int 4
4 TeamID int 4
5 IsBillable bit 1
6 AgreementTypeID int 4
7 AgreementType nvarchar MAX
8 MonthStart date 3
9 WeekStart date 3
10 Date date 3
11 DayOfWeekID int 4
12 Hours decimal 5
13 WorkHours decimal 17
14 LeaveHours decimal 17
15 DailyFTE decimal 9

BI.FactDailyResourced

id name type length
1 Date date 3
2 ResourceID int 4
3 ProjectOfficeID int 4
4 ClientID int 4
5 ProjectID int 4
6 AccountManagerUserID int 4
7 ProjectManagerUserID int 4
8 ProjectTypeID int 4
9 ProjectStatusID int 4
10 ResourceTypeID int 4
11 LineID uniqueidentifier 16
12 UserID int 4
13 IsPenciled bit 1
14 OfficeID int 4
15 TeamID int 4
16 PositionID int 4
17 RankID int 4
18 HourRate decimal 13
19 DayHours decimal 9
20 DayRate decimal 17

BI.FactExpenseClaim

id name type length
1 ExpenseClaimID int 4
2 UserID int 4
3 ProjectID int 4
4 ModuleID int 4
5 TypeID int 4
6 StatusID int 4
7 Date date 3
8 Amount decimal 9
9 CurrencyID int 4
10 PurchaseCurrencyCode nvarchar MAX
11 IsProcessed bit 1
12 IsReimbursable bit 1
13 IncludeTaxInBudget bit 1
14 TaxTypeID int 4
15 IsBillable bit 1
16 MarginAmount decimal 9
17 MarginPercent decimal 9
18 BillableAmount decimal 9
19 BillableCurrencyCode nvarchar MAX
20 InvoiceLineID int 4
21 Notes nvarchar 2000
22 InvoiceDescription nvarchar 512
23 Response nvarchar 2000
24 CreatedBy nvarchar 512
25 CreatedDate datetime 8
26 UpdatedBy nvarchar 512
27 UpdatedDate datetime 8

BI.FactHolidays

id name type length
1 HolidayCalendarID int 4
2 HolidayCalendarName nvarchar MAX
3 HolidayID int 4
4 HolidayName nvarchar MAX
5 ObservedDate date 3
6 ActualDate date 3

BI.FactHourlyBenefit

id name type length
1 UserID int 4
2 CurrencyID int 4
3 CurrencyCode nvarchar MAX
4 Date date 3
5 WorkHours int 4
6 HourlyCost decimal 17

BI.FactHourlyCost

id name type length
1 UserID int 4
2 CurrencyID int 4
3 CurrencyCode nvarchar MAX
4 Date date 3
5 WorkHours int 4
6 HourlyCost decimal 17

BI.FactInvoiceLineExpenseClaim

id name type length
1 ClientID int 4
2 ProjectID int 4
3 ProjectManagerUserID int 4
4 OfficeID int 4
5 ModuleID int 4
6 InvoiceID int 4
7 InvoiceNumber nvarchar MAX
8 InvoiceDate date 3
9 FinancialYearID int 4
10 DueDate date 3
11 InvoiceStatusID int 4
12 GLCodeID int 4
13 GLCodeTypeID int 4
14 ProjectCurrencyID int 4
15 BillableCurrencyID int 4
16 ExpenseCurrencyID int 4
17 PurchaseCurrencyID int 4
18 BillingContactID int 4
19 PONumber nvarchar MAX
20 ExternalReference nvarchar MAX
21 ExpenseClaimID int 4
22 ExpenseClaimTypeID int 4
23 ExpenseClaimStatusID int 4
24 TaxTypeID int 4
25 LineTypeID int 4
26 ExpenseDate date 3
27 UserID int 4
28 PurchasePrice decimal 9
29 BillableAmount decimal 9
30 MarginAmount decimal 9
31 MarginPercent decimal 9
32 InvoiceLineDescription nvarchar MAX
33 TaxAmount numeric 17
34 InvoicedAmountTaxInclusive numeric 17
35 IsProcessed varchar 3
36 IsReimbursable varchar 3
37 IncludeTaxInBudget varchar 3
38 IsBillable varchar 3
39 Notes nvarchar 2000
40 Response nvarchar 2000

BI.FactInvoiceLines

id name type length
1 ClientID int 4
2 ProjectID int 4
3 OfficeID int 4
4 ModuleID int 4
5 InvoiceID int 4
6 InvoiceNumber nvarchar MAX
7 InvoiceDate date 3
8 DueDate date 3
9 InvoiceStatusID int 4
10 StatusCode nvarchar MAX
11 GLCodeID int 4
12 GLCodeTypeID int 4
13 GLCode nvarchar MAX
14 CurrencyID int 4
15 CurrencyCode nvarchar MAX
16 InvoiceLineAmount decimal 9
17 BuyPrice decimal 9
18 PONumber nvarchar MAX
19 ExternalReference nvarchar MAX
20 BillingContactID int 4
21 TaxTypeID int 4
22 LineTypeID int 4
23 ProjectManagerUserID int 4
24 InvoiceLineDescription nvarchar MAX
25 InvoiceDescription nvarchar MAX

BI.FactInvoiceLineUserTask

id name type length
1 ClientID int 4
2 ProjectID int 4
3 ProjectManagerUserID int 4
4 OfficeID int 4
5 ModuleID int 4
6 InvoiceID int 4
7 InvoiceNumber nvarchar MAX
8 InvoiceDate date 3
9 FinancialYearID int 4
10 DueDate date 3
11 InvoiceStatusID int 4
12 GLCodeID int 4
13 GLCodeTypeID int 4
14 CurrencyID int 4
15 BillingContactID int 4
16 PONumber nvarchar MAX
17 ExternalReference nvarchar MAX
18 UserID int 4
19 TaskID int 4
20 InvoicedAmount decimal 9
21 TaxTypeID int 4
22 LineTypeID int 4
23 InvoiceLineDescription nvarchar MAX
24 TaxAmount numeric 17
25 InvoicedAmountTaxInclusive numeric 17
26 TaskMinutes int 4
27 TaskHours numeric 13

BI.FactInvoices

id name type length
1 ClientID int 4
2 ProjectID int 4
3 OfficeID int 4
4 InvoiceID int 4
5 InvoiceNumber nvarchar MAX
6 Description nvarchar MAX
7 InvoiceDate date 3
8 DueDate date 3
9 InvoiceStatusID int 4
10 StatusCode nvarchar MAX
11 CurrencyID int 4
12 CurrencyCode nvarchar MAX
13 InvoicedAmount decimal 17
14 TaxAmount numeric 17
15 AmountPaid decimal 17
16 InvoicePaid int 4

BI.FactMonthlyAccruals

id name type length
1 Date date 3
2 FinancialYearID int 4
3 ModuleID int 4
4 GLCodeID int 4
5 GLCodeTypeID int 4
6 ProjectID int 4
7 ClientID int 4
8 ProjectOfficeID int 4
9 AccountManagerUserID int 4
10 ProjectManagerUserID int 4
11 ProjectTypeID int 4
12 ProjectStatusID int 4
13 CurrencyID int 4
14 CurrencyCode nvarchar MAX
15 Amount decimal 13

BI.FactMonthlyBudgets

id name type length
1 Date date 3
2 FinancialYearID int 4
3 BudgetTypeID int 4
4 GLCodeID int 4
5 GLCodeTypeID int 4
6 BudgetAmount decimal 9
7 CurrencyID int 4
8 CurrencyCode nvarchar MAX

BI.FactMonthlyCapacity

id name type length
1 Date date 3
2 OfficeID int 4
3 LocationID int 4
4 TeamID int 4
5 UserID int 4
6 WorkWeekID int 4
7 HolidayCalendarID int 4
8 Recoverable decimal 9
9 AgreementTypeID int 4
10 PositionID int 4
11 RankID int 4
12 IsBillable bit 1
13 Rate decimal 9
14 CurrencyID int 4
15 CapacityHours decimal 17

BI.FactMonthlyForecasts

id name type length
1 Date date 3
2 FinancialYearID int 4
3 ProjectOfficeID int 4
4 ClientID int 4
5 ProjectID int 4
6 AccountManagerUserID int 4
7 ProjectManagerUserID int 4
8 ProjectTypeID int 4
9 ProjectStatusID int 4
10 ModuleID int 4
11 GLCodeID int 4
12 GLCodeTypeID int 4
13 ForecastAmount decimal 9
14 CurrencyID int 4
15 CurrencyCode nvarchar MAX

BI.FactMonthlyInvoiced

id name type length
1 Date date 3
2 FinancialYearID int 4
3 ProjectOfficeID int 4
4 ClientID int 4
5 ProjectID int 4
6 ModuleID int 4
7 AccountManagerUserID int 4
8 ProjectManagerUserID int 4
9 ProjectTypeID int 4
10 ProjectStatusID int 4
11 GLCodeID int 4
12 GLCodeTypeID int 4
13 CurrencyID int 4
14 CurrencyCode nvarchar MAX
15 Amount decimal 17

BI.FactMonthlyInvoicedPaid

id name type length
1 Date date 3
2 ProjectOfficeID int 4
3 ClientID int 4
4 ProjectID int 4
5 AccountManagerUserID int 4
6 ProjectManagerUserID int 4
7 ProjectTypeID int 4
8 ProjectStatusID int 4
9 CurrencyID int 4
10 CurrencyCode nvarchar MAX
11 PaidAmount decimal 17

BI.FactMonthlyLeave

id name type length
1 Date date 3
2 OfficeID int 4
3 TeamID int 4
4 UserID int 4
5 LeaveTypeID int 4
6 TypeCode nvarchar MAX
7 LeaveStatusID int 4
8 Hours decimal 17

BI.FactMonthlyNetRevenue

id name type length
1 Date date 3
2 FinancialYearID int 4
3 ModuleID int 4
4 GLCodeID int 4
5 GLCodeTypeID int 4
6 ProjectID int 4
7 ClientID int 4
8 ProjectOfficeID int 4
9 AccountManagerUserID int 4
10 ProjectManagerUserID int 4
11 ProjectTypeID int 4
12 ProjectStatusID int 4
13 CurrencyID int 4
14 CurrencyCode nvarchar MAX
15 Amount decimal 17

BI.FactMonthlyResourced

id name type length
1 Date date 3
2 ProjectOfficeID int 4
3 ClientID int 4
4 ProjectID int 4
5 AccountManagerUserID int 4
6 ProjectManagerUserID int 4
7 ProjectTypeID int 4
8 ProjectStatusID int 4
9 ResourceTypeID int 4
10 LineID uniqueidentifier 16
11 UserID int 4
12 IsPenciled bit 1
13 OfficeID int 4
14 TeamID int 4
15 PositionID int 4
16 RankID int 4
17 HourRate decimal 13
18 MonthHours decimal 17
19 MonthRate decimal 17

BI.FactMonthlyUserFTE

id name type length
1 Date date 3
2 UserID int 4
3 CapacityHours decimal 17
4 IsBillable bit 1
5 OfficeID int 4
6 LocationID int 4
7 WorkingHours decimal 9
8 AgreementTypeID int 4
9 FTE numeric 17

BI.FactMonthlyUserFTEv2

id name type length
1 Date date 3
2 UserID int 4
3 CapacityHours decimal 17
4 IsBillable bit 1
5 OfficeID int 4
6 TeamID int 4
7 LocationID int 4
8 WorkingHours decimal 9
9 AgreementTypeID int 4
10 FTE numeric 17

BI.FactMonthlyWorked

id name type length
1 Date date 3
2 FinancialYearID int 4
3 ProjectOfficeID int 4
4 ClientID int 4
5 ClientOfficeID int 4
6 ProjectID int 4
7 AccountManagerUserID int 4
8 ProjectManagerUserID int 4
9 ProjectTypeID int 4
10 ProjectStatusID int 4
11 ModuleID int 4
12 GLCodeID int 4
13 RateCardID int 4
14 Rate decimal 9
15 UserID int 4
16 UserTeamID int 4
17 UserOfficeID int 4
18 UserLocationID int 4
19 AgreementID int 4
20 HoursWorked numeric 17
21 EffortWorked numeric 17

BI.FactProject

id name type length
1 OfficeID int 4
2 OfficeName nvarchar MAX
3 ClientID int 4
4 ClientName nvarchar MAX
5 ProjectID int 4
6 ProjectName nvarchar MAX
7 ProjectStatusID int 4
8 ProjectStatusCode nvarchar MAX
9 ProjectTypeID int 4
10 ProjectTypeCode nvarchar MAX
11 ProjectManagerUserID int 4
12 ProjectManager nvarchar MAX
13 AccountManagerUserID int 4
14 AccountManager nvarchar MAX
15 CurrencyID int 4

BI.FactProjectBudgets

id name type length
1 OfficeID int 4
2 ClientID int 4
3 ProjectID int 4
4 ProjectTypeID int 4
5 ProjectStatusID int 4
6 ModuleID int 4
7 ModuleName nvarchar MAX
8 Budget decimal 9
9 GLCodeID int 4

BI.FactProjectBurn

id name type length
1 ProjectID int 4
2 ClientName nvarchar MAX
3 ProjectName nvarchar MAX
4 ProjectManagerUserID int 4
5 ProjectManager nvarchar MAX
6 Budget decimal 17
7 EffortWorked numeric 17
8 TimesheetInvoicedAmount decimal 17
9 LastTimesheet date 3
10 Burn numeric 17
11 OverrideAmount decimal 19,4

BI.FactProjectInvoiceLines

id name type length
1 InvoiceID int 4
2 InvoiceNumber nvarchar MAX
3 InvoiceDate date 3
4 DueDate date 3
5 StatusCode nvarchar MAX
6 OfficeID int 4
7 ClientID int 4
8 ProjectID int 4
9 AccountManagerUserID int 4
10 ProjectManagerUserID int 4
11 ProjectTypeID int 4
12 ProjectStatusID int 4
13 InvoiceLineAmount decimal 9
14 BuyPrice decimal 9
15 CurrencyCode nvarchar MAX
16 CurrencyID int 4
17 ModuleID int 4
18 ModuleName nvarchar MAX
19 GLCodeID int 4
20 GLCodeTypeID int 4
21 Description nvarchar MAX
22 InternalComment nvarchar MAX
23 PONumber nvarchar MAX

BI.FactProjectRisks

id name type length
1 OfficeID int 4
2 ClientID int 4
3 ProjectID int 4
4 AccountManagerUserID int 4
5 ProjectManagerUserID int 4
6 ProjectTypeID int 4
7 ProjectStatusID int 4
8 RiskCategoryID int 4
9 RiskLikelihoodID int 4
10 RiskImpactID int 4
11 RiskTreatmentID int 4
12 RiskProximityID int 4
13 Details nvarchar MAX
14 Mitigation nvarchar MAX
15 RiskOwner nvarchar MAX

BI.FactProjectTasks

id name type length
1 ProjectOfficeID int 4
2 ClientID int 4
3 ProjectID int 4
4 AccountManagerUserID int 4
5 ProjectManagerUserID int 4
6 ProjectTypeID int 4
7 ProjectStatusID int 4
8 ModuleID int 4
9 ModuleName nvarchar MAX
10 TaskID int 4
11 TaskTypeID int 4
12 TaskStatusID int 4
13 TaskName nvarchar MAX
14 CurrencyCode nvarchar MAX
15 CurrencyID int 4
16 Task Hours decimal 17
17 IsTimesheetTask bit 1
18 IsScheduledTask bit 1
19 PercentComplete int 4
20 StartDate datetime 8
21 EndDate datetime 8

BI.FactProjectTimesheets

id name type length
1 Date date 3
2 FinancialYearID int 4
3 MonthStart date 3
4 WeekStart date 3
5 WeekState varchar 8
6 ProjectOfficeID int 4
7 ClientID int 4
8 ProjectID int 4
9 CurrencyID int 4
10 AccountManagerUserID int 4
11 ProjectManagerUserID int 4
12 ProjectTypeID int 4
13 ProjectStatusID int 4
14 ModuleID int 4
15 GLCodeID int 4
16 TaskID int 4
17 TaskTypeID int 4
18 ExternalIssueID nvarchar 255
19 IsTaskBillable bit  
20 RateCardID int 4
21 Rate decimal 9
22 UserID int 4
23 HoursWorked numeric 9
24 EffortWorked numeric 17
25 BurnRate decimal 21,6
26 BurnAmount numeric 38,11
27 Comment nvarchar 4000
28 UserOfficeID int 4
29 UserLocationID int 4
30 UserTeamID int 4
31 AgreementTypeID int 4
32 CreatedDate datetime 8
33 IsReviewed bit 1
34 ReviewedBy nvarchar 256
35 ReviewedDate datetime2 7
36 InvoiceLineID int  
37 InvoiceID int  
38 InvoiceNumber nvarchar 100
39 InvoicedRate decimal 19,4
40 LockedRate decimal 19,4
41 HoursInvoiced numeric 9
42 InvoicedAmount decimal 9
43 UserTaskHoursID int  
44 TimesheetStatusID int  
45 TimesheetStatus nvarchar 9
46 OverrideType varchar 9
47 OverrideAmount decimal 19,4
48 OverrideBy nvarchar 256
49 OverrideDate datetime  

BI.FactResourceLog

id name type length
1 ProjectID int 4
2 Date date 3
3 UserID int 4
4 IsPenciled bit 1
5 Comment nvarchar MAX
6 Hours decimal 5
7 OfficeID int 4
8 LocationID int 4
9 TeamID int 4
10 PositionID int 4
11 RankID int 4
12 PostingRate decimal 9
13 ChangedBy nvarchar MAX
14 ChangedDate datetime 8
15 RevenueRate decimal 13

BI.FactUserActivity

id name type length
1 OfficeID int 4
2 LocationID int 4
3 TeamID int 4
4 IsBillable bit 1
5 AgreementTypeID int 4
6 AgreementType nvarchar MAX
7 UserID int 4
8 UserName nvarchar MAX
9 FinancialYearID int 4
10 MonthStart date 3
11 WeekState varchar 8
12 Date date 3
13 ActivityType varchar 12
14 ClientID int 4
15 ClientName nvarchar MAX
16 ProjectID int 4
17 ProjectName nvarchar MAX
18 ModuleID int 4
19 ModuleName nvarchar MAX
20 TaskID int 4
21 TaskName nvarchar MAX
22 Rate decimal 9
23 InvoicedRate decimal 9
24 LockedRate decimal 19,4
25 Hours decimal 13
26 Comment nvarchar 4000
27 IsReviewed int 4
28 Paid bit  
29 OverrideType varchar 9

BI.FactUserActivityWithHolidays

id name type length
1 OfficeID int 4
2 LocationID int 4
3 TeamID int 4
4 IsBillable bit 1
5 AgreementTypeID int 4
6 AgreementType nvarchar MAX
7 UserID int 4
8 UserName nvarchar MAX
9 FinancialYearID int 4
10 MonthStart date 3
11 WeekState varchar 8
12 Date date 3
13 ActivityType varchar 12
14 ClientID int 4
15 ClientName nvarchar MAX
16 ProjectID int 4
17 ProjectName nvarchar MAX
18 ModuleID int 4
19 ModuleName nvarchar MAX
20 TaskID int 4
21 TaskName nvarchar MAX
22 Rate decimal 9
23 InvoicedRate decimal 9
24 Hours decimal 13
25 Comment nvarchar 4000
26 IsReviewed int 4
27 OverrideType varchar 9

BI.FactUserCertifications

id name type length
1 UserID int 4
2 CertificationID int 4
3 CertificationCategoryID int 4
4 Passed bit 1
5 CertificationDate date 3
6 Contractual bit 1
7 Bonded bit 1
8 BondDate date 3
9 CurrentOfficeID int 4
10 CurrentTeamID int 4
11 CurrentPositionID int 4
12 CurrentRankID int 4

BI.FactUserCost

id name type length
1 UserCostID int 4
2 UserID int 4
3 CurrencyID int 4
4 CostTypeID int 4
5 CostFrequencyID int 4
6 Amount decimal 9
7 IsActive bit 1
8 IsGrossMargin bit 1
9 IsBenefit bit 1
10 OfficeID int 4
11 FinancialYearID int 4
12 WorkHours int 4
13 CostPerHour decimal 13
14 StartDate datetime 8
15 EndDate datetime 8

BI.FactUserCostByDay

id name type length
1 UserID int 4
2 CurrencyID int 4
3 Date date 3
4 CostPerHour decimal 17

BI.FactUserCourses

id name type length
1 UserID int 4
2 CourseID int 4
3 CourseName nvarchar MAX
4 Location nvarchar MAX
5 AttendanceDate date 3
6 ExpiryDate date 3
7 Bonded bit 1
8 BondDate date 3
9 Notes nvarchar MAX
10 CurrentOfficeID int 4
11 CurrentTeamID int 4
12 CurrentPositionID int 4
13 CurrentRankID int 4

BI.FactUserFeedback

id name type length
1 FeedbackID int 4
2 UserID int 4
3 FromUserID int 4
4 Message nvarchar MAX
5 IsAnonymous bit 1
6 FeedbackTypeID int 4
7 FeedbackDate date 3
8 CurrentOfficeID int 4
9 CurrentTeamID int 4
10 CurrentPositionID int 4
11 CurrentRankID int 4

BI.FactUserLeaveLines

id name type length
1 UserID int 4
2 LeaveID int 4
3 LeaveStatusID int 4
4 RequestComment nvarchar 2000
5 Date date 3
6 Hours decimal 9
7 LeaveTypeID int 4
8 CurrentOfficeID int 4
9 CurrentLocationID int 4
10 CurrentTeamID int 4
11 CurrentPositionID int 4
12 CurrentRankID int 4
13 CurrentAgreementTypeID int 4
14 OfficeID int 4
15 LocationID int 4
16 TeamID int 4
17 PositionID int 4
18 RankID int 4
19 AgreementTypeID int 4

BI.FactUserObjectives

id name type length
1 UserID int 4
2 ReportsToUserID int 4
3 PerformancePeriod nvarchar MAX
4 ObjectiveTypeName nvarchar MAX
5 ResultTypeName nvarchar MAX
6 CompetencyName nvarchar MAX
7 EmployeeRating int 4
8 ManagerRating int 4
9 EmployeeComment nvarchar MAX
10 ManagerComment nvarchar MAX
11 Objective nvarchar MAX
12 Action nvarchar MAX
13 Measure nvarchar MAX
14 DueDate date 3
15 CurrentOfficeID int 4
16 CurrentTeamID int 4
17 CurrentPositionID int 4
18 CurrentRankID int 4

BI.FactUserPostings

id name type length
1 UserID int 4
2 StartDate date 3
3 EndDate date 3
4 OfficeID int 4
5 LocationID int 4
6 TeamID int 4
7 PositionID int 4
8 RankID int 4
9 ReportsToUserID int 4
10 IsBillable bit 1
11 Recoverable decimal 9
12 Rate decimal 9
13 AgreementTypeID int 4
14 HolidayCalendarID int 4
15 WorkWeekID int 4
16 CurrencyID int 4
17 WorkWeekHours decimal 17

BI.FactUserResourced

id name type length
1 UserID int 4
2 OfficeID int 4
3 LocationID int 4
4 TeamID int 4
5 IsBillable bit 1
6 AgreementTypeID int 4
7 AgreementType nvarchar MAX
8 PersonName nvarchar MAX
9 ActivityType varchar 12
10 ClientID int 4
11 ClientName nvarchar MAX
12 ProjectID int 4
13 ProjectName nvarchar MAX
14 WeekState varchar 8
15 Date date 3
16 MonthStart date 3
17 Hours decimal 17
18 RackRate decimal 9

BI.FactUserResourcedV2

id name type length
1 UserID int 4
2 OfficeID int 4
3 LocationID int 4
4 TeamID int 4
5 IsBillable bit 1
6 AgreementTypeID int 4
7 AgreementType nvarchar MAX
8 PersonName nvarchar MAX
9 ActivityType varchar 12
10 ClientID int 4
11 ClientName nvarchar MAX
12 ProjectID int 4
13 ProjectName nvarchar MAX
14 WeekState varchar 8
15 Date date 3
16 MonthStart date 3
17 Hours decimal 17
18 RackRate decimal 9
19 ProjectTaskRate decimal 9
20 ProjectRateCardRate decimal 9
21 CurrencyRateCardRate decimal 9

BI.FactUsers

id name type length
1 UserID int 4
2 Name nvarchar MAX
3 FirstName nvarchar MAX
4 LastName nvarchar MAX
5 Email nvarchar 512
6 DOB date 3
7 EmployeeStartDate datetime 8
8 EmployeeEndDate datetime 8
9 Active bit 1
10 Gender nvarchar MAX
11 JobTitle nvarchar MAX
12 ExternalReference nvarchar MAX
13 CurrentOfficeID int 4
14 CurrentLocationID int 4
15 CurrentTeamID int 4
16 CurrentPositionID int 4
17 CurrentAgreementTypeID int 4
18 CurrentRankID int 4
19 CurrentReportsToUserID int 4
20 CurrentRackRate decimal 9

BI.FactUserTarget

id name type length
1 UserID int 4
2 OfficeID int 4
3 LocationID int 4
4 TeamID int 4
5 IsBillable bit 1
6 AgreementTypeID int 4
7 AgreementType nvarchar MAX
8 StartDate date 3
9 EndDate date 3
10 Date date 3
11 MonthStart date 3
12 DayOfWeekID int 4
13 Hours decimal 5
14 TargetBillable numeric 13
15 TargetHours numeric 17

BI.FactWeeklyCapacity

id name type length
1 WeekStart date 3
2 FinancialYearID int 4
3 WeekState varchar 8
4 UserID int 4
5 TotalResourceHours decimal 17
6 TotalHolidayHours decimal 17
7 TotalCapacityHours decimal 17
8 AvailableHours decimal 17
9 OfficeID int 4
10 LocationID int 4
11 TeamID int 4
12 PositionID int 4
13 RankID int 4
14 AgreementTypeID int 4

BI.FactWeeklyResourced

id name type length
1 Date date 3
2 FinancialYearID int 4
3 ResourceID int 4
4 MonthStart date 3
5 WeekState varchar 8
6 ProjectOfficeID int 4
7 ClientID int 4
8 ProjectID int 4
9 AccountManagerUserID int 4
10 ProjectManagerUserID int 4
11 ProjectTypeID int 4
12 ProjectStatusID int 4
13 ResourceTypeID int 4
14 LineID uniqueidentifier 16
15 UserID int 4
16 IsPenciled bit 1
17 Hours decimal 5
18 OfficeID int 4
19 TeamID int 4
20 PositionID int 4
21 RankID int 4
22 Rate decimal 13
23 TotalCapacityHours decimal 17
24 TotalHolidayHours decimal 17
25 TotalResourceHours decimal 17