Custom Power BI Building: SQL Views List

There are several views that can be used for data analytics. This article will list those views, their intent, and the fields and descriptions contained within. 

We highly recommend that you use these views and not the raw tables since these views will be maintained as the database structure changes.

NOTE: If you have archive data from prior to your implementation of Estimator, scroll to the bottom of this article for a file that can help you map your old data to the Estimator views for use in Power BI.

ProjectSummaryDataDetails
Intent: High level summary of each project, with project costs separated by Base Estimate and Accepted Alternates. Client and Project information are pulled from the Estimate Properties window within Estimator.
Column Name	Type	Description
EstimateKey	GUID	The unique identifier for the estimate
EstimateName	String	The name of the estimate.
VersionName	String	The name of the version
ClientZipCode	String	The client’s postal code.
BaseEstimateTotalCost	Decimal	The total cost (direct + indirect) of the Base Estimate.
BaseEstimateDirectCost	Decimal	The direct cost of the Base Estimate.
AcceptedAlternatesTotalCost	Decimal	The total cost (direct +indirect) of the Accepted Alternates.
AcceptedAlternatesDirectCost	Decimal	The direct cost of the Accepted Alternates.
ProjectType	String	The type of the project.
EstimateStatus	String	The status of the estimate.
CreateYear	Char(4)	The year the estimate was created in DESTINI Estimator.
ProjectId	GUID	Unique identifier for the project.
BranchKey	GUID	Unique identifier for the project version.

EstimateSummaryDataDetails
Intent: High level summary of each estimate. Client and Project information are pulled from the Estimate Properties window within Estimator.
Column Name	Type	Description
EstimateKey	GUID	The unique identifier for the estimate
EstimateName	String	The name of the estimate.
VersionName	String	The name of the version
EstimateNumber	String	The job number for the estimate.
ProjectName	String	The name of the project.
ClientName	String	The client’s name.
ClientAddress	String	The client’s street address.
ClientCity	String	The client’s city location.
ClientState	String	The client’s state location.
ClientZipCode	String	The client’s postal code.
TotalCost	Decimal	The total cost (direct + indirect) of the Base Estimate.
TotalCostPerArea	Decimal	The total cost (direct + indirect) per Total Building Area of the Base Estimate.
DirectCost	Decimal	The direct cost of the Base Estimate.
DirectCostPerArea	Decimal	The direct cost per Total Building Area of the Base Estimate.
ProjectType	String	The type of the project.
EstimateStatus	String	The status of the estimate.
CreateYear	Char(4)	The year the estimate was created in DESTINI Estimator.

EstimatePropertyDataDetails
Intent: Returns all Project View properties and Estimate Properties window properties for each estimate as individual rows.
Column Name	Type	Description
EstimateName	String	The name of the estimate.
EstimateKey	GUID	The unique identifier for the estimate.
CreatedBy	String	The estimator that added the property to the estimate.
Property	String	The name of the property in the project tab.
Value	String	The text value of the project property

EstimateWBSProperties
Intent: Returns a distinct list of all WBS Property Names in all the estimates.
Column Name	Type	Description
WBSPropertyName	String	The name of the WBS property.

EstimateWBSPropertyValues/AlternateWBSPropertyValues
Intent: List of all WBS Property Values assigned to line items inside the estimate. “Estimate” View returns WBS Values for Base Estimate line items. “Alternate” View returns WBS Values for Alternate line items.
Column Name	Type	Description
EstimateKey	GUID	The unique identifier for the estimate.
WBSPropertyName	String	The name of the WBS Property.
WBSPropertyType	Int	WBS Property Type, stored as a number. 1 = Number 2 = Text 3 = DateTime 5 = Color 6 = Boolean (True/False) 7 = Price
WBSPropertyValue	String	The default value of the WBS Property for that estimate.
AlternateId (AlternateWBSPropertyValues only)	GUID	The alternate’s unique identifier

