(Upbeat music)
Right, module three now,
we'll focus on the all important topic of retrieving data.
So we'll focus first of all
in this recording on data sources.
We defined data sources in the report definition itself
to connect to our data.
And there are three possibilities here,
there's the ability to connect to a Power BI data set.
CHRIS: Mhh.
And there's also the ability
to connect to external data stores.
Interestingly, there's been a new feature added
beyond what reporting services had,
and that is the ability to define
static sets of data within the report itself.
Now your report can have one or possibly more data sources,
and they're all defined with a unique name.
The type, meaning the data provider,
the connection string
on how Power BI will connect to the store,
and how authentication will take place.
Right, let's take a look then
at the front type of data source
and that is the Power BI data set.
And this makes a lot of sense when you think about it,
because when you publish the report to your workspace,
there are already data resources
that you have probably built in Power BI.
CHRIS: Yes.
So we see this as a first class option,
that the data set itself,
which could represent an input model,
a direct query model to your data,
or even a composite model.
So while this course won't go into the details
about a Power BI data set,
you have the ability to build reports of them.
CHRIS: Yes,
Okay, that data set must reside within the same tenant,
you as a report author must have built permission
for that data set.
And if the data said now, there is an exception,
if it represents what we call a live connection
to an external data set
that could be hosted with SQL Server Analysis Services
or Azure Analysis Services,
you can't connect to it.
You will have to connect directly to the model itself.
CHIS: Yes, you'd need security,
you'd have to access it anyway.
It's just the way the Power BI handles that exchange,
under the covers there,
you just need to connect directly to it
as opposed to go through premium.
And we'll come to those external.
That's what we'll define as an external data source
in just a moment.
Chris, what can we say about the XMLA
to connect to these data sets.
So if your data set's in Power BI premium,
you can use the XMLA endpoint to connect to it,
which would mean you're choosing this specific Power BI
as the analysis services provider as the data source.
Now, since the XMLA endpoint has been introduced,
we've actually added in native connectivity option
to Power BI datasets which you can use
for either datasets hosted in premium
or non premium workspaces.
And in general, we advise people to go that route.
Alright, so using the option we're describing here,
create a data source off of Power BI data set.
CHRIS: Yeap.
Here we go.
Alright, the second type of data source then,
the external data sources.
So we can group them into two types.
[Chris] Mmh.
They're either hosted in the cloud,
or they're hosted on premises.
So a look there at the cloud sources all Azure
as your SQL database, as your SQL data warehouse,
as your SQL managed instance,
and Azure Analysis Services.
For on-premises,
it can be SQL server, or SQL Server Analysis Services,
That could include tabular or multi dimensional models
if you have them, Oracle and Teradata.
Do note then that Power BI as a cloud service,
when it needs to retrieve data
from these on-premises sources will require
that you have the on-premises data gateway setup,
and data sources configured to support that.
CHRIS: Yeah, and even though the slide says
you can connect to SQL Server 2005 Plus,
it's probably not a good idea at this point
to be running SQL Server 2005 for your data support.
PETER: What, in the data support?
CHRIS: Yes.
PETER: Okay.
Then we have the third type Chris,
the static sets of data.
Yeah, enter data is a very popular option
in Power BI desktop where you can just go
and type a table of data.
We did this about a year or so ago,
we added the option to do enter data
in the context of Report Builder.
And I will say that this was a feature very much
that the PM was looking for,
because one of the challenging things with paginated reports
is having a deemable data report or report you can use
because you would always need
a connection to a data source under the covers.
So you had to have that setup in terms of SQL server
or some other data source.
Power BI Desktop, of course allows you to pull data
into the data model.
And it made it much easier to share files
and show these deemable reports.
So this is a great way to just type in some tables of data.
In fact, I created a report with my friend
paginate report bear where I used all of it as enter data.
And it is great for a demonstration
or proof of concept to get started quick.
Great, okay, so supplementary data,
demonstrations, proof of concept.
CHRIS: Exactly.
PETER: So when Power BI connects,
it will need to authenticate to your data stores.
And there are two possible options,
using Windows Integrated Security or specific credentials.
Yeah, so if you're using a cloud data source,
you do have the option when you set this up
to authenticate as the user viewing the report.
So you can if you're using as your SQL database
or as your analysis services,
you can go ahead and set that up
once you've loaded that up to the servers.
And the credentials could be
if you're storing their credentials,
either windows credentials or database credentials.
And then the authentication types,
they differ according to the source that you're using.
Yeah, and I think the big thing to call out here
is around the data sources for the gateway.
you do not have a credential stored in the gateway.
Interestingly enough, you can still apply
low level security things like SQL Server,
even with those stored credentials
if you're using the user ID field
that we talked about earlier.
And we'll maybe touch on that a little bit later on.
All right, then, just a note,
we made mention that some global built in fields
behave differently in Report Builder
compared to the report when it's actually rendered
by the Power BI service.
There are some things to note also about
when working with data sources.
You see when you're developing a report
in Power BI Desktop and you're toggling to preview
to run the report,
those connections are being made from your machine.
However once published, it will be Power BI
that will be doing those connections.
So things will be a little bit different.
You will require that when working with on premises sources,
you don't need the gateway at development time.
CHRIS: That's Correct.
But when you publish it to the servers,
be prepared, that report isn't gonna render
because probably I will have no physical ability
to connect to the data.
Okay, so we will cover this off in module seven.
That having published the report,
there may be some additional tasks
that you need to undertake,
to ensure the probably I can achieve
that connection and authentication.
And we do help you, walk you through that
once you've uploaded the report.
We do prompt you to go and set
those credentials all the time.
Right, I think we'll be seeing that shortly
in a demonstration.
I think you're right.