>> Refreshing Tabular Models can be a tedious task.
In this episode, learn how to use
multiple Azure services to automate the process.
Learn more in this episode of Data Exposed.
[MUSIC]
>> Hi, I'm Anna Hoffman.
Welcome to this episode of Data Exposed MVP Edition.
Today, I'm with [inaudible] Gaston.
Before we get started, Gaston,
why don't you tell us a little bit
more about what it is that you do?
>> Okay. I'm really excited to be here today.
My name is Gaston Cruz.
I am a Microsoft MVP in Data Platform,
and I've been working as a Senior Principal within Slalom company.
I've been running different architectural design
and running Microsoft Center of Excellence for Slalom globally.
I recently moved with my family from Uruguay, South America.
I've been engaged with different leaders and
communities in South America, let's say,
Uruguay, Argentina, Brazil, Peru,
Colombia, Chile, so I've been involved,
really engaged, with those communities.
Also since I moved here to Bellevue,
I started the YouTube channel
with some folks, trying to connect,
Microsoft Core with some leaderships from South America.
That is some quick introduction to myself.
>> Awesome. Cool. We're really
excited to have you here today, and we'll put
a link for our viewers to
your YouTube channel, so they can go and learn more, if they like.
Today, we're going to be talking about refreshing
Tabular Models and some easy ways to do that.
This is something I don't know a ton about.
I'm actually relatively new to this space.
So, Gaston, I'm really looking forward to learning from you.
What are some of the common scenarios,
and what are some solutions that you have?
I'll just ask you to take it away,
and show us, and teach us, and we'll learn together.
>> Sounds great. This is a common scenario
where I have been shopping with
different clients and talking about,
what is the best practice in terms of
refreshing data models that you can deploy in,
let's say, Azure Logic services?
That being said, and this is a scenario where you
want to create a solution for your data models,
as you know, and I can briefly introduce
to [inaudible] on that end, in terms of,
if you have multiple models,
let's say, you deploy your semantic layer in
Azure Logic Services because you have multiple users, that
they want to run different reports
within Power BI, accessing those data models.
In some way, you need to
refresh the data coming to your semantic model.
Your semantic model can have data source coming from Azure SQL DB,
or let's say Azure Data Lake, or let's say,
legacy systems like SAP or Salesforce.
You can actually combine
those data sources in Azure Logic Services.
You can deploy models using,
let's say, SQL data tools from Visual Studio.
Then after you deploy your model,
you need to refresh those works with
the up-to-date information coming from those data sources.
I will be talking a little bit here in terms of e-service to
where we are going to jump into e-financial data services like
the double registration to
refresh data model not using a naming account,
using a service principal account.
Then using Logic Apps to access the Rest API
of Azure Logic Services and doing the refresh modeling.
Then with Azure Data Factory,
you can provide a trigger orchestration when you can say,
let's have our refresh of our data model every day at 6:00 AM.
That is briefly the introduction
of this architectural design, that way,
we can [inaudible] and say,
"This is one of the ways that we have to refresh those data models.
I'm going to bring here my Azure portal.
Let's say that I have one analysis services deployed,
and you can see here that I have
multiple models in my Azure Logic services.
You can see here at the screen that I
have three different data models running in my server.
Then I have, for example,
different options in terms of how we can access those data models.
One option, of course,
is going directly with SQL Management Studio.
From Management Studio, I can connect to those data models.
In this case, I have
one data model here that lives in my SQL Management Studio.
I am connecting from
SQL Management Studio to my Azure Analysis Services.
As you can see, I have multiple tables living in my data model.
In this case, in this scenario,
what I'm saying is, to provide a way to
refresh these data models and have
all these tables coming with the latest data,
in this case, I'm connecting to
an Azure SQL database to provide and to create a semantic layer.
If I want to refresh,
let's say, the customer table or
the product table or the address table.
I need a way to, in some way,
refresh that data model in a way where we can set
up a process to every day have the up-to-date information.
One manual way, of course,
is go into the table,
right-click, and process the table,
or go into the data model,
right-click, and process the database.
Or if you have millions of rows,
let's say you have five table and you need to
provide a way to refresh just a partition of that table,
you can go to that table and right-click,
go to Partitions, and only process the partition itself.
From here, you can see if you have multiple partitions,
we are going to have multiple rows.
With this option, you can process only that partition.
That is the manual way to do things.
But let's go with our case.
If I want to refresh
the data model using different Azure Data Services,
what I can do is, first of all,
I need to provide Guan approach illustration.
This is my approach illustration.
Why I am calling the approach illustration?
Because I don't want that Gaston is
going to be the name account to refresh my data model.
I want to provide
service accounts that can refresh the data model.
When I create this approach illustration,
I have my client ID, my tenant ID,
I provide a certificate to have the secure layer
here where I can provide
that account with the security to process my data model.
At the same time, I need to provide to
that account to give API permissions to process my data model.
As you can see here,
I add some permissions to my Azure IoT Services saying,
"Okay, I want to provide read and
write to this account to refresh the data model."
This is the way you can refresh
the data model using this kind of accounts,
and this is how and why we are creating
the approach illustration to
provide that refresh of the data model.
Then what I'm showing here is I create
the Logic apps that this Logic app is accessing,
and this is really cool way to go with Logic apps and
send an HTTP request and use this action,
this is just one action in my Logic apps,
this is an HTTP action.
What I am doing here is provide a URL for
my Azure IoT services and running this REST API call.
In this case, as you can see,
I am refreshing one partition for the sales address table,
and I am refreshing the table of customers,
and I am refreshing the table of product.
I am saying, with Logic Apps,
I am refreshing three different tables of my Azure IoT service.
Where I can sham and how I
bring all these details of accessing my Azure IoT services,
I am showing here, there's a link.
We can provide that link.
This is the link to access the REST API of Azure IoT services.
This is what I am using right now to do
the authentication from Logic Apps and to run the process,
you can see the body that I'm using to refresh my data model.
I am accessing directly from Logic Apps
to the REST API of Azure IoT service to do that Refresh.
Last but not least,
what I'm doing after that, of course,
I can run the Logic Apps directly from here to
see that my model is running and up-to-date.
But at the same time,
I can bring another services like
Azure Data Factory that allow me to connect directly,
this is my Azure Data Factory process
where I just move only one activity,
the activity that I move is just a web action.
This web action in the settings is just calling,
"This is the URL of my Logic Apps."
I can show briefly,
if you can see my Logic Apps,
this URL is the one I provide from Azure Data Factory.
From Azure Data Factory,
I'm calling this Logic Apps via this URL.
That allow me to trigger from Azure Data Factory, the Logic App.
What is the advantage to do that?
I can set up a trigger here from Azure Data Factory to say,
okay, this trigger is going to just let me edit here.
As you can see, I can set up the refresh directly from here.
I can say, okay, from this trigger,
I can refresh the model,
let's say, every day or every hour.
You can see the recurrence here,
you can set up every minute,
every hour, every day, every week.
That is one of the ways that we can address to
have the automation of the process of the entire data model.
That is just introduction that with did
here to understand what's going on in my Azure IoT services,
how we connect to different data sources from Azure IoT services,
and how we deploy a solution where we can have
all my data models up-to-date without any manual efforts.
>> Cool. This was really cool.
I haven't seen a lot of these things used together before.
If I just recap what you did,
you showed us how to create a service principle that can
be then used to access this Azure Analysis Services,
then you used Logic Apps to create
a way to update specific things that you want updated,
and then you used Azure Data Factory to actually
trigger those updates from the Logic App
with the service principle you created on
whatever schedule it is that you set.
Now, that's really cool. Did I get that quite right?
>> Exactly. You summarized exactly what we did today.
>> Awesome. Very cool. Now, this seems like
a great way if your data source might be Azure SQL database,
like, I think, it might have been in this case.
But what if you had other data sources?
Can you do something similar, or what do you advice?
>> That is a really great question.
Right now, with Azure IoT services,
our semantic layer, and with different tools,
one of them is Visual Studio data tools,
with SQL data tools,
what you can do is just go with Visual Studio
and add different data sources to create your semantic model,
and then deploy it to Azure IoT services.
Let's say that you don't have all of the Azure SQL database.
If you have Azure SQL database,
you can have a Cosmos DB,
you can have different Spark [inaudible] you can have data lakes,
and you can provide
all those data sources to your semantic models,
you can create those relationships
to connect different data sources,
and then create measures, create relationships,
and then deploy that data model to Azure IoT services,
and then start ramping up with
these kind of solutions to update information.
>> Wow, that's awesome.
It's really great that you can use it for multiple data sources.
Also, you made it look pretty simple to set up.
I think I might be able to go try this myself.
Gaston, thanks so much for joining us on the show today.
It was great to have you.
We'll provide a link to our viewers in the description, so you
can go try this out yourself, and set
up what Gaston has setup and showed us today.
If you like this video,
please like this video,
subscribe to our channel, and
we hope to see you next time on Data Exposed.
[MUSIC]