HistoricalCostData
Intent: Detailed set of line items across all estimates. Self Perform Duration Units: 1 = Weeks 2 = Days 3 = Hours 4 = Minutes 7 = Months
Column Name	Type	Description
EstimateKey	GUID	The unique identifier for the estimate.
Key	GUID	The unique identifier for the line item
Username	String	The name of the estimator that currently “owns” the record.
Name	String	The line item’s Item Number.
Description	String	The line item’s Description.
Quantity	Decimal	The line item’s quantity.
Unit	String(50)	The line item quantity’s unit of measure.
LaborPrice	Decimal	The line item’s labor unit cost.
MaterialPrice	Decimal	The line item’s material unit cost.
EquipmentPrice	Decimal	The line item’s equipment unit cost.
SubPrice	Decimal	The line item’s sub unit cost.
OtherPrice	Decimal	The line item’s other unit cost.
TotalUnitCost	Decimal	The line item’s total unit cost.
TotalCost	Decimal	The line item’s total cost.
TotalCostPerUnit	Decimal	The line item’s total cost per Unit Reference
CostPerUnitResource	String	The Project Property name for the line item’s Unit Reference.
DistributedFeeAmount	Decimal	The line item’s distributed fee amount.
DistributedEquipmentFeeAmount	Decimal	The line item’s distributed equipment fee amount.
DistributedLaborFeeAmount	Decimal	The line item’s distributed labor fee amount.
DistributedMaterialFeeAmount	Decimal	The line item’s distributed material fee amount.
DistributedOtherFeeAmount	Decimal	The line item’s distributed other fee amount.
DistributedSubFeeAmount	Decimal	The line item’s distributed sub fee amount.
CreatedBy	String	The estimator that added the line item to the estimate.
LaborResourceGroupDuration	Decimal	The line item’s labor crew duration.
LaborResourceGroupDurationUnit	Int	The line item’s labor crew duration unit.
LaborResourceDuration	Decimal	The line item’s labor resource duration.
LaborResourceDurationUnit	Int	The line item’s labor resource duration unit.
LaborResourceGroup	String	The line item’s labor resource group.
LaborUnitsPerManHour	Decimal	The line item’s labor units per man hour
QuantityPerLaborResourceUnit	Decimal	The line item’s quantity per labor resource unit.
MaterialResourceGroupDuration	Decimal	The line item’s Material crew duration.
MaterialResourceGroupDurationUnit	Int	The line item’s Material crew duration unit.
MaterialResourceDuration	Decimal	The line item’s Material resource duration.
MaterialResourceDurationUnit	Int	The line item’s Material resource duration unit.
MaterialResourceGroup	String	The line item’s Material resource group.
MaterialUnitsPerManHour	Decimal	The line item’s Material units per man hour
QuantityPerMaterialResourceUnit	Decimal	The line item’s quantity per Material resource unit.
EquipmentResourceGroupDuration	Decimal	The line item’s Equipment crew duration.
EquipmentResourceGroupDurationUnit	Int	The line item’s Equipment crew duration unit.
EquipmentResourceDuration	Decimal	The line item’s Equipment resource duration.
EquipmentResourceDurationUnit	Int	The line item’s Equipment resource duration unit.
EquipmentResourceGroup	String	The line item’s Equipment resource group.
EquipmentUnitsPerManHour	Decimal	The line item’s Equipment units per man hour
QuantityPerEquipmentResourceUnit	Decimal	The line item’s quantity per Equipment resource unit.
SubResourceGroupDuration	Decimal	The line item’s Sub crew duration.
SubResourceGroupDurationUnit	Int	The line item’s Sub crew duration unit.
SubResourceDuration	Decimal	The line item’s Sub resource duration.
SubResourceDurationUnit	Int	The line item’s Sub resource duration unit.
SubResourceGroup	String	The line item’s Sub resource group.
SubUnitsPerManHour	Decimal	The line item’s Sub units per man hour
QuantityPerSubResourceUnit	Decimal	The line item’s quantity per Sub resource unit.
OtherResourceGroupDuration	Decimal	The line item’s Other crew duration.
OtherResourceGroupDurationUnit	Int	The line item’s Other crew duration unit.
OtherResourceDuration	Decimal	The line item’s Other resource duration.
OtherResourceDurationUnit	Int	The line item’s Other resource duration unit.
OtherResourceGroup	String	The line item’s Other resource group.
OtherUnitsPerManHour	Decimal	The line item’s Other units per man hour
QuantityPerOtherResourceUnit	Decimal	The line item’s quantity per Other resource unit.
…	String	1 column per WBS Property Name

