Microsoft Data & Pow­er Plat­form year 2022 sum­ma­ry and view to hori­zon — with Islet’s data & ana­lyt­ics team’s binoculars

Let’s dive into the world of Microsoft Data Plat­form and Pow­er Plat­form, and specif­i­cal­ly the events and most impact­ful releas­es in our view, Islet’s Data & Ana­lyt­ics team mem­bers Ilk­ka Ellilä and Mikko Antti­la. How do we lever­age Microsoft cloud ser­vices to sup­port our cus­tomers’ busi­ness and suc­cess? What are the ben­e­fits and how can they be uti­lized in dif­fer­ent imple­men­ta­tions? What are the most antic­i­pat­ed new fea­tures in these ser­vices in the near future and why?

We both have vast expe­ri­ence in using the Microsoft ecosys­tem in data ware­hous­ing projects, cod­ing expe­ri­ence from the past and we both have been Microsoft cer­ti­fied in areas of our per­son­al inter­ests. Mikko has been spe­cial­iz­ing into the Data Plat­form side, and Ilk­ka gain­ing knowl­edge of the fast-grow­ing part of low-code devel­op­ment in Pow­er Plat­form and how it can be har­nessed to boost effec­tive­ness in the Data & Ana­lyt­ics envi­ron­ment and stream­line busi­ness users, data engi­neers or ana­lysts’ workflows.

Year 2022 was full of new releas­es in Microsoft Data and Pow­er Plat­form areas. Thoughts were thrown around in Teams about new fea­tures and we came close to test­ing them all… Here are our most impor­tant findings:

POW­ER APPS

Pow­er Apps had some great releas­es in 2022 regard­ing espe­cial­ly the devel­op­ment of Pow­er­Apps Can­vas app, but first I think I should cast some light on Pow­er Plat­form devel­op­ment with­in data ware­hous­ing envi­ron­ment in general.

• Pow­er Plat­form pro­vides all the tricks in the book for data automa­tion, inte­gra­tion via con­nec­tors and even cer­tain arti­fi­cial intel­li­gence tasks.
• Data Ware­hous­ing lacks often a good way of imple­ment­ing cus­tomer dri­ven data input. In most cas­es “the only way” has been Share­Point or Excel which are great for what they’re intend­ed for, but han­dling long lists of user input data will cause prob­lems at some point of a data ware­hous­ing project.

When Pow­er Plat­form is need­ed in projects, we use Pow­er Apps Can­vas with SQL con­nec­tor and Pow­er Auto­mate (Flow) in most cases.

Best or most dis­cussed things (2022) — Pow­er Apps

• Search and Find and Replace

Search and Find and Replace in gen­er­al might sound not that new and ground break­ing, but in fact, the Pow­er­Apps Can­vas app was lack­ing just that. It changed renam­ing, code check­ing, vari­able han­dling, them­ing etc. from agony to easy-mode. It has real­ly made the whole devel­op­ment expe­ri­ence way better.

• AI in general

AI was the big top­ic at the end of year 2022. AI seems to be the answer for everything.

Even Pow­er­Apps Can­vas had its share of AI. Now you can code even less with Pow­er Apps Ideas. Ideas enables the devel­op­er to just write the want­ed abil­i­ty that Pow­er­Fx should do and Ideas pro­vides the com­ment­ed code for it. I have not per­son­al­ly test­ed this abil­i­ty broad­ly because my most used Pow­er­Fx com­mands are already quite clear — although if there were a lot to write and the use-case was pre­sent­ed in clear man­ner for Ideas, it could lead to even less time spent cod­ing… These are still kind of mind-bog­gling abil­i­ties but clear­ly the future of coding.

Also, if you decide to code your­self, then it can help fix­ing the prob­lems with For­mu­la Repair (pre­view)

• Small changes in envi­ron­ment handling

Envi­ron­ment group­ing and changes in keep­ing the envi­ron­ment active is pret­ty handy when work­ing with many dif­fer­ent envi­ron­ments and it real­ly helps with the work­flow. There was noth­ing more frus­trat­ing than to des­e­lect the Default envi­ron­ment every…, sin­gle…, time, when log­ging into make​.pow​er​apps​.com.

Things to look for­ward to (2023) — Pow­er Apps

• Mak­ing team­work eas­i­er with Co-author­ing, Git and commenting

Usu­al­ly, Cod­ing project in gen­er­al does include mul­ti­ple devel­op­ers doing simul­ta­ne­ous devel­op­ment work. Co-author­ing fea­ture and Git repos­i­to­ry use can help with this. Git inte­gra­tion helps with Can­vas app and Mod­ern app.

With Can­vas app you will be able to use Git and there­fore make use of Git’s pow­er­ful fea­tures while devel­op­ment. Mod­el dri­ven apps will have an Office-like “Coau­thor­ing” expe­ri­ence where you see every change made by your co-work­ers in real-time.

