Custom Power BI Building: Database Access and SQL Views List

This article is for Hosted clients who want to connect to their hosted Estimates Database (DESTINIData) for the purposes of creating a custom Power BI dashboard.

Prerequisites for Database Access

1. Whitelisting, Server Name, Database Name

To gain direct access to your database in the Hosted environment requires having your Company's IP address whitelisted for server access. Please contact Support to request an IP whitelisting.

As part of our standard procedure, when we whitelist your IP, we will also issue you the server name and your database name (the server name and database can also be found in your standard Power BI dashboard issued by Beck Tech, via the Data Source menu).

Due to security considerations, we cannot whitelist personal home IP addresses.

2. Credentials:

When our standard Power BI dashboards are deployed to a client, we also issue a username and password that is used to set up a refresh schedule in Power BI Online. You should have a record of those credentials, and they will be required here. If you do not, please contact Support.

3. SQL Server Management Studio (SSMS):

This guide demonstrates database access using SSMS.

Accessing the Database To See the Tables and Views

Open SSMS
On the login window, enter the server name and credentials that were provided.

2. Click the Options tab and Enter the database name and click Connect

Power BI Desktop: Updating Credentials

If you have been whitelisted as detailed above, you will also have the ability to open the .pbix dashboard file in Power BI Desktop, where you will be able to quickly view the dashboard or make edits in the Power Query Editor.

When opening in Power BI Desktop, you may be prompted to enter credentials. Here are the steps to do so:

1. Open the .pbix file in Power BI Desktop and click on click on the 'Transform Data' button on the top Ribbon.

2. The Power Query Editor will open and you will see a prompt: "Please specify how to connect'. Click the 'Edit Credentials' button.

3. Select 'Database' on the left menu. Then input the username and password you were issued with your PBI files. Lastly, in the 'Select which Level' at the bottom, choose the option that shows both the server AND your database name (eg. servername.database.windows.net;your_destinidata_ database_name_destinidata).

4. Click 'Connect'.

DESTINIData SQL Views and Descriptions

There are several views that can be used for data analytics. This article will list out 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.

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.

EstimateSummaryDataDetails
Intent: High level summary of each estimate
Field Name	Description
EstimateKey	The unique identifier for the estimate
EstimateName	The name of the estimate.
VersionName	The name of the version
EstimateNumber	The job number for the estimate.
ProjectName	The name of the project.
ClientName	The client’s name.
ClientAddress	The client’s street address.
ClientCity	The client’s city location.
ClientState	The client’s state location.
ClientZipCode	The client’s postal code.
TotalCost	The total cost (direct + indirect) of the estimate.
TotalCostPerArea	The total cost (direct + indirect) per total building area of the estimate.
DirectCost	The direct cost of the estimate.
DirectCostPerArea	The direct cost per total building area of the estimate.
ProjectType	The type of the project.
EstimateStatus	The status of the estimate.
CreateYear	The year the estimate was created in DESTINI Estimator.

EstimatePropertyDataDetails
Intent: High level summary of each estimate and its properties.
Field Name	Description
EstimateName	The name of the estimate.
EstimateKey	The unique identifier for the estimate.
CreatedBy	The estimator that added the property to the estimate.
Property	The name of the property in the project tab.
Value	The value of the project property

EstimateWBSProperties
Intent: List of WBS Properties in all of the estimates.
Field Name	Description
WBSPropertyName	The name of the WBS property.

EstimateWBSPropertyValues
Intent: List of WBS Properties and their default values in the estimates.
Field Name	Description
EstimateKey	The unique identifier for the estimate.
WBSPropertyName	The name of the WBS property.
WBSPropertyType	The type of WBS property (i.e., Estimate specific- or database level).
WBSPropertyValue	The default value of the WBS property for that estimate.