ProjectCostDataDetails
Intent: Returns the line items inside a single estimate using the stored procedure sp_GetSimpleProjectCostDataDetails. Typically used within Standard Reports.
Column Name	Type	Description
EstimateKey	GUID	The unique identifier for the estimate.
Key	GUID	The unique identifier for the line item
Username	String	The name of the estimator that currently “owns” the record.
Name	String	The line item’s Item Number.
Description	String	The line item’s Description.
Quantity	Decimal	The line item’s quantity.
Unit	String(50)	The line item quantity’s unit of measure.
LaborPrice	Decimal	The line item’s labor unit cost.
MaterialPrice	Decimal	The line item’s material unit cost.
EquipmentPrice	Decimal	The line item’s equipment unit cost.
SubPrice	Decimal	The line item’s sub unit cost.
OtherPrice	Decimal	The line item’s other unit cost.
TotalUnitCost	Decimal	The line item’s total unit cost.
TotalCost	Decimal	The line item’s total cost.
TotalCostPerUnit	Decimal	The line item’s total cost per Unit Reference
TotalCostPerArea	Decimal	The line item’s total cost per Total Building Area
CostPerUnitResource	String	The Project Property name for the line item’s Unit Reference.
DistributedFeeAmount	Decimal	The line item’s distributed fee amount.
DistributedEquipmentFeeAmount	Decimal	The line item’s distributed equipment fee amount.
DistributedLaborFeeAmount	Decimal	The line item’s distributed labor fee amount.
DistributedMaterialFeeAmount	Decimal	The line item’s distributed material fee amount.
DistributedOtherFeeAmount	Decimal	The line item’s distributed other fee amount.
DistributedSubFeeAmount	Decimal	The line item’s distributed sub fee amount.
LaborResourceGroup	String	The line item’s labor resource group.
LaborManHoursPerUnit	Decimal	The line item’s labor man hours per line item’s quantity.
LaborUnitsPerManHour	Decimal	The line item’s quantity per labor man hour
LaborProductivityKey	GUID	Reference to the line item’s productivity rate.
LaborCrewHourPerUnit	Decimal	The line item’s labor crew hours per line item’s quantity.
AlternateId	GUID	Reference to the line item’s alternate. Null for base estimate.
Notes	String	The line item’s Notes field.
WBSOverallFilter	String	The line item’s WBS Value for the WBS Property passed through the WBSOverallFilter parameter.
WBSProperty1	String	The line item’s WBS Value for the WBS Property passed through the WBSProperty1 parameter.
WBSProperty2	String	The line item’s WBS Value for the WBS Property passed through the WBSProperty2 parameter.
WBSProperty3	String	The line item’s WBS Value for the WBS Property passed through the WBSProperty3 parameter.
WBSProperty4	String	The line item’s WBS Value for the WBS Property passed through the WBSProperty4 parameter.

ProjectFeeDataDetails/EstimateFeeDataDetails/Fees
ProjectFees Intent: Returns all fees Fees/EstimateFees Intent: Returns all fees inside the Base Estimate.
Column Name	Type	Description
EstimateName	String	The name of the estimate.
EstimateKey	GUID	The unique identifier for the estimate.
Name	String	The name of the fee.
Type	Int	The type of the fee 0 = % of Direct 1 = % of Total 2 = Percentage 3 = Running Total 4 = Lump Sum 5 = % of Labor 6 = % of Material 7 = % of Equipment 8 = % of Sub 9 = % of Other 10 = % of Distributed Direct 11 = Bond 12 = Custom 13 = Subtotal 14 = Compounding
EvalOrder	Int	The order of the fee in the fee table.
Amount	Decimal	The amount of the fee.
Value	Decimal	The cost of the fee.
Distributed	Boolean	Returns True if the fee is distributed.
CreatedBy	String	The estimator that added the fee.
IsTurnedOn	Boolean	Returns True if the fee is turned on.
LastUpdatedBy	String	The estimator that last changed the fee.
IsDistributedToEquipment	Boolean	Returns True if the fee is distributed to equipment.
IsDistributedToLabor	Boolean	Returns True if the fee is distributed to labor.
IsDistributedToMaterial	Boolean	Returns True if the fee is distributed to material.
IsDistributedToOther	Boolean	Returns True if the fee is distributed to other.
IsDistributedToSub	Boolean	Returns True if the fee is distributed to sub.
IsSubtotal	Boolean	Returns true if Type = 3
IsLumpSum	Boolean	Returns true if Type = 4
IsRunningTotal (ProjectFees only)	Boolean	Returns true if Type = 13
IsCompounding (ProjectFees only)	Boolean	Returns true if Type = 14
AlternateId (ProjectFees only)	GUID	Unique identifier for the associated alternate. Null for base estimate
CriteriaApplicationType	Int	Numerical representation of Custom Fee Criteria Type 1 = All Must Apply 0 = Any Can Apply, or default if not custom fee
DistributedCriteriaApplicationType	Int	Numerical representation of Distribution Criteria Type 1 = All Must Apply 0 = Any Can Apply, or default if not distributed
ApplyToEquipment	Boolean	Returns true if Custom Fee is based on Equipment Cost
ApplyToLabor	Boolean	Returns true if Custom Fee is based on Labor Cost
ApplyToMaterial	Boolean	Returns true if Custom Fee is based on Material Cost
ApplyToOther	Boolean	Returns true if Custom Fee is based on Other Cost
ApplyToSub	Boolean	Returns true if Custom Fee is based on Sub Cost
BaseCostType	Int	If bond fee, base cost’s numerical Type. Otherwise, null.
BaseCostReferences	String	Name of the fee referenced inside bond fee
UserDefinedBaseCost	Decimal	User defined base cost for bond fee.
InitialPremium	Decimal	Bond fee’s initial premium
ConstructionMonths	Decimal	Bond fee’s construction months
ProjectMonths	Int	Bond fee’s project months
AdditionalPremium	Decimal	Bond fee’s additional premium
DurationPremium	Decimal	Bond fee’s duration premium
SubTotalPremium	Decimal	Bond fee’s subtotal premium
PremiumSurcharge	Decimal	Bond fee’s premium surcharge
LastUpdatedBy	String	Individual that last updated the fee