Com­ments are great way to add notes and ques­tions about the code and share infor­ma­tion inside a team developers.

• Named for­mu­las

Every­one doing Can­vas devel­op­ment knows App.OnStart and what it can do and what cer­tain prob­lems it can have regard­ing to the start-times of the app. Usu­al­ly the prob­lems appear when hav­ing mul­ti­ple glob­al para­me­ters set-up on appli­ca­tion start. If you are fetch­ing data from the data­base lay­er, and set­ting that data to para­me­ters, then the data down­load times may cause delays is actu­al appli­ca­tion start.

Named For­mu­las will stream­line the use of basic para­me­ters by set­ting those as named for­mu­las. When using the for­mu­la in your appli­ca­tion it will have the val­ues ready to go but load­ing those won’t affect the actu­al appli­ca­tion start.

• App from image — UI cre­ation with great possibilities

You can cre­ate a work­ing app UI from your team’s plan­ning white­board by just tak­ing a pic­ture and upload­ing it. This fea­ture is at a “fun demo” stage at the moment, but it can real­ly become some­thing in the future. Tried it and yes, it cre­at­ed the app, but some texts were not read cor­rect­ly, and some box­es were missed. Did my best to have as basic box­es and font as I could. For demo pur­pos­es I will most cer­tain­ly keep this in my playbook.

And if you real­ly want to head start plan­ning your UIs with some­thing else than Can­vas app Design­er, with Fig­ma files you can cre­ate the app’s every screen. UI spe­cial­ists can still use their Fig­ma and you can just ask them to cre­ate a new cool UI.

• Man­aged Envi­ron­ments pipelines

Auto­mat­ed deploy­ment pipelines have been used in devel­op­ment projects to auto­mate the solu­tions deploy­ments into the new envi­ron­ments. Most impor­tant aspect has been improved vis­i­bil­i­ty about the sit­u­a­tion between envi­ron­ments and the his­to­ry of deployments.

Pow­er­Plat­form pipelines will be its own instal­lable appli­ca­tion that you install into the select­ed envi­ron­ment. With the app you can man­age all the envi­ron­ments and see the audit logs, deploy­ment activ­i­ties, access errors and much more.

POW­ER BI

Pow­er BI devel­op­ment is and will be an impor­tant part of Data & Ana­lyt­ics teams’ work. Mod­els, mea­sures, and visu­al­iza­tions do not hap­pen by them­selves, although AI can nowa­days make nice sug­ges­tions, but still good ana­lyt­i­cal insights into the data con­text are need­ed to make report­ing solu­tions work correctly.

There aren’t many as fast evolv­ing report­ing envi­ron­ments as Pow­er BI around, so keep­ing up with new fea­tures is important.

Best or most dis­cussed things (2022) — Pow­er BI

• Com­pos­ite mod­els upgraded

Cre­at­ing Power­BI datasets has not always been as flex­i­ble as now, and more improve­ments are com­ing. The com­pos­ite mod­els have proven to be good way to imple­ment datasets com­bin­ing once-a-day-refresh­ing, “data ware­hous­ing” data, and dai­ly chang­ing user input (Pow­er­Apps etc). Look and feel are that of a real-time report with this “wow” fac­tor. We’ve used this for many datasets and it always strikes me as “look, how cool is this?”

The fea­ture has more releas­es com­ing and now we are able to use the Azure Analy­sis Ser­vices mod­els in com­pos­ite model.

• Dataset refresh can­cel­la­tion now avail­able in Pow­er BI Premium

Refresh­ing a big dataset can be time-con­sum­ing. In Pow­er BI Pro work­space the max­i­mum dataset size is 1GB and usu­al­ly that max­i­mum capac­i­ty is not met, or you would be real­ly stretch­ing the capacity.

In Pre­mi­um capac­i­ty the datasets can be a lot big­ger, there­fore if you had miss-clicked a dataset refresh, the pro­cess­ing can take ages. It is very con­ve­nient to be able to can­cel the cur­rent dataset refresh.

Things to look for­ward to (2023) — Pow­er BI

• Azure Analy­sis Ser­vices auto­mat­ed migra­tion to Pow­er BI

Pow­er BI Pre­mi­um has been adding fea­tures into its datasets and is now pret­ty close to Azure Analy­sis Ser­vices. Microsoft announced an auto­mat­ed migra­tion tool that allows users to migrate their Azure Analy­sis Ser­vices mod­els into Pow­er BI datasets. Lat­er they will add a Pow­er BI report rebind tool, but for now the report rebind­ing must be done manually.

With mod­els in Pow­er BI Pre­mi­um you have a cen­tral­ized place for the data and enjoy cost-relat­ed benefits.

• Cross-ten­ant Pow­er BI Dataset Sharing

