Create a Power BI Real-Time Semantic Model and update it with 1 new Record/Sec using Power Automate

hello everyone this is vahid and in this video I'm going to show you how to create a real time streaming data set or semantic model in powerbi and update that semantic model or data set with at least one new record per second by using a flow in power automate so let's create this solution together so first thing first is to create a flow on power automate what I want to create that flow because because I want to use that flow to generate some random values and use those values as a new record and update the semantic model or data set on powerp in this video I want to show you first how to create that solution that flow and create and generate those random uh values or data and update a real time streaming data set and at the end I want to show you how to save that record on a cql database and to update a table on that database and at the end I want to show you how to create a dashboard live dashboard by using that streaming data set or or semantic model or how to create a report and connect that powerbi report uh to the cql database by using a direct query connection and update update the visuals on that report per second have a live dashboard or report so first of all we we need to create a flow so in power automate I will click on Create and then select the schedule Cloud flow this schedule Cloud flow is useful if you want to repeat uh some actions in 1 minute 2 minutes or in some specific interval so I will add that as a test test demo to and real time dot uh time and date is okay I will leave it as is but for the interval repeat every I will select seconds and we add hundreds here I will tell you and show you uh during this video how we can select and update that interval duration and how we can find that how how many seconds we need to add here so during this video I will show you how to find that and how to update that one so press create when I press create the new window with the new uh design will be open with a co-pilot but I don't want to use the co- pilot the co- pilot is great it's super powerful but in this video I don't want to use that so I will switch that to the uh old design of the power automat so you can see the trigger is here recurrence here with the interval of 100 seconds you can select the time zone my time zone is Sydney Australia so I will select that one and that time and duration and time and uh date is okay I leave it as is so to create a sample data set and update that with one new record at least one new record per second I need uh data set I need a data set I need to Define some columns and values there so for that that data set I want to use the structure of uh this final uh Financial sample Excel workbook it's the free sample Excel file you can find it if you just search download the financial sample Excel workbook for powerbi you can find this Excel file I downloaded uh before and it's like this this Excel file has 16 columns from segment to year but I don't want to use all those columns I only want to use this three with text value this one and thison says and cogc with number values and date with date and time values I selected those uh columns have a simple uh data set with different column with different with columns with different type of values so eight columns here and I will return to this file again later but before that we need to add some actions and and to to create some uh to generate or create some random data for those columns so one column has a date and time which is okay very easy to write a formula to find the current time and time and uh time and date and save that in that column each time we want to run this uh flow those uh four columns with number value are easy to update because there is a Formula here in power automate a random formula and you can define a minimum and maximum and in each one power automate will pick one number between MA minimum and maximum and we'll use that to create a new record and save that on the dots but for those columns with text value that's a little bit different than the others because first we need to Define all values for those columns for example for Country we need to define the name of countries in a um array um uh format to Define okay there are six name of uh countries and then each time the power power automate need to select one of them randomly so we need three uh three variables with array uh with values of for those three columns and then we can randomly select use some formula and randomly select one of those values so first of all I will add one uh uh variable here initialize variable and I will name it as a we country for the country columns let me rename this as country and add R country and then the type of the values in this variable should be array and then the values here I can type it manually to say okay Germany and the second value is Australia two double quotations and then close open baret and close that one so this is the array with two companies and if I want select each of them uh I can use this formula so let me just uh first show that here so if I use compose and here um I can write a formula I can use variables and the name of that variable uh which is uh V country V country and I need to keep that between two single code and then between two barcades uh open and close baret I need to add a number number of the the if we add the index to all of those values consider that as a column with Australia Germany and USA in that column so if we added index there the first value we get the zero number and then after that 1 2 3 4 to the end so start from zero to the end so if I select zero here and save that one here the Germany is zero and Australia is one let me add USA as well USA and then so this is zero this is one and this is uh two here so okay so here um I added them there so let's test it save this one and test it manually test this one so it run and select the first item which is Germany in the list if I test that and change that to two then the third one which is the USA will be selected Let's test that one on that and you can see the USA has been selected so we can select that one and if we want to select that randomly we rather than a number we can use Rand random uh formula with minimum which is always zero and the maximum based on the number of the values in that array there are three values here so it start from zero so two is the maximum number save that one and if I save that run it again and you can see is randomly for this one it selects Germany which is the first one maybe the in the next one it select the USA or select Australia let me remove this compos I don't need it anymore that was just for testing and I need to add two more uh variable here in parallel because I have three columns with text value so I will copy this to clipboard and add in par and cboard I have this one this one and another one here and go this one this is for Country uh I will name change that for Segment segment and here because I want to use this names the first one is segment country and product okay in the segment array how can I find these items here you can type those items or values there or you can use this values here so easily you can paste it here and remove the D duplicate then you have all those values there are five unique values here this is the name of the colum I don't want to use that one but these are the five items if I if I want to convert them to array it's easy we can use table convert and I can say that has one um one row and five values five columns one row five columns and need to transer this one one copy that paste it here and then here if I select this second array then it will convert this table to an array so here I don't need to use this baret I only need this area I will copy and this area sorry from here to here copy this one and paste it here fine then then the country there are few countries here I type them there you can use some uh available list on net for example this one old name of count is here in Array format it easy just copy and paste but for for this this demo I only want to use the values from this sheet so copy paste do the same exercise again for Country column remove duplicates and there are five here just make it as a row and then paste it here so that done copy this one and paste it here and for the other one the last one which is the product product uh let's find all name of the products here cop past it here here move duplicate then copy uh past it here there are six items here I need to update this one to have six columns here okay and then paste my values here and boom I have the array copy that one and paste it here uh so I created those three uh variables for those column with Tex value so I name this one as let me change this one to segment this one country is okay and this is product because we need to use those items this is this is good that's done so now we have those items we need a data set a streaming data set in powerbi so in powerbi I in my Workplay I will click on streaming semantic model here is semantic model if I click on that one here is a still streaming dot set so so that's a new change in powerbi and there are still some areas with data set or semantic model but during this video I would call it sometimes the S set or streaming or semantic model so both are same so here I would click API click on next name that one as a demo test uh Real Time Value real time data and I need to Define all those columns first one is segment with text value that's good country text as well product text this oh unit the order of them are not important unit so that number discount number sales CC yes number and then last one date time and and I will select the DAT time format for that column at the end make sure to turn on this historic historic data analysis because if you don't do that there is no uh storage for that data set so I will create that data set here done here and I can now add rows to that data set so there is a action here add rows uh to data set here in powerbi section and I select my workspace which is demo the data set names I added that demo test real time data and the name of the table is always real time data for those streaming data set when I select that one all columns will be added here so here I need to Define and the I need to write some formula to randomly uh select some uh values select some uh values for those text values from this array and for those numbers to define the minimum and maximum and from them the power will pick one number randomly and for date and time the I want to use the current data and time so for Segment what I need to do in the formula again I will write variables the name of that is wi segment and then in here I need to write Rand random formula and I know this is start from zero five items there so the maximum is four C on Save that's there so let me open this items the 1 2 3 4 five items for is the maximum there for Country again same uh variables um we country country and then here random from 0o to four that four is because here is zero 1 2 3 4 so this is the minimum and this is the max so in there I Define zero as the minimum here and Max as the number here okay save that one for product the same variables we product and but the number of the values in that one is one more than the others so 0 to five because there are six values there say for the others is easy just use the formula uh click on this FX and here just use the Rand and then from for example unit from from 100 to 1,000 uh just random numbers I don't if those numbers are not uh meaningful is okay for me I just want to add an update add some values and update my DOA set here for Discount again a random so for this 100 for example from the th to 10,000 save say let me change this one from Z maybe there is no this and sales u i do the same from R from $1 $110,000 for example to 10 million and million yeah here for cgc I can add another one for for example th000 to 100 that's okay for date and time I save that for formula before so what I can do I can use um UT U UTC here if I write UTC here I can use UTC now or I can convert convert um UTC to another time but I'm pretty sure I save that formula somewhere to save some time I will copy paste that to here to convert the time to my location that's good see here so convert from UTC UTC now convert this time Australia Easter uh Standard Time and may use the use the this format yeah this one okay so here uh I will click on Save and that's done save that and before I run and test this uh uh this um low I want to create a report by using this data set the the demo test real time data is or real time streaming uh semantic model data set I want to create report on top of that one so here I will select table here and first I will select segment then country then product um unit so there discount CC and data time it's okay and I want to sort that by date and time save that as a test report demo so here what I want to do I first Let me refresh that and you can see there is one item here why because I added the 100 seconds here and if I back you can see that behind the scen see that flow is running because I set some um some time for that to run again and again so 100 second later the other items will be added here so that that is the time and then one and half a minute uh later one uh one new aord will be added to this uh this data set but it's okay because the segment is correct country is correct product okay number un need uh all values are okay are fine um I can run it manually to test it rather than waiting there so test this one random run it that's done and if I refresh my visual you can see another one has been added here with with new data let me just sort it the other way yes so Enterprise France new country new product and the numbers are randomly selected but this is good this is fine so it works very well but I want to add one record at least one record new record per second I don't want to wait for 90 seconds or 100 seconds and here I can't make it 1 second if I change that to 1 second and save that there is error here the the lowest amount or the is 60 seconds I can't go uh below 60 seconds so here it should be 60 even if I add 59 and try to save that there is an error here but if I add that 60 here that will be fine so 1 minute is the minimum time for this trigger what should I do I want to add some some tricks here I want to use some tricks I search on the net I couldn't find any good um solution for this uh challenge or this limitation so I I will show you this this method may be a little bit strange but it works it works really well without any issue so first of all what I will do I will add um another variable here as a iniz variable here and I will name name it as a for Loop here and Ray as well what I want to do I want to add let me just add another item here and explain what I want to do I want to add apply to each to create a first Loop here let me change that to First Loop here and move this add row to data set inside this Loop and I want to say Okay I want to wait 100 seconds but between that between the start to end of that uh duration I want want to add at least 100 new records it fast so I want to add apply to each for more than 100 items here because maybe in one second that uh that will that adds more than one rows one one row to my data set so what I can do I can add the array with 120 for this test I will add 120 uh ARR an array with 120 uh items here so I created that here the this file with with this now items the sample file is available on my website in the BL post I uh wrote for this uh solution so you can find it download it and just copy paste it here this array has 120 items let me create this one and here I will select apply to each for each of these items for item one run it once item two run it again three four and do it for 100 for uh for 120 times and this interval is still 100 change it to 100 so let's keep it here and back there for the next R I will wait there maybe there are more items here which is fine let me add another it another column here on number of rows here let me use add date and time here and and use it as a con okay so at the moment there are six items here and I wait for the next run so here that will a new Ron will appear here in less than a minute I will wait for that one yes that is start so if I run that one you can see new items in second will be added here and you can see that in each second for here four rows new rows added here and then three rows here each in that second this one three rows and that's too much for this type of data set because there are some limitation here and that will stop on here and it stopped six six items were there and we added 100 item and it stopped it doesn't fail here if I save that point it is still running but no it no new record will appear here so nothing will be added here why because of the limitation of that action add rules to the data set in powerbi and the limitation of the streaming data set we can't add too many records per second there so we need to keep that one or two records if you want to use the streaming dot set we need to I need to set some limitation here to add only one or at most two rows per seconds here otherwise I need to wait that won't fail this this will stop here for four to 5 minutes and then will run it again so for now I will turn it off and we cancel that manually okay here is cancel and I can see there are 106 records here but I want to for restart my data set so I will go to edit and that historic data on is off and again I will um turn that on that's a way we can we can uh restart or or streaming data set if I refresh that again no record there okay so the solution this flow works very well but I need to add uh some delay there to add the row there and stop a little bit 1 second or 30 millisecond and after that add the new records to to be below those boundary and the limitations be inside that boundary and then there shouldn't be any issue so here there is a delay action here but that has a limitation as well so the minimum items or minimum minimum amount here is 5 Seconds I can't go load 5 Seconds less than 5 seconds I will add I will face with an issue so that again doesn't work for me because I want to add at least one record per second so I need to do another I need to add another actions here uh I couldn't find again another uh a good way or a perfect way on the internet so I find this way which I want to show you now it's again little bit strange but it works very well what we need to do is to add another loop here so I want to define the variable add a number one to there and I want to add some calculation there so say Okay add the if you add the rows the data set in this first Loop go to the second Loop take reduce one uh from that variable which has only one it will be zero if it's equal zero exit the loop so that calculation and checking those numbers takes around less than a second so that's good for us so let's create that one first I will add another uh let me rename this one for Loop here variable again and initialize a variable I will name it delay and integer as a integer as a type of that and add one here so and rename that to delay here inside the first Loop apply to each I want to add another loop and this time I want to use this do until do until I want to say okay use the delay variable and when it's zero exit the loop let me rename that as a second Loop and then inside that I want to take one and to reduce uh reduce the amount of that because it's one I want to go to through this Loop uh reduce the one and take that one the one from that that variable and that will be zero and that and then it will uh Stop the Loop and exit the loop and go to the next items and add and new rows there so new row there so here I will use a variable and use this decent variable and say okay from that delay reduce one from that one so one Whatever number is here in the delay variable take one out and then check the check the value here if it's zero exit the loop if it's not zero do it again so the value is zero here what will happen it will in the first round it will come here so it's bigger than zero so it's bigger than zero it will go there so take one uh from that variable and it will be zero when it's Z we won't go outside of the first Loop to go there to set that one again so here we'll in the second run it will back here with zero and it's zero and it won't go through that Loop what we need to do we need to add another action before that decrement variable to set that variable to one so that should be always one so here delay one and I will put it put this one after that so set that variable one and then take that one uh from that variable in that case it always go through this Loop let me save this one maybe it's a little bit strange uh but it works let let me show you um it's at the moment nothing in this data set so I will make that on I don't want to run that manually because if I run that manually when it's uh processing that manual uh run maybe the next run will uh will started will be started and then there are some overlap between those runs and then that will touch the limitation again and it will stop I will wait for that to be started automatically so let's wait for because the total amount is6 is nothing we can wait for that one I will refresh that to find that when it's noted it so here oh it's a start so here if I back to my report I can see every second there is one record for that but I don't want to hit this visual refresh every time I can you can use this tool which is available for all browser Chrome um Firefox or uh the edge and by using that one you can set the interval for example 1 second and click on start and that will update the visuals every second automatically I don't need to hit any button anymore so here I can see the number of the rows or record for each second um is one or two is not more than that one so there shouldn't be any issue and that will add at least one record per second and then the next thing we need to check is to need to check how the how uh how many seconds or minutes that will take to finalize a to process a run each run how long does it take to be done so for example this one is still running it's uh for examp example now is 60 60 seconds and 100 records has been added have been added so now is 100 five 7 8 so no issue last time when we ran that one it stuck at 100 because it um it was over the limitations 120 is the total amount we want to be added in each run because the total amount of the for the loop is 120 so now it takes 1 minute and 20 seconds which is equal to 86 seconds now I can change this one to 90 seconds because each run will take around 90 seconds let's add one 2 second for uh to to to make sure there won't be any overlap so I can reduce that to 95 and save it if I back here the next run has been started so here it works very well still adding the values St adding values each second we will receive a new value so if I go to uh to my workpace I can create a new dashboard here and say test Dash and here I can use this streaming data set at the tile streaming data set and I can use demo test real time which is the name of my data set and here I can use can create line chart by adding the date and time and values sales and the legend as country the minutes data there and you can see if I make that um little bit bigger you can see every second there is a value here for new records here I can add another tile here for for example streaming next this one this data set and a card for example for the value of the discount it here and that will be updated as well so here let's check that one to done this one takes around um 95 seconds so next run will be started now because it's exactly the duration we have set for each run so 95 to 100 is good and it works at a new record here so you can test any scenarios um with this live streaming data set you can if you need something uh some scenarios because three weeks ago I wanted to test a scenario for monitoring a financial market and I couldn't find any sample real time data set with new record per second free free version of that on Market so I created this version so let me turn this off uh and show you um let me cancel this run as well and show you how we can um use um SQL database update that with some new records and then create a report with the requery connection and update that with that that report as well so here I have a report in this work space I have this report demo that demo that demo uh report is connected to my database to one table on my database let me turn this off still refreshing the visuals it's directly connected to that database but at the moment if I refresh that there is no record here so I want to create to change this flow and add row to that database and up then make that as a live uh live live report by adding at least one or more than one records per second there is no limitation but for using that uh SQL Server those limitations we had before uh where only for the push real time streaming data set on powerp so we don't need to use the second Loop for that scenario I can remove that and I can remove this delay as well and I can add another a new new action here cql if i s cql you can see there is a premium tag here that means you need to have a proper license in power automate to be able to use that insert row is the action in cql server that I want to use for adding and adding records to my table on the and the database so here I can select my database here and database and you can see that DOA that table and that database has exactly same columns that's fine can copy paste these formulas to that here okay so I can remove this add rows to a data set because I don't want to update that do data set anymore I want to update my SQL L save that one back to my report if I refresh that there is no record here and last one was few hours ago so back to my flow and I want to turn that on when we use the SQL SQL database there is no limitation so we can add many records per second but those limitations I explained before were only for semantic model real time streaming data set or semantic model so I will wait for this run here it is start running so if I back to that report and start refreshing that you can see it is start to be updated so I can set use this tool again uh web browser extension the name of that is powerbi visual Auto refresh if you search it it's available for Chrome age and Firefox set it on 1 second and start and you can see now it updated with one one or more than one record per second here you can see that number of Records here and it shows all values here there is no limitation so if two runs have or three runs happen concurrently shouldn't be any issue and by using this um tool you can have a live data report by using SQL database and connect this report to that um database with the query connection okay hope you like this video and use this method to create some live data set or semantic model or live cql database and create many Dash boards and report and test your scenarios if you like this video please make sure to hit that like button subscribe my channel until next video bye for [Music] now oh

Channel: Data BI

Views: 4,192

Keywords: Power BI, Power Automate, Semantic model, Dataset, Sample Data source, Live data, Real Time Streaming, Report, Database, SQL

Id: hdjyLon-hzk

Length: 41min 28sec (2488 seconds)

Published: Mon Dec 11 2023