EstimateBondFeeSteps
Intent: Returns the bond fee steps inside the Base Estimate.
Column Name	Type	Description
EstimateName	String	The name of the estimate.
EstimateKey	GUID	Unique identifier for the estimate
StepName	String	“First”, “Next”, or “Over” for the Fee Step
Value	Decimal	Based On Cost for Fee Step
Percent	Decimal	Fee Step’s Step Value
TotalAmount	Decimal	Fee Step’s resulting amount based on Value and Percent
EF_BondKey	GUID	Unique identifier for the fee
IsFinalStep	Boolean	Returns true if the step is the final step for the bond fee
Order	Int	Bond fee step order number
StepId	GUID	Unique identifier for the bond fee step

EstimateCustomFeeCriterias
Intent: Returns all custom fee criteria for the Base Estimate.
Column Name	Type	Description
EstimateName	String	The name of the estimate.
EstimateKey	GUID	Unique identifier for the estimate
PropertyName	String	WBS Property Name for the criteria
PropertyType	Int	WBS Property Type, stored as a number. 1 = Number 2 = Text 3 = DateTime 5 = Color 6 = Boolean (True/False) 7 = Price
ValueCompareType	Int	Comparison type used for the WBS Property criteria 0 = Equals 1 = Not Equal 2 = Contains
PropertyValue	String	WBS Property Value for the criteria
TeamCustomFeeEF_Key	GUID	Unique identifier for the fee
TeamCustomFeeEF_UserName	String	Fee’s created by username

EstimateFeeDistributionCriterias
Intent: Returns all distributed fee criteria for the Base Estimate.
Column Name	Type	Description
EstimateName	String	The name of the estimate.
EstimateKey	GUID	Unique identifier for the estimate
PropertyName	String	WBS Property Name for the criteria
PropertyType	Int	WBS Property Type, stored as a number. 1 = Number 2 = Text 3 = DateTime 5 = Color 6 = Boolean (True/False) 7 = Price
ValueCompareType	Int	Comparison type used for the WBS Property criteria 0 = Equals 1 = Not Equal 2 = Contains
PropertyValue	String	WBS Property Value for the criteria
EF_FeeKey	GUID	Unique identifier for the fee
EF_FeeUserName	String	Fee’s created by username

EstimateDataDetails
Intent: Returns 1 column per Project View property and Estimate Properties window property for each estimate row.
Column Name	Type	Description
EstimateName	String	The name of the estimate.
EstimateKey	GUID	The unique identifier for the estimate.
VersionName	String	The version of the estimate.
EstimateNumber	String	The job number of the estimate.
ClientName	String	The Client Name inside the Estimate Properties window.
ProjectName	String	The Project Name inside the Estimate Properties window.
ClientAddress	String	The Client Address inside the Estimate Properties window.
ClientCity	String	The Client City inside the Estimate Properties window.
ClientZipCode	String	The Client Zip Code inside the Estimate Properties window.
…	String	1 column per Project View Property name.

EstimatePropertyValuesView
Intent: Returns all Project View properties for each estimate as individual rows.
Column Name	Type	Description
EstimateKey	GUID	The unique identifier for the estimate.
EstimatePropertyName	String	The name of the property in the Project View.
EstimatePropertyValue	String	The text value of the project property

EstimatePropertiesView
Intent: Returns a list of all Project View properties in the jobs database.
Column Name	Type	Description
EstimatePropertyName	String	The name of the property in the Project View.