HistoricalCostData
Intent: Detailed set of line items across an estimate.
Field Name	Description
EstimateKey	The uniqueidentifier for the estimate.
Key	The uniqueidentifier for the line item
Username	The name of the estimator that currently “owns” the record.
Name	The line item’s item number.
Description	The line item’s description.
Quantity	The line item’s total quantity.
Unit	The line item quantity’s unit of measure.
LaborPrice	The line item’s labor unit cost.
MaterialPrice	The line item’s material unit cost.
EquipmentPrice	The line item’s equipment unit cost.
SubPrice	The line item’s sub/install unit cost.
OtherPrice	The line item’s other/misc unit cost.
TotalUnitCost	The line item’s total unit cost.
TotalCost	The line item’s total cost.
TotalCostPerUnit	The line item’s total cost per unit.
CostPerUnitResource	The resource TotalCostPerUnit is based on.
DistributedFeeAmount	The line item’s distributed fee amount.
DistributedEquipmentFeeAmount	The line item’s distributed equipment fee amount.
DistributedLaborFeeAmount	The line item’s distributed labor fee amount.
DistributedMaterialFeeAmount	The line item’s distributed material fee amount.
DistributedOtherFeeAmount	The line item’s distributed fee amount.
DistributedSubFeeAmount	The line item’s distributed fee amount.
CreatedBy	The estimator that added the line item to the estimate.
LaborResourceGroupDuration	The line item’s labor duration.
LaborResourceGroupDurationUnit	The line item’s labor duration unit. (a number that represents hour, month, days, year, etc.)
LaborResourceDuration	The line item’s labor resource duration.
LaborResourceDurationUnit	The line item’s labor resource duration unit.
LaborResourceGroup	The line item’s labor resource group.
LaborUnitsPerManHour	The line item’s labor units per man hour
QuantityPerLaborResourceUnit	The line item’s quantity per labor resource unit.
MaterialResourceGroupDuration	The line item’s Material duration.
MaterialResourceGroupDurationUnit	The line item’s Material duration unit. (a number that represents hour, month, days, year, etc.)
MaterialResourceDuration	The line item’s Material resource duration.
MaterialResourceDurationUnit	The line item’s Material resource duration unit.
MaterialResourceGroup	The line item’s Material resource group.
MaterialUnitsPerManHour	The line item’s Material units per man hour
QuantityPerMaterialResourceUnit	The line item’s quantity per Material resource unit.
EquipmentResourceGroupDuration	The line item’s Equipment duration.
EquipmentResourceGroupDurationUnit	The line item’s Equipment duration unit. (a number that represents hour, month, days, year, etc.)
EquipmentResourceDuration	The line item’s Equipment resource duration.
EquipmentResourceDurationUnit	The line item’s Equipment resource duration unit.
EquipmentResourceGroup	The line item’s Equipment resource group.
EquipmentUnitsPerManHour	The line item’s Equipment units per man hour
QuantityPerEquipmentResourceUnit	The line item’s quantity per Equipment resource unit.
SubResourceGroupDuration	The line item’s Sub duration.
SubResourceGroupDurationUnit	The line item’s Sub duration unit. (a number that represents hour, month, days, year, etc.)
SubResourceDuration	The line item’s Sub resource duration.
SubResourceDurationUnit	The line item’s Sub resource duration unit.
SubResourceGroup	The line item’s Sub resource group.
SubUnitsPerManHour	The line item’s Sub units per man hour
QuantityPerSubResourceUnit	The line item’s quantity per Sub resource unit.
OtherResourceGroupDuration	The line item’s Other duration.
OtherResourceGroupDurationUnit	The line item’s Other duration unit. (a number that represents hour, month, days, year, etc.)
OtherResourceDuration	The line item’s Other resource duration.
OtherResourceDurationUnit	The line item’s Other resource duration unit.
OtherResourceGroup	The line item’s Other resource group.
OtherUnitsPerManHour	The line item’s Other units per man hour
QuantityPerOtherResourceUnit	The line item’s quantity per Other resource unit.
Uniformat 1	Uniformat Level 1 classification.
Uniformat 2	Uniformat Level 2 classification.
Uniformat 3	Uniformat Level 3 classification.
Uniformat 4	Uniformat Level 4 classification.
….	Any other user defined WBS_Property

In Power BI, this table is referred to as EstimateCostDataDetails

EstimateFeeDataDetails
Intent: High level summary of each estimate and its properties.
Field Name	Description
EstimateName	The name of the estimate.
EstimateKey	The unique identifier for the estimate.
Name	The name of the fee.
Type	The type of the fee (i.e., Lump Sum, Bond).
EvalOrder	The order of the fee in the fee table.
Amount	The amount of the fee.
Value	The value of the fee.
Distributed	A boolean of if the fee is distributed.
CreatedBy	The estimator that added the fee.
IsTurnedOn	A boolean of if the fee is turned on.
LastUpdatedBy	The estimator that last changed the fee.
IsDistributedtoEquipment	A boolean of if the fee is distributed to equipment.
IsDistributedToLabor	A boolean of if the fee is distributed to labor.
IsDistributedToMaterial	A boolean of if the fee is distributed to material.
IsDistributedToOther	A boolean of if the fee is distributed to other.
IsDistributedToSub	A boolean of if the fee is distributed to sub.

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.