It’s been some time that I’m working with OData knowledge supply in Energy BI. One problem that I virtually at all times wouldn’t have understanding of the underlying knowledge mannequin. It may be actually onerous and time consuming if there isn’t any one within the enterprise that understands the underlying knowledge mannequin. I do know, we are able to use $metadata to get the metadata schema from the OData feed, however let’s not go there. I’m not an OData professional however right here is the factor for somebody like me, I work with numerous knowledge sources which I’m not essentially an professional in, however I want to know what the entities are, how they’re linked and many others… then what if I wouldn’t have entry any SMEs (Subject Matter Expert) who may help me with that?
So getting concerned with extra OData choices, let’s get into it.
The customized perform under accepts an OData URL then it discovers all tables, their column depend, their row depend (extra on this later), quantity and record of associated tables, quantity and record of columns of kind textual content, kind quantity and Decimal.Sort.
// fnODataFeedAnalyser
(ODataFeed as textual content) =>
let
Supply = OData.Feed(ODataFeed),
SourceToTable = Desk.RenameColumns(
Desk.DemoteHeaders(Desk.FromValue(Supply)),
{{"Column1", "Identify"}, {"Column2", "Knowledge"}}
),
FilterTables = Desk.SelectRows(
SourceToTable,
every Sort.Is(Worth.Sort([Data]), Desk.Sort) = true
),
SchemaAdded = Desk.AddColumn(FilterTables, "Schema", every Desk.Schema([Data])),
TableColumnCountAdded = Desk.AddColumn(
SchemaAdded,
"Desk Column Depend",
every Desk.ColumnCount([Data]),
Int64.Sort
),
TableCountRowsAdded = Desk.AddColumn(
TableColumnCountAdded,
"Desk Row Depend",
every Desk.RowCount([Data]),
Int64.Sort
),
NumberOfRelatedTablesAdded = Desk.AddColumn(
TableCountRowsAdded,
"Variety of Associated Tables",
every Record.Depend(Desk.ColumnsOfType([Data], {Desk.Sort}))
),
ListOfRelatedTables = Desk.AddColumn(
NumberOfRelatedTablesAdded,
"Record of Associated Tables",
every
if [Number of Related Tables] = 0 then
null
else
Desk.ColumnsOfType([Data], {Desk.Sort}),
Record.Sort
),
NumberOfTextColumnsAdded = Desk.AddColumn(
ListOfRelatedTables,
"Variety of Textual content Columns",
every Record.Depend(Desk.SelectRows([Schema], every Textual content.Accommodates([Kind], "textual content"))[Name]),
Int64.Sort
),
ListOfTextColunmsAdded = Desk.AddColumn(
NumberOfTextColumnsAdded,
"Record of Textual content Columns",
every
if [Number of Text Columns] = 0 then
null
else
Desk.SelectRows([Schema], every Textual content.Accommodates([Kind], "textual content"))[Name]
),
NumberOfNumericColumnsAdded = Desk.AddColumn(
ListOfTextColunmsAdded,
"Variety of Numeric Columns",
every Record.Depend(Desk.SelectRows([Schema], every Textual content.Accommodates([Kind], "quantity"))[Name]),
Int64.Sort
),
ListOfNumericColunmsAdded = Desk.AddColumn(
NumberOfNumericColumnsAdded,
"Record of Numeric Columns",
every
if [Number of Numeric Columns] = 0 then
null
else
Desk.SelectRows([Schema], every Textual content.Accommodates([Kind], "quantity"))[Name]
),
NumberOfDecimalColumnsAdded = Desk.AddColumn(
ListOfNumericColunmsAdded,
"Variety of Decimal Columns",
every Record.Depend(
Desk.SelectRows([Schema], every Textual content.Accommodates([TypeName], "Decimal.Sort"))[Name]
),
Int64.Sort
),
ListOfDcimalColunmsAdded = Desk.AddColumn(
NumberOfDecimalColumnsAdded,
"Record of Decimal Columns",
every
if [Number of Decimal Columns] = 0 then
null
else
Desk.SelectRows([Schema], every Textual content.Accommodates([TypeName], "Decimal.Sort"))[Name]
),
#"Eliminated Different Columns" = Desk.SelectColumns(
ListOfDcimalColunmsAdded,
{
"Identify",
"Desk Column Depend",
"Desk Row Depend",
"Variety of Associated Tables",
"Record of Associated Tables",
"Variety of Textual content Columns",
"Record of Textual content Columns",
"Variety of Numeric Columns",
"Record of Numeric Columns",
"Variety of Decimal Columns",
"Record of Decimal Columns"
}
)
in
#"Eliminated Different Columns"
Right here is the GitHub hyperlink for the above code.
I used this perform for preliminary investigation on numerous OData sources together with Microsoft Venture On-line, Microsoft Enterprise Central, some third get together instruments and naturally Northwind pattern. Whereas it really works wonderful in all the talked about knowledge sources, for some knowledge sources like Enterprise Central it’s not fairly useful. So be conscious of that.
I used Energy Question formatter to format the above code. I simply polished it a bit to suit it to my style. Give it a go, it’s device.
As talked about earlier, the above perform reveals tables’ column depend in addition to their row depend. On the latter, the row depend, I want to increase a degree. If the underlying desk has lots of columns then the row depend calculation might take a very long time.
The screenshot under reveals the outcomes of the fnODataFeedAnalyser perform invoked for a Microsoft Venture On-line and it took a wee bit lower than 3 minutes to run.
fnODataFeedAnalyser customized perform for Microsoft Venture On-lineHave you ever used this methodology earlier than to analyse a dataset that you’re not accustomed to the construction? Do have a greater thought? Please share your ideas within the feedback part under.
Oh! and… by the way in which, be at liberty to vary the above code and make it higher. Simply don’t forget to share the improved model with the neighborhood.
