More and more we are getting requests to create reports in Power BI using date from cloud-based applications using REST API’s. We have experience with several power bi solutions where the full dataset (limited) or only a limited amount of data was extracted from the application every time the data was refreshed.
This time the challenge was:
- The volume of data was too big to load all data every data refresh.
- Big API requests crashed to online application, so not allowed to request big volume of data.
- The API token was not fixed so new token needed every week
Existing connectors were not available for the application (Xelion, a telephone system).
We finally came up with a solution were only Power BI is used to exact the API source data and load it to a SQL server database incrementally.
The end result is:
- Having a power bi workspace with 4 datasets (ETL solution):
o 1 dataset updates the key in the SQL database ones a week
o 3 datasets that add new records (max 5000) and updates all records that are modified during the last two days (in this example that was sufficient) every 30 minutes. As 1 dataset can be only scheduled 8 times (Pro licence) I created 3 which are all scheduled 8 times. This results in 24 times running between 7am and 7pm. If required this could be expanded to 48 times by adding another 3 datasets.
- A power bi pbix file that is connected to the SQL database where all the data is stored for reporting purpose. (Report solution)
The ETL run for almost a week to get fully up to date and load all data up to now and after that every 30 minutes the data was up to date.
What did I do to create the ETL solution?
I created two tables in de SQL database.
1. Table with only one field and one record that holds the API token.
2. Table with data needed. It’s only one table and for testing everything was created in format text except for the primary key field (oid) which is a number.
I created a pbix file:
Let
Login_Parameter = "[user]", Login_Password = "[password]", Login_Userspace = "TEST2", LOGIN = Text.Combine({"""",Login_Parameter,""""},""), PW = Text.Combine({"""",Login_Password,""""},""), US = Text.Combine({"""",Login_Userspace,""""},""), body = "{ ""userName"": "&LOGIN&", ""password"": "&PW&", ""userSpace"": "&US&" }", url = "https://[internetaddress]", Bron = Json.Document(Web.Contents(url,[Headers=[#"Content-Type"="application/x-www-form-urlencoded"], Content = Text.ToBinary(body),RelativePath="/api/v1/[company]/me/login"])), authentication = Bron[authentication], sql = "UPDATE [dbo].[Xelion_Key] SET [key] = '"&authentication&"'", Bron2 = Sql.Database("[server]", "[database]"), query = Value.NativeQuery(Bron2,sql)
in query
This pbix file is published to Power BI service and scheduled ones a week so the token is refreshed ones a week and stored in the sql database
A second pbix file where I created two functions and two queries
One function that gets the 5000 records from Xelion using the oid which is the variable in the function. The api token is taken from the SQL database which was stored by the first file.
let Bron = (OID) =>
let BronKey = Sql.Database("[server]", "[database]", [Query="Select * from Xelion_Key"]), Key = BronKey{0}[Key], Bron = Json.Document(Web.Contents("https://[Internetaddress]",[RelativePath="api/v1/[company]/communications?limit=5000&after="&Text.From(OID)&"&include=all", Headers=[ContentType="application/json", Authorization="xelion "&Text.From(Key)]])), #"Geconverteerd naar tabel" = Record.ToTable(Bron), Value = #"Geconverteerd naar tabel"{0}[Value], #"Geconverteerd naar tabel1" = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Column1 uitgevouwen" = Table.ExpandRecordColumn(#"Geconverteerd naar tabel1", "Column1", {"object", "links"}, {"object", "links"}), #"object uitgevouwen" = Table.ExpandRecordColumn(#"Column1 uitgevouwen", "object", {"subject", "date", "durationSec", "status", "incoming", "displayed", "flagged", "hasAttachment", "hasInlineAttachment", "contentSummary", "contentSummaryIsComplete", "participants", "voicemail", "callId", "callFlowId", "phone", "isConferenceCall", "callAnswerTime", "callAnswerTimeSec", "callEndTime", "onHoldDuration", "trunk", "phoneLine", "wrapUpTime", "commonName", "oid", "objectType"}, {"subject", "date", "durationSec", "status", "incoming", "displayed", "flagged", "hasAttachment", "hasInlineAttachment", "contentSummary", "contentSummaryIsComplete", "participants", "voicemail", "callId", "callFlowId", "phone", "isConferenceCall", "callAnswerTime", "callAnswerTimeSec", "callEndTime", "onHoldDuration", "trunk", "phoneLine", "wrapUpTime", "commonName", "oid", "objectType"}), #"Kolommen verwijderd" = Table.RemoveColumns(#"object uitgevouwen",{"links"}), #"phoneLine uitgevouwen" = Table.ExpandRecordColumn(#"Kolommen verwijderd", "phoneLine", {"commonName"}, {"phoneLine"})
in #"phoneLine uitgevouwen"
in Bron
Then I created a query that determines the last oid in the sql database and uses the above function the get the following 5000 records from the API and stores it in the database base by creating a sql script insert into values for every records and the script is finally run on the sql server by using a nativequery in M code.
let Bron = Sql.Database("[server]", "[database]", [Query="Select Max([oid]) from Xelion"]), #"Namen van kolommen gewijzigd" = Table.RenameColumns(Bron,{{"", "OID"}}), #"Waarde vervangen2" = Table.ReplaceValue(#"Namen van kolommen gewijzigd",null,18446921,Replacer.ReplaceValue,{"OID"}), OID = #"Waarde vervangen2"{0}[OID]-10, #"Aangeroepen aangepaste functie" = Table.AddColumn(#"Waarde vervangen2", "Data opahlen", each #"Data ophalen"([OID]-1)), #"Data opahlen uitgevouwen" = Table.ExpandTableColumn(#"Aangeroepen aangepaste functie", "Data opahlen", {"subject", "date", "durationSec", "status", "incoming", "displayed", "flagged", "hasAttachment", "hasInlineAttachment", "contentSummary", "contentSummaryIsComplete", "participants", "voicemail", "callId", "callFlowId", "phone", "isConferenceCall", "callAnswerTime", "callAnswerTimeSec", "callEndTime", "onHoldDuration", "trunk", "phoneLine", "wrapUpTime", "commonName", "oid", "objectType"}, {"subject", "date", "durationSec", "status", "incoming", "displayed", "flagged", "hasAttachment", "hasInlineAttachment", "contentSummary", "contentSummaryIsComplete", "participants", "voicemail", "callId", "callFlowId", "phone", "isConferenceCall", "callAnswerTime", "callAnswerTimeSec", "callEndTime", "onHoldDuration", "trunk", "phoneLine", "wrapUpTime", "commonName", "oid.1", "objectType"}), Source = Table.RemoveColumns(#"Data opahlen uitgevouwen",{"OID"}), #"Namen van kolommen gewijzigd1" = Table.RenameColumns(Source,{{"oid.1", "oid"}}), #"Waarde vervangen1" = Table.ReplaceValue(#"Namen van kolommen gewijzigd1","","leeg" ,Replacer.ReplaceValue,{"contentSummary","subject","callId","callFlowId","trunk","callAnswerTime","callEndTime"}), #"Waarde vervangen" = Table.ReplaceValue(#"Waarde vervangen1",null,"leeg",Replacer.ReplaceValue,{"subject", "date", "durationSec", "status", "incoming", "displayed", "flagged", "hasAttachment", "hasInlineAttachment", "contentSummary", "contentSummaryIsComplete", "participants", "voicemail", "callId", "callFlowId", "phone", "isConferenceCall", "callAnswerTime", "callAnswerTimeSec", "callEndTime", "onHoldDuration", "trunk", "phoneLine", "wrapUpTime", "commonName", "oid", "objectType"}), #"Type gewijzigd" = Table.TransformColumnTypes(#"Waarde vervangen",{{"oid", Int64.Type}}), #"Waarde vervangen4" = Table.ReplaceValue(#"Type gewijzigd","'","''",Replacer.ReplaceText,{"commonName"}), #"Waarde vervangen5" = Table.ReplaceValue(#"Waarde vervangen4","'","''",Replacer.ReplaceText,{"subject"}), #"Waarde vervangen6" = Table.ReplaceValue(#"Waarde vervangen5","'","''",Replacer.ReplaceText,{"contentSummary","phoneLine"}), #"Aangepaste kolom toegevoegd" = Table.AddColumn(#"Waarde vervangen6", "AddSQLColumn", each "IF not EXISTS (SELECT * FROM Xelion WITH (UPDLOCK) WHERE oid = '" & Text.From([oid]) & "') INSERT INTO Xelion ( subject ,date ,durationSec ,status ,incoming ,displayed ,flagged ,hasAttachment ,hasInlineAttachment ,contentSummary ,contentSummaryIsComplete ,participants ,voicemail ,callId ,callFlowId ,phone ,isConferenceCall ,callAnswerTime ,callAnswerTimeSec ,callEndTime ,onHoldDuration ,trunk ,phoneLine ,wrapUpTime ,commonName ,oid ,objectType ) VALUES ('" & Text.From([subject]) & "' ,'" & Text.From([date]) & "' ,'" & Text.From([durationSec]) & "' ,'" & Text.From([status]) & "' ,'" & Text.From([incoming]) & "' ,'" & Text.From([displayed]) & "' ,'" & Text.From([flagged]) & "' ,'" & Text.From([hasAttachment]) & "' ,'" & Text.From([hasInlineAttachment]) & "' ,'" & Text.From([contentSummary]) & "' ,'" & Text.From([contentSummaryIsComplete]) & "' ,'List' ,'" & Text.From([voicemail]) & "' ,'" & Text.From([callId]) & "' ,'" & Text.From([callFlowId]) & "' ,'" & Text.From([phone]) & "' ,'" & Text.From([isConferenceCall]) & "' ,'" & Text.From([callAnswerTime]) & "' ,'" & Text.From([callAnswerTimeSec]) & "' ,'" & Text.From([callEndTime]) & "' ,'" & Text.From([onHoldDuration]) & "' ,'" & Text.From([trunk]) & "' ,'" & Text.From([phoneLine]) & "' ,'" & Text.From([wrapUpTime]) & "' ,'" & Text.From([commonName]) & "' ,'"& Text.From([oid])&"' ,'" & Text.From([objectType]) & "') "), AddSQLColumn = #"Aangepaste kolom toegevoegd"[AddSQLColumn], Stap = Lines.ToText(AddSQLColumn) & " select * from Xelion", Bron2 = Sql.Database("[server]", "[database"), Aangepast1 = Value.NativeQuery(Bron2,Stap) in Aangepast1
The second function gets all the records from the sql database that were added in the last two days but have a status that is empty. It seemed that when the data is extracted and a call was ongoing the records would exist but would not be complete and the status of that record would be empty. So this function would get the data of one single record that needs to be updated.
let Bron = (OID) => let BronKey = Sql.Database("[server] ", "[database]", [Query="Select * from Xelion_Key"]), Key = BronKey{0}[Key], Bron = Json.Document(Web.Contents("https://[internetaddress]/",[RelativePath="api/v1/[company]/communications?limit=1&after="&Text.From(OID-1)&"&include=all", Headers=[ContentType="application/json", Authorization="xelion "&Text.From(Key)]])), #"Geconverteerd naar tabel" = Record.ToTable(Bron), Value = #"Geconverteerd naar tabel"{0}[Value], #"Geconverteerd naar tabel1" = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Column1 uitgevouwen" = Table.ExpandRecordColumn(#"Geconverteerd naar tabel1", "Column1", {"object", "links"}, {"object", "links"}), #"object uitgevouwen" = Table.ExpandRecordColumn(#"Column1 uitgevouwen", "object", {"subject", "date", "durationSec", "status", "incoming", "displayed", "flagged", "hasAttachment", "hasInlineAttachment", "contentSummary", "contentSummaryIsComplete", "participants", "voicemail", "callId", "callFlowId", "phone", "isConferenceCall", "callAnswerTime", "callAnswerTimeSec", "callEndTime", "onHoldDuration", "trunk", "phoneLine", "wrapUpTime", "commonName", "oid", "objectType"}, {"subject", "date", "durationSec", "status", "incoming", "displayed", "flagged", "hasAttachment", "hasInlineAttachment", "contentSummary", "contentSummaryIsComplete", "participants", "voicemail", "callId", "callFlowId", "phone", "isConferenceCall", "callAnswerTime", "callAnswerTimeSec", "callEndTime", "onHoldDuration", "trunk", "phoneLine", "wrapUpTime", "commonName", "oid", "objectType"}), #"Kolommen verwijderd" = Table.RemoveColumns(#"object uitgevouwen",{"links"}), #"phoneLine uitgevouwen" = Table.ExpandRecordColumn(#"Kolommen verwijderd", "phoneLine", {"commonName"}, {"phoneLine"}) in #"phoneLine uitgevouwen" in Bron
The second query gets all records from yesterday and today which have a status that is empty and need to be updated and then the above function is used to get the updated data. A SQL script is created by using this data which updates every record and is run by using the nativequery function.
let Bron = Sql.Database("[server]", "[database]"), dbo_Xelion = Bron{[Schema="dbo",Item="Xelion"]}[Data], #"Rijen gefilterd" = Table.SelectRows(dbo_Xelion, each ([status] = "leeg") and ([objecttype] = "CallLog")), #"Rijen gefilterd1" = Table.SelectRows(#"Rijen gefilterd", each [date] <> "leeg"), #"Kolom gedupliceerd" = Table.DuplicateColumn(#"Rijen gefilterd1", "date", "date - Kopie"), #"Kolom splitsen op posities" = Table.SplitColumn(#"Kolom gedupliceerd", "date - Kopie", Splitter.SplitTextByPositions({0,11}), {"date - Kopie.1"}), #"Type gewijzigd" = Table.TransformColumnTypes(#"Kolom splitsen op posities",{{"date - Kopie.1", type date}}), #"Rijen gefilterd2" = Table.SelectRows(#"Type gewijzigd", each Date.IsInPreviousDay([#"date - Kopie.1"]) or Date.IsInCurrentDay([#"date - Kopie.1"])), #"Andere kolommen verwijderd" = Table.SelectColumns(#"Rijen gefilterd2",{"oid"}), #"Aangeroepen aangepaste functie" = Table.AddColumn(#"Andere kolommen verwijderd", "Data updaten", each #"Data updaten"([oid])), #"Data updaten uitgevouwen" = Table.ExpandTableColumn(#"Aangeroepen aangepaste functie", "Data updaten", {"subject", "date", "durationSec", "status", "incoming", "displayed", "flagged", "hasAttachment", "hasInlineAttachment", "contentSummary", "contentSummaryIsComplete", "participants", "voicemail", "callId", "callFlowId", "phone", "isConferenceCall", "callAnswerTime", "callAnswerTimeSec", "callEndTime", "onHoldDuration", "trunk", "phoneLine", "wrapUpTime", "commonName", "oid", "objectType"}, {"subject", "date", "durationSec", "status", "incoming", "displayed", "flagged", "hasAttachment", "hasInlineAttachment", "contentSummary", "contentSummaryIsComplete", "participants", "voicemail", "callId", "callFlowId", "phone", "isConferenceCall", "callAnswerTime", "callAnswerTimeSec", "callEndTime", "onHoldDuration", "trunk", "phoneLine", "wrapUpTime", "commonName", "oid.1", "objectType"}), #"Kolommen verwijderd" = Table.RemoveColumns(#"Data updaten uitgevouwen",{"oid"}), #"Namen van kolommen gewijzigd1" = Table.RenameColumns(#"Kolommen verwijderd",{{"oid.1", "oid"}}), #"Waarde vervangen1" = Table.ReplaceValue(#"Namen van kolommen gewijzigd1","","leeg" ,Replacer.ReplaceValue,{"contentSummary","subject","callId","callFlowId","trunk","callAnswerTime","callEndTime"}), #"Waarde vervangen" = Table.ReplaceValue(#"Waarde vervangen1",null,"leeg",Replacer.ReplaceValue,{"subject", "date", "durationSec", "status", "incoming", "displayed", "flagged", "hasAttachment", "hasInlineAttachment", "contentSummary", "contentSummaryIsComplete", "participants", "voicemail", "callId", "callFlowId", "phone", "isConferenceCall", "callAnswerTime", "callAnswerTimeSec", "callEndTime", "onHoldDuration", "trunk", "phoneLine", "wrapUpTime", "commonName", "oid", "objectType"}), #"Type gewijzigd2" = Table.TransformColumnTypes(#"Waarde vervangen",{{"oid", Int64.Type}}), #"Waarde vervangen4" = Table.ReplaceValue(#"Type gewijzigd2","'","''",Replacer.ReplaceText,{"commonName"}), #"Waarde vervangen5" = Table.ReplaceValue(#"Waarde vervangen4","'","''",Replacer.ReplaceText,{"subject"}), #"Waarde vervangen6" = Table.ReplaceValue(#"Waarde vervangen5","'","''",Replacer.ReplaceText,{"contentSummary","phoneLine"}), #"Aangepaste kolom toegevoegd" = Table.AddColumn(#"Waarde vervangen6", "AddSQLColumn", each "UPDATE Xelion SET subject = '" & Text.From([subject]) & "' ,date = '" & Text.From([date]) & "' ,durationSec = '" & Text.From([durationSec]) & "' ,status = '" & Text.From([status]) & "' ,incoming = '" & Text.From([incoming]) & "' ,displayed = '" & Text.From([displayed]) & "' ,flagged = '" & Text.From([flagged]) & "' ,hasAttachment = '" & Text.From([hasAttachment]) & "' ,hasInlineAttachment = '" & Text.From([hasInlineAttachment]) & "' ,contentSummary = '" & Text.From([contentSummary]) & "' ,contentSummaryIsComplete = '" & Text.From([contentSummaryIsComplete]) & "' ,participants = 'List' ,voicemail = '" & Text.From([voicemail]) & "' ,callId = '" & Text.From([callId]) & "' ,callFlowId = '" & Text.From([callFlowId]) & "' ,phone = '" & Text.From([phone]) & "' ,isConferenceCall = '" & Text.From([isConferenceCall]) & "' ,callAnswerTime = '" & Text.From([callAnswerTime]) & "' ,callAnswerTimeSec = '" & Text.From([callAnswerTimeSec]) & "' ,callEndTime = '" & Text.From([callEndTime]) & "' ,onHoldDuration = '" & Text.From([onHoldDuration]) & "' ,trunk = '" & Text.From([trunk]) & "' ,phoneLine = '" & Text.From([phoneLine]) & "' ,wrapUpTime = '" & Text.From([wrapUpTime]) & "' ,commonName = '" & Text.From([commonName]) & "' ,oid = '"& Text.From([oid])&"' ,objectType = '" & Text.From([objectType]) & "' FROM Xelion WHERE OID = '" & Text.From([oid]) & "'"), AddSQLColumn = #"Aangepaste kolom toegevoegd"[AddSQLColumn], Stap = Lines.ToText(AddSQLColumn) & " select * from Xelion", Bron2 = Sql.Database("[server]", "[database]"), Aangepast1 = Value.NativeQuery(Bron2,Stap) in Aangepast1
I created this file 3 times and published all to power bi service where I scheduled them all every half hour which means this process is run every half hour for 24 times during the day between 7 am and 7 pm.