Soon you can cre­ate datasets in the Pow­er BI ser­vice and then share those to users in a dif­fer­ent ten­ant. It has a lot of real-world use cas­es for exam­ple in busi­ness-to-busi­ness data shar­ing, and data col­lab­o­ra­tion also offers busi­ness cas­es for new features.

AZURE DATA PLATFORM

Azure Data Plat­form is the back­bone of a data ware­hous­ing solu­tion. Con­tin­u­ous fea­ture upgrades and new ways to imple­ment work­ing solu­tions will keep devel­op­ment work com­pelling and meaningful.

Best or most dis­cussed things (2022) — Azure Data Platform

• Azure Machine Learn­ing — Auto­mat­ed ML

Auto­mat­ed ML pro­vides a user less expe­ri­enced with fore­cast­ing mod­els, some good tools to offer usable, code-com­ment­ed, mod­els. It won’t remove the need for data sci­en­tists because the mod­el is just a start that requires tweaks and mak­ing sure that the fore­cast­ing mod­els are opti­mized for more accu­rate or com­plex use cases.

After test­ing the fea­ture, I was amazed as there were a large num­ber of mod­els and each mod­el could be giv­en a com­ment­ed ver­sion if desired. On the whole, every­thing was well doc­u­ment­ed, only some undoc­u­ment­ed fea­tures remained.

• Azure Data Fac­to­ry UI changes

One of the most notice­able new things in Azure Data Fac­to­ry was that the val­i­da­tion for cer­tain com­po­nents were made more intel­li­gent. Once you could use some com­po­nents nest­ed and con­tin­ue devel­op­ment until the first time you tried to debug the run, and only then it would state that that is not the sup­port­ed way to use the com­po­nents. And then you would need to refac­tor the whole thing again. Now you can’t real­ly use the com­po­nents in the way they are not sup­port­ed. Also one wel­comed improve­ment: default time­out was changed from 7 days to 12 hours.

• Synapse con­nec­tiv­i­ty improved — Synapse Link for SQL

The fea­ture offers near real-time data move­ment from SQL (Azure SQL Data­base or SQL Serv­er 2022) to Synapse Ded­i­cat­ed pool. That is great for fast report­ing needs. You can even have some dai­ly oper­a­tional report­ing done via your data ware­hous­ing envi­ron­ment as you can see the “cur­rent” — almost live — sit­u­a­tion on reports when con­fig­ured cor­rect­ly. Of course, this doesn’t remove the need for the actu­al report­ing mod­els and inte­gra­tions of a data ware­hous­ing envi­ron­ment, but gives some rope on how often the data can be refreshed.

Things to look for­ward to (2023) — Azure Data Platform

• Vir­tu­al net­work (VNet) Data Gateway

Fea­ture still in pre­view and only avail­able for Pow­er BI Pre­mi­um ver­sion. Data gate­way cre­at­ed a Secure con­nec­tion to VNET and removes the need for On-Prem gate­way when con­nect­ing to resources on pri­vate VNET. This makes con­nect­ing a lot eas­i­er and removes most of the admin­is­tra­tive work need­ed to keep the gate­way running.

• SQL DB pro­ce­dure REST API

Trig­ger­ing basi­cal­ly any REST API from your Azure SQL DB is now pos­si­ble and this offers so many ways of using pro­ce­dures etc. There is a list on the doc­u­men­ta­tion page that states all the allowed end­points, but you can also expose your own API. Of course, you can use func­tion app or some­thing like Pow­er Auto­mate runs that are exposed as REST.

If you want to invoke a REST ser­vice that is not with­in the allowed list, you can use API Man­age­ment to secure­ly expose the desired ser­vice and make it avail­able to sp_invoke_external_rest_endpoint

• Time-To-Live (TTL) in man­aged vir­tu­al network

This fea­ture is still in pre­view, but it is a must-have for every­one using the Man­aged Vir­tu­al Net­works in Synapse. Before this fea­ture, resources had to be fetched for each call with­in the loop sep­a­rate­ly. Now, Time-To-Live fea­ture can be used to assign resource a life­time that deter­mines how long it will be reused for. The ben­e­fit of this is big when, for exam­ple, copy­ing func­tions are run in a large loop, and the resource need­ed for copy­ing is not always recre­at­ed, but an exist­ing one is used.

Nat­u­ral­ly, Microsoft is con­stant­ly devel­op­ing its solu­tions, and more and more fea­tures are being added all the time. So we stay alert and always ready to learn, and for a good rea­son – the ben­e­fits they bring can be big. 2022 was a great year in this con­text, and we can’t wait for the next year.

Best regards,

Mikko and Ilkka

Ps. More excit­ing things are com­ing up next year from us, so stay tuned!

Pps. Below some enthu­si­as­tic vibes from Microsoft­’s Part­ner Archi­tects Day. Best thing over­all about 2022 was to be able to meet and greet peo­ple in real-life.