We were recently asked how we can build a dashboard for cost. The basis of the model was an IFC model.
The first challenge was finding all the classes that had a WBS structure defined that correlated to the pricing sheet that was provided …
select sc.name || '.' || cl.name class, pd.class.id, pd.name property from meta.ecpropertydef pd join meta.ecclassdef cl on pd.class.id = cl.ecinstanceid join meta.ecschemadef sc on cl.schema.id = sc.ecinstanceid where pd.name like '%<partialomniclassfield>%'
Running this query returned only 12 classes … also select * from one of these classes
Select * from IfcDynamic.ifcAspect_IfcBuildingElementProxy_SpecificClass
Returns the properties that I can use …
The properties I was interested are
IFCCode IFCCodeDescription IFCNetQuantity IFCUnitOfMeasure
Building out a query for this we get
Select ecInstanceId id, ecclassId, IFCCode, IFCCodeDescription, IFCNetQuantity, IFCUnitOfMeasure from IfcDynamic.ifcAspect_IfcBuildingElementProxy_SpecificClass
Then appending this
Select ecInstanceId id, ecclassId, IFCCode, IFCCodeDescription, IFCNetQuantity, IFCUnitOfMeasure from IfcDynamic.ifcAspect_IfcBuildingElementProxy_SpecificClass UNION Select ecInstanceId id, ecclassId, IFCCode, IFCCodeDescription, IFCNetQuantity, IFCUnitOfMeasure from IfcDynamic.ifcAspect_IfcBuildingElementProxy_SpecificClassB
Note I generally build this out in a spreadsheet from the list of classes that have the properties.
Using a Formula something similar to this
="select a.ecinstanceid id, a.ecclassid, a." & C2 & " WBSCode, a." & D2 & " Discipline, a." & E2 & " NetQuantity, a." & F2 & " UnitofMeasure from " & A2 & " a union " & G1
The last row then will have the ultimate query to be used, less the trailing union phrase
Before we load PowerBI …
I also took the users Costing Spreadsheet and ensured that the columns I needed were aligned
WBSCode
Description
Unit
Units
Price
Currency
02.01.01.00-01
Not necessary
M2
0
7.05 MXN
MXN
The key columns we need to ensure alignment are the WBS code, the price and the currency …. And also ensure that the Units are aligned with what is in the model.
Note also there were also mixed currencies in this sheet, as long as the symbols are industry standard currencies, then this is acceptable.
In this case we changed the € symbol to EUR
Finally we need a ProjectId and iModelId for the iModel viewer.
ProjectId
iModelId
d821ebcd-730c-42c8-865d-f7d2f45513ad
e41a8c90-98e8-48e2-b001-6f88b1e61baa
We can now plug this into PowerBI
I will not go through the Excel loading as that should be straightforward, the key for the pricing spreadsheet is to make sure Price is loaded as a Decimal number and that WBSCode is unique, there should only be one row for each WBS code.
We noted in the dataset from the user that there were multiple rows with the same WBS code which needed resolving
Still in PowerBI select the Design Insights Connector
And populate the ProjectId, iModelId and the complete SQL union query from above
The below PBAX script will ensure the data is pulled from the iModel note this is after the source statement.
#"Converted to Table" = Record.ToTable(Source), Value = #"Converted to Table"{3}[Value], #"Converted to Table1" = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {"className", "discipline", "id", "netQuantity", "unitofMeasure", "wBSCode"}, {"className", "discipline", "id", "netQuantity", "unitofMeasure", "wBSCode"}), #"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"netQuantity", type number}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"netQuantity", "NetQuantity"}, {"discipline", "Discipline"}, {"wBSCode", "WBSCode"}}) in #"Renamed Columns"
This loaded ~2700 rows from the iModel
Also to enable the overall price to be listed in USD, we can pull the currency conversion rates from a currency exchange.
This is a standard Load from Web Data Connection
let Source = Web.BrowserContents("http://www.xe.com/currencytables/?from=USD"), #"Extracted Table From Html" = Html.Table(Source, {{"Column1", "TABLE[id='historicalRateTbl'] > * > TR > :nth-child(1)"}, {"Column2", "TABLE[id='historicalRateTbl'] > * > TR > :nth-child(2)"}, {"Column3", "TABLE[id='historicalRateTbl'] > * > TR > :nth-child(3)"}, {"Column4", "TABLE[id='historicalRateTbl'] > * > TR > :nth-child(4)"}}, [RowSelector="TABLE[id='historicalRateTbl'] > * > TR"]), #"Promoted Headers" = Table.PromoteHeaders(#"Extracted Table From Html", [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Currency code#(lf)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)▲▼", type text}, {"Currency name#(lf)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)▲▼", type text}, {"Units per USD", type number}, {"USD per Unit", type number}}) in #"Changed Type"
IFCWBSCodes.WBSCode * = 1 Sanitised_WSB.WBSCode Santiised_WBS.Currency * = 1 USD_Exchange.[Currency Code]
Most of the following can be optimized … but I added the following columns to enable these to be easily checked.
Add the following columns to the IFCWBSCodes table
UnitCost = RELATED(Sanitised_WBS[Price]) USDCost = Sanitised_WBS[Price] / RELATED(USDExchange[Units per USD]) NetCost = IFCWBSCodes[NetQuantity] * IFCWBSCodes[UnitCost] NetUSDCost = IFCWBSCodes[NetQuantity] * IFCWBSCodes[USDCost] ElementFilter = "{ ""Type"": ""EmphasizeQuery"", ""Query"" : ""SELECT ecInstanceId id FROM " & IFCWBSCodes[className] & " where ecInstanceId = " & [id] & """, ""IdKey"" : ""id"", ""Colour"" : ""#FF3300"" }"
Add a PieChart
Add an iModelViewer
You can now navigate any materialized item and view those items in the model … and the pricing is also shown in USD
Adding the relationships enables us to select any WBS code and view those elements in the model or the breakdown.