Springwater 04 | Revenue and cost types

Springwater 04 | Revenue and cost types


In this post I’m going to explain how I’ve set up the Revenue Type and Cost Type dimensions in Power BI. Here’s the part of the OLTP database that contains the relevant data:

You can see that the approach taken to these two dimensions in the OLTP system is different. There are only three revenue type – Billable Revenue, Incentive Credit MRC and Service Level Credit MRC. These values are stored directly in the Revenue table within the set schema. Cost types are different – there is a large, and variable, number of cost components for each circuit in the network, so the designers of the OLTP system have decided to separate cost types out into a lookup table within the circuit schema. In my Power BI data model, I want Cost Type and Revenue Type to be dimensions, and I want the user experience to be the same for both. This means I need to write different Power Query code for each dimension table in Power BI.

Cost Type

I’ll start with the Cost Type dimension as that is the simpler of the two to implement, because there is already a Cost Type table in the OLTP database. All I need to do is remove the column representing the related Cost table. Here’s the Power Query code I used to create the dimension table:

// Cost Type (dimension query)
    Source = circuit_CostType,
    Result = 
    Table.RemoveColumns ( Source, { "circuit.Cost" } )

And here’s the first few rows of the resulting Cost Type dimension table:

Check out the previous post on service packages for a description of the code I’ve used to provide access to the underlying OLTP database. You should be able to deduce the code for the circuit_CostType query above based on what I’ve described there.

Revenue Type

As the three revenue types are part of the column structure of the Revenue table in the OLTP database, I’m going to create the Revenue Type dimension table using the table constructor function in Power Query. To do this, I need to specify the column names and types for the table, as well as the rows I want to insert. I’ll declare a table type with the column names and types, then add the required rows. The official Microsoft documentation on the table constructor isn’t particularly helpful on the subject of passing a table type as it’s first parameter. I must thank Chris Webb for this excellent tip.

Here’s the query code and the three resulting rows:

// Revenue Type (dimension query)
    // There are only 3 revenue types, which do not occur in the source
    // database, so they are created here in a new table
    Source = 
    #table ( 
        type table [ 
            #"Revenue Type" = Text.Type, 
            #"Revenue Component" = Text.Type, 
            #"Recurrence" = Text.Type, 
            #"RevenueTypeID" = Int64.Type 
            { "Revenue | Billable",                   "Revenue", "Recurring", 1 },
            { "Revenue | Service Level Credit | MRC", "Revenue", "Recurring", 2 },
            { "Revenue | Incentive Credit | MRC",     "Revenue", "Recurring", 3 }
    Result = Source

It was relatively straightforward for me to create these two dimensions. In the next post, I’ll tackle something more complex – the Site dimension.

Leave a Reply