EstimateResourceDataDetails/AlternateResourceDataDetails
Intent: Returns time totals (hours, days, weeks, months) per resource per cost category for each estimate.
Column Name	Type	Description
EstimateKey	GUID	The unique identifier for the estimate.
EstimateName	String	The name of the estimate.
ResourceName	String	The name of the resource
AlternateId (AlternateResource only)	GUID	The unique identifier for the alternate.
TotalLaborResourceHours	Float	Summed total labor resource hours.
TotalLaborResourceDays	Float	Summed total labor resource days.
TotalLaborResourceWeeks	Float	Summed total labor resource weeks.
TotalLaborResourceMonths	Float	Summed total labor resource months.
TotalEquipmentResourceHours	Float	Summed total equipment resource hours.
TotalEquipmentResourceDays	Float	Summed total equipment resource days.
TotalEquipmentResourceWeeks	Float	Summed total equipment resource weeks.
TotalEquipmentResourceMonths	Float	Summed total equipment resource months.
TotalMaterialResourceHours	Float	Summed total material resource hours.
TotalMaterialResourceDays	Float	Summed total material resource days.
TotalMaterialResourceWeeks	Float	Summed total material resource weeks.
TotalMaterialResourceMonths	Float	Summed total material resource months.
TotalSubResourceHours	Float	Summed total sub resource hours.
TotalSubResourceDays	Float	Summed total sub resource days.
TotalSubResourceWeeks	Float	Summed total sub resource weeks.
TotalSubResourceMonths	Float	Summed total sub resource months.
TotalOtherResourceHours	Float	Summed total other resource hours.
TotalOtherResourceDays	Float	Summed total other resource days.
TotalOtherResourceWeeks	Float	Summed total other resource weeks.
TotalOtherResourceMonths	Float	Summed total other resource months.

EstimateRatesResourcesDataDetails
Intent: Returns Resource information inside each estimate.
Column Name	Type	Description
EstimateKey	GUID	The unique identifier for the estimate.
RateTableName	String	The name of the rate table
ResourceGroupName	String	The name of the resource group.
ResourceName	String	The name of the resource.
ResourceQuantity	Decimal	The resource’s quantity
ResourceBaseRate	Decimal	The resource’s base rate.
ResourceTotalRate	Decimal	The resource’s total rate with resource markups.
ResourceRateUnit	Int	The resource’s time unit, stored as a number: 1 = Week 2 = Day 3 = Hour 4 = Minute 7 = Month

ProjectCostProductivityDetails/EstimateCostProductivityDetails/ AlternateCostProductivityDetails
Intent: Returns the productivity rate information for each resource group inside a line item.
Column Name	Type	Description
EstimateKey	GUID	The unique identifier for the estimate.
Key	GUID	The unique identifier for the productivity rate.
AlternateId (Project/Alternate Views only)	GUID	The unique identifier for the Alternate.
IsFlipped	Boolean	Returns true if the productivity rate is formatted as Time Amount / Work Amount. Returns false if the productivity rate is formatted as Work Amount / Time Amount.
Description	String	The line item’s Description.
AggregatedWorkAmount	Decimal	The productivity rate’s work amount, including escalations, productivity adjustments, and markups.
AggregatedTimeAmount	Decimal	The productivity rate’s time amount, including escalations, productivity adjustments, and markups.
Unit	Int	The productivity rate’s time amount unit, stored as a number: 1 = Week 2 = Day 3 = Hour 4 = Minute 7 = Month
CostCategory	String	Cost category name for the line item’s resource group

ProjectOverlineDataDetails
Intent: Returns the overline quantity, unit, and grouping information for each overline inside all estimates.
Column Name	Type	Description
EstimateKey	GUID	The unique identifier for the estimate.
OverlineId	GUID	The unique identifier for the overline.
Quantity	Decimal	The overline quantity for the overline.
Unit	String	The unit of measure of the overline.
AlternateId	GUID	The unique identifier for the Alternate.
WBSProperty1_Name	String	The WBS Property Name for Level 1.
WBSProperty1	String	The WBS Value for Level 1.
WBSProperty2_Name	String	The WBS Property Name for Level 2.
WBSProperty2	String	The WBS Value for Level 2.
WBSProperty3_Name	String	The WBS Property Name for Level 3.
WBSProperty3	String	The WBS Value for Level 3.
WBSProperty4_Name	String	The WBS Property Name for Level 4.
WBSProperty4	String	The WBS Value for Level 4.

Merging Archive Data From Another System

This document provides a map for customer historical SQL tables to be conditioned to the views supplied by Beck Tech. Some clients have been successful using this map to mash their data with ours for an even longer historical view that predates implementation of DESTINI Estimator.