Full transcript
0:00Uh, cool. I'm Ashley. Uh, I'm going to
0:02talk about CRCD in PowerBI. Um, so the
0:06tagline being for uh for when hitting
0:08publish isn't good enough. So, we're
0:10going to sort of work up to that. But
0:12before I do get to that, I just want to
0:13give a bit of an introduction to myself.
0:15So, I started Oh god, all my animations
0:18aren't working. I started as a finance
0:19analyst um training accountant 2017. And
0:22then somebody in the BI team showed me
0:24PowerBI and that was it. um quit quit
0:28being an accountant, went and got a job
0:29working with PowerBI and have been
0:31working with PowerBI ever since. Um so
0:33started with a small business, then went
0:34to a slightly bigger one and now I work
0:37for Advancing Analytics um as a senior
0:40consultant. So we work with really big
0:42organizations, big data teams. Um we
0:45also work with some small companies as
0:47well, but yeah, lots of big
0:49organizations.
0:50Um and as a little plug, I help organize
0:53and run the version of this group in
0:55Bristol. So, if you're ever down south,
0:57please come along. We're always looking
0:58for speakers. We're always looking for
1:00attendees. So, um yeah, I won't plug
1:03that anymore. Oh, that except for it's
1:05now disappeared. Um but the general
1:07overview of my career is a bit more like
1:09this. So, I started as sort of Excel
1:12heavy and then sort of moved through and
1:14went from like beginner to intermediate
1:16to to advanced. And I'm telling you that
1:19because I'm going to focus on this bit
1:20of just the PowerBI section going from
1:23beginner to advanced and try and use
1:24that journey to explain what um like
1:29what CI/CD options exist in PowerBI
1:32through the lens of sort of what I've
1:34learned over the course of my career. So
1:35hopefully you'll be able to take
1:37something away from this no matter where
1:39you are across that spectrum or where
1:42you think you sit in there. So that's
1:44the aim. Um lots of stuff to cover. So
1:47we'll crack on uh with beginner
1:49developer. So
1:51my first role in PowerBI um I was the
1:54only person using PowerBI in the
1:56business. I put it into like installed
1:58it and started using it from there. And
2:01so wasn't super mature because I was
2:04brand new as data person. And so I
2:07developed all the reports and models by
2:08myself. Saved all of the PBI access to
2:11SharePoint. content was deployed using
2:13the publish button and obviously that is
2:16then quite difficult for you to review
2:18your work and make sure that it's right.
2:20Um and eventually as the team grew from
2:25me to a team of three of us working on
2:27reports, it became quite clear that this
2:30just doesn't scale. It doesn't work
2:32unless there's only one of you. And even
2:34then it's not great when there's only
2:36one of you. Um and so why? Well, human
2:40errors firstly, everyone makes mistakes.
2:43Um, when there's a bunch of you working
2:45in the same in that way, that really
2:47simplistic way. Um, there's nothing
2:50there to catch or prevent mistakes. Um,
2:54testing is difficult. Um, like it's very
2:57very manual if you don't have something
2:59around. Um, we found that we were having
3:01lots of inconsistencies, things getting
3:03put into production that weren't ready
3:06and change management. Yeah, there was
3:08very little change management to that
3:10process as well. So, um everything was
3:13really difficult, right? There wasn't
3:15anything there to support us as
3:16developers. We were just wild westing
3:19everything effectively. So, I can
3:22imagine there's a few people in the
3:23audience who sort of vibe with that.
3:25Hopefully, you've got a better um
3:26process in place where you work at the
3:28moment, but if not, I'm going to
3:30hopefully you recognize these challenges
3:32and you'll be able to start seeing what
3:35more you can do or what you can use. Um,
3:38we did get a bit better at this place.
3:39It wasn't all terrible all the time. I'm
3:42just sort of highlighting the negatives
3:43there. We did get better as I was
3:46working there, but
3:48sort of getting more towards that
3:50intermediate level. So stepping up from
3:52beginner what as I moved from my first
3:55company to a large UK university we had
3:59access to PowerBI premium but um we
4:02started doing things a little better
4:04there they were already doing things a
4:05little better but what did I learn
4:08during that role well firstly time I
4:10ever heard the phrase CI/CD um and
4:12started learning why that's important
4:14and getting into PowerBI deployment
4:16pipelines and all of the various things
4:18built into PowerBI as standard
4:21Um so what is CI/CD? Well, it's a it
4:27stands for continuous integration and
4:29continuous deployment. Um and it comes
4:31from software engineering originally,
4:33but it's been applied to data
4:35engineering and even analytics
4:36engineering now. And a lot more we're
4:38taking we're taking a lot more of the
4:41the benefits from it from there. And the
4:43idea is that when you start working on a
4:46new like um a new project, you're going
4:48to plan it. you're going to probably
4:49write the code, build the code, and then
4:51you'll test it, you release, you deploy,
4:54you operate, you monitor, and you have
4:55this cycle, this continuous cycle that
4:58you're flowing through. Um, and CI/CD is
5:01the framework that that helps you to
5:04automate away some of the some of the
5:06steps of this. So, it's got things like
5:09automated version control. So, thinking
5:11about how you deploy or add changes or
5:14make changes and commit them in to your
5:17to your repo. um thinking about things
5:19like um automated deployment as well. So
5:23how do you go from test to release and
5:25making sure that everything is approved
5:27before you go there. Um and uh the idea
5:30is that it enforces good change
5:33management. It will help you to make
5:35sure that the right things are getting
5:38into production. So
5:41what options do we have natively in
5:43PowerBI for this? Um the first bit came
5:48out a couple of years ago or so.
5:50Workspace integration in fabric. The
5:52idea that your PowerBI workspace you can
5:54link that to a git repository whether
5:56that's GitHub or Azure DevOps wherever
5:59you store your repository. Um but git is
6:02super powerful. Lets you do all sorts of
6:05things. Um but the things that are most
6:06important is you can track the changes.
6:08You can make sure that like there is
6:10proper version control there so that um
6:14as people make a change to a report you
6:16can sort of see what's going on what
6:18individual elements are changing and
6:20then have proper pull requests and uh
6:22process there to make sure that that is
6:24managed and organized.
6:26Um there is the PowerBI uh project files
6:30pit file format. So before we had these
6:32PBXs that were one big monolithic file
6:35that had your semantic model and your
6:37report all together as one file. Now
6:39there's the PowerBI project file which
6:41kind of splits that down into more
6:43granular things. So there is the
6:45semantic models which is run which is in
6:48a pip file is stored in tindle format
6:50which Ricardo is going to talk about
6:52more later so I'm not going to too tell
6:54you too much about that. And then
6:55there's the um the PBIR file format for
6:58reports which is breaking down that big
7:01PBIX into a a series of JSON files. Um
7:05and and so that you can sort of control
7:08each individual even down to like an
7:10individual visual individually within
7:12your report. Um, and so when you combine
7:14these two things together, you've got
7:16super granular granular um, definitions
7:20of your model and your report um,
7:22combined with the ability to track how
7:24everything is changing, revert it
7:26changes over time. Um, so they work
7:28really well together. And then the last
7:31thing that's sort of natively there that
7:33to help with this CI/CD process, and
7:35this is a a premium feature, is
7:37deployment pipelines. Um so the idea
7:41there is that if you've got different
7:43workspaces for de development test and
7:45prod separating um different users or
7:48different stages of the life cycle so
7:50that you your prod is your production
7:53data and your development might be the
7:54stuff that you're just test like just
7:56building out. Um deployment pipelines
7:59can help you move um reports and models
8:01across the life cycle across those
8:03different workspaces.
8:05And I've got a little demo of them. uh
8:08of a deployment pipeline. Hopefully,
8:11it's not popped up. Uh it's on the wrong
8:14screen. That's great. Uh here we go. Uh
8:17I am not intending to do so much
8:19branding for all of you. Um it's uh just
8:22my work laptop and it has gone super
8:24super small for some reason. Uh where is
8:27my
8:29where's the little bit at the bottom? It
8:31seems to have zoomed in super far,
8:36right? Nope. Uh, let me just refresh
8:38this.
8:42See if the little thing at the bottom
8:44pops up. It didn't. Okay. So, this is
8:48three workspaces. Um, development, test,
8:51and prod. Um, they're called different
8:53things. This one is ash, demo, dev, ash,
8:55demo, test, ash, demo. Um, and this
8:58little bar at the bottom, we're
8:59currently clicked on development. You
9:01can see all of the items that are in the
9:03development workspace. Um if we go to if
9:06I click on the test um bucket um you can
9:09see there's different things in there
9:11now. So we've got um the sales dashboard
9:15and sales dashboard semantic model is
9:16still there but it's there's a report
9:18that was only in the source called the
9:19old sales report. And then if we go to
9:22production you can see something
9:23different there. So you can see there is
9:25still that sales dashboard uh report and
9:28model but there's also an original
9:30profit um uh reporting report in there.
9:33And you can sort of see across here
9:35where it says it's not in the source.
9:37What I'm going to do, uh, if I have up
9:40here, I've got the version of the report
9:43in dev and the version of it in test.
9:47And then I have got my
9:49pix is really difficult to see where the
9:52mouse is. I've got the Okay, this one
9:57here. Uh, it's come down to the bottom
9:59screen. Helpful.
10:01Let's drag that up here.
10:08Okay. Um, so this is a new version of
10:10the report that I've I've built in uh in
10:13PowerBI desktop. Um, it's the same one.
10:16It's just got this extra page called
10:17trends on it. Um, and I'm going to
10:20publish this uh where's publish file.
10:24Publish here into the workspace. Oh, I
10:27haven't saved it. Publish. And it is
10:30this one here.
10:32I know I've said that we're going to get
10:33rid of the publish button. We've not got
10:35to that date yet. So, for now, we're
10:36just hitting the publish button. Um, and
10:38this is going to put this report with
10:40its new page into
10:44the Okay, that's successful into the
10:47development
10:49uh into the development workspace. So
10:52I'll refresh this one and you should see
10:54on the left there's now going to be this
10:56navigation bar and there's a second page
10:59profit trends. If we refresh our
11:01pipeline at this point
11:05and it's obviously got rid of this bit
11:08at the bottom. Uh if I click on the de
11:12this is the development workspace. It's
11:13still got the the sales dashboard
11:15report. If I click on the test workspace
11:17you can now see it's saying it's
11:19different from source here. So the old
11:22the version in the test workspace still
11:25this old version with only one page. The
11:27version in the development has got two
11:29pages. Well the de deployment pipeline
11:33we can set this up so that only certain
11:35people can use a deployment pipeline but
11:37those people could go in and say I'm
11:39going to highlight this report and I'm
11:41going to deploy it from development into
11:43production.
11:45Now it's deploying successfully
11:47deployed. So let's go to the test
11:48version. Hit refresh
11:52and our second page has now appeared
11:55here. So this is a deployment pipeline.
11:58They're great because they they mean
12:00that you can you can isolate different
12:03workspaces from each other. You could
12:04set up the test workspace so nobody can
12:07deploy things into there. Nobody's got
12:09access to publish reports directly into
12:12there. The only way to get your report
12:14into there using this deployment
12:15pipeline. Um, you can also do things
12:17with these like, um, if you're using a
12:20semantic model, you can set it up so
12:21that as you move across different
12:23workspaces, you can change the data
12:26source from one thing to another. So the
12:28file in this one is saved in this
12:30SharePoint. Maybe I've got a new
12:32SharePoint which has a bunch of G's in
12:34it for um, you can set that up so that
12:36when you move things from development
12:39into test, it automatically updates the
12:42the the source for you. Um, you can also
12:45include um, so you've got the option in
12:48here to have an app for the workspaces.
12:50So every workspace can have a an app.
12:53You can publish your app and update your
12:54app from the deployment pipeline. It's
12:57not part of the sort of uh, it's not as
13:00it's not as nice in the UI. Can do it.
13:02Um, if we look at the production, we can
13:05now see there is it's still got that
13:08idea that this report is not in
13:10production. It's only in test. Whereas
13:12in test it's the same as development. So
13:13you can sort of it's very small but
13:15there's a little one on the on the test
13:17which is saying there's one thing that's
13:19different and there is a little two on
13:21production which is saying there is two
13:23things different with this with this
13:25workspace. Um that's pipeline deployment
13:29pipelines they are great um as a sort of
13:35a first step towards this getting
13:37towards a slightly more robust process.
13:39You could have this set up so that only
13:40one person can do what I've just done
13:42and that person then is can review the
13:45work the changes and make sure they're
13:47happy with them before you put them in.
13:48So combining this with some sort of good
13:50process in your team means that you can
13:52start improving your um ability to
13:55control what's getting into production.
13:58Uh let me go back to my slides if I can
14:01find my mouse. Uh
14:05slides.
14:07Okay.
14:11Okay. So that's the sort of intermediate
14:14sort of options. Um what I'm going to
14:17start covering now is a bit more
14:20advanced. So what things look might look
14:23like if you work at an enterprise grade.
14:25So as that intermediate type level stuff
14:28that I was talking about here that was I
14:29was working at a a business with a
14:31relatively small data team. A lot of
14:33data but small data team. Now I'm going
14:36to sort of talk about what I've learned
14:39while I've worked at Advancing. At the
14:40moment I'm working with a client that's
14:42got about 30 engineers. It's got a
14:45central data team, PowerBI team of about
14:4815 developers and then more out in the
14:51business as well. The first thing you
14:53learn when you start seeing that is that
14:56when you work at scale with those huge
14:58teams and there's so many things going
15:00on, it is nearly impossible to control
15:02what or know what's changing or control
15:04what's going on if you've not got a
15:06robust process around it. So, um having
15:10a big team, a big organization, you get
15:12lots more done obviously if there's like
15:1420 or 30 of you, but it's much harder to
15:17control and make sure that the quality
15:18level is at the right level. Um, so
15:23you need different processes for it.
15:25What else did I learn in this role?
15:26Tabular editor. I am a massive tabular
15:28editor fanboy. Um, it is amazing. We're
15:31going to cover that in a second. Um, I
15:33learned about DevOps pipelines and about
15:36the fabric API. So, this is what we're
15:37going to cover in this session.
15:39First, Tabular Editor. If you take one
15:42thing away from this talk, it's not even
15:43CI/CD related. Go and download Tabular
15:46Editor. It's free. Um, or at least there
15:48is a free version, Tabular Editor 2.
15:50There's a paid for version as well if
15:51you like the free one. Um the paid for
15:54one is even better, but it's got a bunch
15:56of cool features. Um so things like you
15:58can batch edit measures in your report.
16:01You can do it unlocks some of these more
16:03advanced data modeling things. They are
16:04starting to be added into the PowerBI UI
16:07now, but they've been in tabular editor
16:08for years. Um and then you've also got
16:10scripting. So you can automate things
16:12that you're changing in your report. So
16:13you can write scripts that create a
16:16bunch of measures for you. So if you got
16:1760 columns and you want to like create a
16:19sum measure for each of them, there's
16:21you can you can make get chat gbt to
16:23write the C# script that can do that and
16:25you can run it through tabular editor
16:27and make 60 measures all at once which
16:29is crazy. Uh and it is part of it is
16:32recommended or it is at least mentioned
16:34in DP600 um which is Microsoft's fabrics
16:37analytics engineering um exam. But my
16:41favorite feature of tabular editor and
16:42this is sort of related to CI/CD uh is
16:45best practice analyzer. This is like a
16:48amazing lifesaver of a tool for me. Um
16:51it is so powerful. The idea is that
16:54Microsoft Michael Kowalsski at Microsoft
16:56has released a list of um best practice
16:59rules that your semantic model should
17:00follow and best practice analyzer let
17:02you download those rules and scan your
17:04semantic model and check against them.
17:06So, for example, this is really small
17:08here, but it's finding things with this
17:11semantic model, which is the the report
17:13that I showed you before, which I
17:14purposefully left in. There's uh 200 odd
17:17problems with it, but it's things like
17:18I've not put formatting strings on my
17:20measures or I've I've got too many power
17:23query transformations, I've got columns
17:25that aren't being used, etc. So, um this
17:29is really cool. Um, the best thing about
17:31it is you can if you start scanning your
17:34semantic models with this before you
17:36publish them, you can make sure that
17:38they're of a certain level, right? So, I
17:41wouldn't publish something if there's a
17:42severe problem that that tabular editor
17:45is flagging. Now, um, and you can do all
17:47of this like really easily um, with
17:49tabular editor. Go and go and try this
17:52out. Um, the other thing that I think
17:55tabular editor does which is really good
17:57to CI/CD is the file format. So, I
18:00mentioned TIMDLE. Ricardo is going to
18:01mention it a bit more, but I don't um I
18:04don't like TIMDLE as much as I like
18:06Tabular Editor's version of it. Um
18:09because the tabular editor file format,
18:13which is called the save to folder, um
18:15let you take your semantic model and
18:17save it down into a bunch of different
18:20individual files. So, this is that
18:22semantic model that I mentioned. You can
18:23see there's tables, there's expressions,
18:25relationships, and we can drill into the
18:27tables. That's my tables. That's this is
18:30the sales folder. There's columns and
18:31partitions which partitions will include
18:33your power query transformation in
18:35there. And then this is all of the
18:37columns um in uh in that table. Tim has
18:41all of that as all of this oh all of
18:44this stuff as one page. Uh sorry as one
18:47file. Tabular editor breaks it down into
18:49lots of individuals. Um so each of these
18:52is different columns in there which all
18:54of them have individual bits of
18:55information about them. And I'm going to
18:57sort of show you what that looks like in
19:00a second. But why I like it over Tindle
19:02is that it's just even more granular. So
19:05it reduces the likelihood of loads
19:06complex. So if you know, let's jump to
19:10let's jump to
19:13um
19:14jump to a a demo of it. If I can find my
19:17mouse and put it on the other. So this
19:21is so this is just my um that folder
19:25that I showed you before. The same
19:27screenshot, same report um just opened
19:29up in Visual Studio. And these are all
19:31of the uh if I scroll down, this is all
19:34of those columns in the sales table. So
19:36this is one specific column. You can see
19:38all of the information that PowerBI has
19:39got about this. Um or if I go into the
19:42measures, for example, here's my profit
19:45measure, which is total sales amount
19:46minus that's the DAX. If I went and got
19:49the total sales amount, you can see the
19:51DAX uh the DAX here. Uh which is just
19:55max of that sales column. If I want um
19:58this is all of the information that
19:59PowerBI has under the hood about your
20:01semantic model. So if you have a pix,
20:03this is all there. It's just hidden away
20:05and you don't see it. Um the the cool
20:08thing about this is if I then went and
20:10changed uh I needed to change this
20:13measure um here. Maybe that becomes a a
20:16sum instead of a max and somebody else
20:19was also needing to do something to this
20:21model. Maybe they were needing to change
20:23the profit measure in some way. So it
20:26didn't use total product cost. It used
20:28something else. You can each do that.
20:29You can change the same measures in the
20:31same table at the same time. Whereas
20:34with TimL, I've got a version of this
20:37with Tim down here somewhere. I think uh
20:42it is this one. This is No, it is not
20:46this one. Uh model. Here it is. Semantic
20:50model definition
20:53tables measures. So this is that same
20:56table with all of the measures in it in
20:58Tindle. You can see there's quite a lot
21:00going on there. Um, so if you had two
21:02people needing to make changes to
21:05different measures in the same table,
21:07changing this file, maybe adding more
21:08information to those measures, it can
21:10quite easily break. Um, so having it
21:14just more granular is really useful.
21:18Cool. Uh, let's go back to the slides.
21:25So I'm going to talk a little bit about
21:27advanced deployments. So, we've talked
21:29about sort of files and things. I showed
21:30you the deployment pipelines. The
21:32advanced version of this I'm going to
21:33run through now. I've got um we're going
21:36to have to introduce some other concepts
21:38first, but the thing to really take away
21:40is that if you're interested in this,
21:42there is a guy called Kevin Chant. Kev
21:44Chant talks about this a lot. He has got
21:47an amazing repo full of information on
21:50this stuff. He's amazing speaker. I got
21:52the chance to meet him last month, see
21:54him speak. He was incredible on this
21:56topic. go and check that out. Um, but
22:00yeah, first what's a DevOps pipeline?
22:02Well, a DevOps pipeline is just a tool
22:04for CI/CD. Um, it's not specific to
22:07data. It's used by software engineers.
22:09It's used by all sorts of people. But
22:11effectively what it is is it allows when
22:14you you sit it over the top of a repo um
22:18you set pipeline over the top of a
22:19repository like a git repository and
22:21when you make changes to that repository
22:23it can trigger bits of code um to check
22:26it um so and it can run code from all
22:27sorts of different languages. So for
22:29example I make a change to my repo. If I
22:32had that um those files that I just
22:35showed you like in a repository and I go
22:37and change a file, push it up into my
22:40repository, DevOps pipelines could say,
22:42"Right, something's changed. I'm going
22:44to run this pipeline which has got a bit
22:46of code in it which can do things with
22:48say the fabric APIs or it can check um
22:52that some of the the changes that you've
22:53made make sense. Maybe you've maybe it
22:55checks for specific buzzwords in those
22:58files and sees that you've spelled some
23:00wrong or something. You can do all sorts
23:01of different things here. It's all code
23:03first type stuff, but there are some
23:04sort of built-in things that are not
23:07code um that you can like sort of a UI
23:09for low code changes as well in DevOps.
23:12But it's just uh there are other tools
23:14available uh GitHub actions for example.
23:16Um but it's just a way of when something
23:19changes triggering some code to check
23:21something.
23:23I'm going to go back to Tabular Editor
23:24for a second now. Um so there is in
23:27Tabular Editor there is a model
23:28deployment button. So when you're in
23:30tabular editor, there's a button you can
23:32click to start deploying changes from
23:34your model into your workspace. So from
23:37your local file into your workspace up
23:39in the cloud. Um, and you click the
23:42button, deploys. The cool thing about it
23:45is it means you can deploy semantic
23:47models without reports. Um, so you can
23:50build a semantic model in tabular
23:51editor. It's entirely focused around
23:53semantic models as tabular editor. um
23:56you can build that publish it and it
23:58just publishes a semantic model without
24:00a report assigned to it which can be
24:02really cool if you want to split the two
24:05in uh aside which is I think is best
24:07practice to have your semantic model and
24:09your reports managed completely
24:11separately um you can have different
24:14people when you get to big organizations
24:16you can get people that specialize in
24:17front-end visualization versus people
24:19that specialize in semantic models and
24:22your gold layer in your medallion
24:23architecture if you're using that. So,
24:25um more of an analytics engineering type
24:28role. Um the cool thing about the other
24:31cool thing about this is that everything
24:33you can do in tabular editor you can do
24:36through the CLI. Um which is a
24:39effectively a uh a command line version
24:42of tabular editor. So you can instead of
24:44having to go and do all of those clicks
24:47manually by yourself with your mouse or
24:49whatever, you can write some code that
24:51does everything you can do in Tabular
24:52Editor. and they've produced some
24:55documentation and a blog. There's also a
24:58a YouTube video by Johnny Winters um
25:00Gray School Analytics um that covers
25:03some of this stuff as well. So uh would
25:05recommend following this if you're
25:07interested in it. But the idea is that
25:08when they they're using this DevOps
25:11pipeline and their code so that when you
25:15deploy your report on want to deploy
25:17your report it's automatically running
25:19best practice analyzer. So I told you
25:21about that scanning it um and checking
25:23for all of the the the issues that might
25:25be there in your in your model. You can
25:27automate it to do that. So it can go
25:29away and scan your model and if it finds
25:32too many problems with it, stop the
25:33whole process and you don't deploy your
25:35semantic model. So it's just like a a
25:38way of unit testing semantic models
25:40before you push them up and checking
25:41that everything is right. Um their blog
25:44will teach you take you step through
25:45step by step through how to build that
25:48for semantic models.
25:51um the fabric APIs. So this is um
25:56incredible incredibly powerful set of
25:58tools. Um basically they let you
26:00interact with objects in fabric or
26:02PowerBI. Um so you can get information
26:05about a semantic model or report or you
26:07can post changes to it. Um there's all
26:10sorts of different things you can do
26:11with these APIs, but it does require
26:13some level of coding skill to do it. I
26:15use Python. there are other options
26:17there, but um it so this is where we're
26:20starting to get into that more advanced
26:22sort of techniques of you need to be
26:24able to code to do this or at least
26:26interact well with your favorite l and
26:28know what it's telling you what it all
26:30the code it's producing. Um if you are
26:33interested in the fabric APIs, there's a
26:35great blog here from one of my
26:36colleagues who's um shown you how to
26:39make your first fabric API call. Um all
26:41of the links are going to be in on the
26:43on the last slide as well. Um, so if you
26:45don't chance to to catch it, um, they're
26:48there. Um, so what I'm going to I'm
26:50going to show you something that I've
26:52done with the fabric APIs. I can't show
26:55you the code directly because it's my
26:57deep, but I can show you what it does
26:58and then sort of tell you roughly how it
27:01works. Um, so I have got a semantic
27:05model here.
27:08Uh, not semantic model, a report, sorry.
27:10Uh, a thin report. And this is a thin
27:12report because it's connected to this
27:13semantic model at the bottom down here.
27:16There is no like semantic model sort of
27:20this is just a report uh in PowerBI. Um
27:23the semantic model is developed
27:25separately and run. We we interact with
27:27that through tabular editor but this is
27:29it the version of it in uh in the pubix.
27:33Uh I've got the same thing in a
27:36workspace here. And this is the report.
27:38I'm just going to refresh it so you can
27:39see it. What I'm going to do uh is make
27:42some changes to this report locally and
27:45then I'm going to publish it into
27:46PowerBI without touching the publish
27:49button. So, let me find my mouse again.
27:53Maybe we will change this to
27:57#PBIMCR and we'll change the color of
28:00this. Which color do we want? Someone
28:03shout color. Just
28:05I heard red from the front. Uh, so I'm
28:08going to go red.
28:11I shouldn't say that in Manchester.
28:12Obviously, some people are going to say
28:13blues. Some say you're a Liver Liverpool
28:15fan in Manchester. Oh my gosh, what's
28:18going on?
28:20I've hit save.
28:23You shot yourself in the foot there.
28:26So, I've hit save. I haven't hit
28:29publish, right?
28:32Uh, let me just find my mouse. The mouse
28:34keeps disappearing off the edge of the
28:36screen. Uh, it's still blue in here. And
28:40just to prove that, I've not published
28:41anything yet. But what I am going to do
28:43is I'm going to find my code. Uh, which
28:47I can't show you. Um, oh, where's the
28:50mouse going again? It keeps disappearing
28:51off me. Uh, sales
28:57example. Let's see. So, I've got a
28:59little file. So, I'm going to push this
29:01onto this screen. Hopefully, this works.
29:04So, this is just running the code. Um,
29:06I'm going to talk you through what it's
29:08doing in a second, but what that's just
29:11done hopefully is, uh, if I refresh this
29:15now,
29:18hey, all of the all of those changes
29:21have pushed up into PowerBI. Um, so what
29:24is this doing?
29:26Um,
29:29gosh, my mouse keeps disappearing off
29:30the edge of this screen. Uh, it's a bit
29:32of a pain, but here we go.
29:36So,
29:38first things first, I've saved the
29:40report and it's saved as a pip and and
29:42then I've hit to run the code. Um,
29:44that's manual um at this point, but you
29:47could use your DevOps pipeline to
29:48automate this. So, when somebody pushes
29:50your changes up into your repository, it
29:52automatically runs that code. It's
29:55getting an access token, so it's
29:56authenticating to the tenants. So,
29:58logging in to PowerBI. Um, it's
30:00converting a workspace name into an ID.
30:03So we obviously call thing workspaces
30:05things like demo or demo dev or
30:08whatever. PowerBI knows it by GID. Um
30:11and there's an API that you can call to
30:13to to um get a list of all of your
30:16workspaces and you can use that to sort
30:18of convert from name into GID. Do the
30:21same with the data set which semantic
30:23model is what it's called now but the
30:24API is still called data set um because
30:27that's what they used to call semantic
30:28models. Same with a report. The
30:31complicated bit of this is it's turning
30:33that report that I've just done into a
30:36report definition in B 64 which is a
30:40horrible thing. Um there are Python like
30:42modules that can convert um files or
30:45things into B 64. Um so you can use that
30:48there and I I'll go into a bit more
30:50about what a report definition is in the
30:51next slide. And then the last thing it's
30:54doing is it's checking if the report is
30:56new. It's going to use the create report
30:57API and publish it up. So what I didn't
31:00show I didn't show this happening but if
31:02that report hadn't have existed in the
31:04service it would have created a new
31:05version of it in the workspace that I
31:08told it to um or it's going to update
31:10the existing report.
31:13So what's a report definition file? Well
31:15basically you saw this thing. This is
31:17actually a screenshot from a blog that
31:18one of my colleagues wrote rather than
31:20the specific one that I've shown you.
31:22But um your report a PBIR is just a
31:26series of files. Um and each of those um
31:29is a JSON. Um a report definition is
31:32just putting all of that together into
31:34one big JSON file. Um so you as I said
31:38you need to convert each of them into B
31:4064 which there is a Python library that
31:43can do that. There are other ways of
31:44doing that but effectively there is it
31:47will end up looking something like this.
31:48This is every file in my report or in a
31:51report and this is a version from
31:52Microsoft. Um, and then each of these
31:54needs to be a base 64 string. So it's
31:56just a way of converting data into
31:59machine language. Um, and there's
32:01there's documentation from Microsoft on
32:03on that and how to do that. Um,
32:07but let's put everything I've talked
32:08about in this section together. So
32:10firstly, I'm going to split my PowerBI
32:12reports into semantic models and
32:14reports. I'm going to store all the
32:15semantic models as using that save to
32:18folder file format. I'm going to save my
32:20reports using PBIP um with this enhanced
32:23PBR which is a preview function but it's
32:26a setting that you turn on in uh in
32:27PowerBI. I then have a repository which
32:31splits and there's separate folders for
32:33model and reports. I've created a DevOps
32:36pipeline for model deployment which is
32:38used following that um tabular editor
32:40script. In my case, I've just borrowed
32:42one that one of my colleagues wrote for
32:43this, but you can load tabular editor
32:46CLI, run best practice analyzer. You
32:48could also build in think unit tests. So
32:50things like check that this measure
32:53exists or check that this like this
32:55table or this column still exists.
32:58Again, it would need to be to need to be
32:59Python script, but you can do it and run
33:01that as part of it. Um, you I've created
33:04the fin report deployment script that I
33:06just showed you. Um, and then use a
33:08DevOps pipeline to trigger that. And so
33:11what that means is that then when
33:12somebody wants to make a change to a
33:14PowerBI report or semantic model, they
33:16can make a a change to a file in that
33:19repo, they can use a PR to push um to
33:22push those changes or pull those changes
33:24in to that reput repository. Um the
33:28right devops pipeline will trigger and
33:30then everything will automatically when
33:32when it's been approved, everything will
33:34push into into PowerBI automatically.
33:36Nobody needs to be there to click a
33:38publish button or click a deploy button.
33:40You can do it all set it all up so that
33:42everything is fully automated other than
33:45the the bit of somebody approving a PR
33:47which is hope you're hopefully doing
33:49that anyway. Um
33:52what's the benefits of all that? Well
33:54then production PowerBI models and
33:58reports is just your repository. So
34:00somebody deletes production workspace.
34:03It's all there in your repo. You can
34:04just redeploy it all. But also, you know
34:06that for it to get into that workspace,
34:09get out to production, it's gone through
34:11your your um your change management
34:14processing, your your um your different
34:17PR processes, whatever you use to make
34:20sure that that repo is safe. That
34:22suddenly is extended to all of your
34:24reports and models in PowerBI. Um you
34:26can automate deployment. So, I mentioned
34:29this is sort of a specific use case I've
34:31mentioned in here where if you've got a
34:32hundred reports and they're all stored
34:34in this repository and you need to
34:36change one specific hex code from one
34:38shade of red to another shade of red
34:40because your company's rebranded and
34:41they're not doing it using a a theme
34:44file. Um, you can just control F or you
34:46could do the same thing if like you
34:48needed to change every instance of the
34:49word revenue to sales. Um, you could
34:54controlf over all of the files in a
34:55repository, make a commit, push it up,
34:58and then the pipeline would just push
35:01all of those changes out to all of your
35:03reports, hundreds of them maybe. And it
35:06can do that in seconds. Um, and you can
35:09add in testing in flight suit. We
35:11already I talked about the um the about
35:14um the best practice analyzer for
35:16semantic models. you can do similar
35:18things or build your own versions for um
35:20for um your reports, but you can also
35:23use things like PBI tools. If you're
35:25interested in that, then go to Kev's um
35:29Kev's group. But yeah, that's
35:31everything. Um thank you everyone and uh
35:34yeah, any questions?
35:39>> Okay,
35:40>> thank you.
35:49Can you find
35:52that different?
35:54>> Uh yeah, good question. So as part of
35:57that file save to file folder save to
36:01folder file format um the rowle security
36:04is just a part of one of those files. So
36:07if you needed to change the rowle
36:09security for different environments,
36:11you'd probably have to write a script to
36:14change that or or a step in your DevOps
36:16pipeline to to hot swap that. But you
36:19can yeah, you can parameterize you could
36:22parameterize it and use then your your
36:24DevOps pipeline to change it in flight.
36:27Um, I think there is if you're just
36:31using the PowerBI deployment pipelines,
36:33I think you can do the same if you set
36:35up the parameters, but I'm not super
36:36sure on that. But yeah, like uh anything
36:39you want to do with a DevOps pipeline,
36:42you can you can write a little script, a
36:44little code, a bit of code, and you
36:45could insert that into your DevOps
36:47pipeline and get it to run that. um or
36:50you as I said the param the changing
36:52things between workspaces
36:54um is just a um it's just a parameter
36:58that it's swapping at different stages.
37:00So I can say in dev link that you need
37:03to use this a need group in in prod one
37:09or whatever. Um so you can definitely
37:10set that up. My screen has gone really
37:12weird there but yeah these were all of
37:14the links across I've put in that file.
37:16There's also my LinkedIn down here if
37:18anyone wants to. Um, please feel free to
37:21add me. Ask me questions. I'm always up
37:23for answering questions on any of this
37:25stuff or anything related. But yeah,
37:27thank you very much. Any other
37:29questions?
38:10So there is that's it's just not done in
38:12PowerBI anymore. So when you want to
38:15make so because it's all files in a
38:16repository um and you're using you're
38:19using git so either in um Azure DevOps
38:22or GitHub um when somebody wants to make
38:24that change and push it out they've got
38:26to put they've got to make the got to
38:28make the change to the files push it up
38:30to to Git um and then you can set up a
38:33pull request process. So they can't just
38:35put that straight into the the the
38:38branch that's connected to your your
38:40workspaces. You need to have a pull
38:43request. So they want they're saying I
38:44want to make these changes. You create a
38:47pull request and then with git you can
38:49set up a process around that so that you
38:52man you're managing that there. So you
38:55can't publish into a protected branch
38:58automatically. Somebody has to still
39:00approve it. They're just doing it in
39:02GitHub or in uh Azure DevOps repos uh
39:06rather than in PowerBI. Um you can see
39:09when you do that you can see a series of
39:12files that have changed. Um so they
39:14could they can review that in in that
39:17system there but it then means that you
39:19you don't have to do it all manually.
39:21When they hit approve it automatically
39:23pushes it up. So there is still a review
39:25process. It's just making it in it's
39:28effectively making it earlier in the
39:30cycle in in your repo rather than in
39:32PowerBI on the the pipeline there. So
39:36it's yeah it's you could set it up so
39:38that anyone can just push any old stuff
39:39into into production if you don't want
39:42but it is you can still have a you still
39:45have those reviews and if anything it
39:47becomes a bit more formal.