Welcome to Webinar Wednesday my name is Paul Johnson I'm a software Solutions
Architect with Prophet Business Group in
this webinar we will review how to
connect to power your Power BI desktop
application to Business Central and
Dynamics CE which is CRM this webinar is
being recorded in chat is available for
you the audience to ask questions chat
is not being recorded today's objectives
to learn how to connect to D 365 CE and
business central from Power BI so let's
walk into demo of how I would get data
using Power BI desktop so I've opened up
power bi desktop here and I'm gonna hit
the get Data button from here I'm gonna
select more and online services so this
will show me online services are
available I have then Amex 365 business
central which is what I'm gonna be
selecting here and I'm gonna hit connect
as soon as I do that it's gonna prompt
me for my credentials to sign in on this
in this case I've already supplied the
credentials but it'd be my my ID and
password and now it's showing me the
attendance tenant that's available to me
and production in sandbox with sandbox
setup in this case I want to connect to
sandbox so I'm gonna expand sandbox and
I have a list of companies that are
available to me so I'm going to select
Kronus Canada now here's all the objects
that have been made visible to me as a
user so fairly large list here of
different objects are available and I'm
gonna show you shortly how I would
expose another web service endpoint to
be able to connect to so I'm not going
to connect to any of the objects in this
case but if I wanted you know list of
customers I can definitely do that I've
created a customer's webinar here I have
GL entries
jobless so I'm just gonna hit cancel to
this and I'm gonna show you how you
would expose another web endpoint so I'm
going to switch over to business central
from here to create a new data endpoint
or Web Services endpoint I'm going to
select setup in extensions and I'm going
to go to assisted setup and I'm gonna
select the ability or option for
reporting data so I'm just gonna select
that and I'm gonna click start setup and
I'm just gonna run this again so that's
okay
hit next so I have the option to create
a new data set in this at this point or
copy an existing data sets or edit an
existing data set so I'm gonna create a
new data set hit next give it a name so
we're going to call this customers and
items to for webinar as an example now
this could be a page or query I'm going
to select query here and I'm gonna hit
the lookup so these are all the objects
that are exposed to me so I have you
know all these available these different
queries that have already been created
and exposed if I scroll down list fairly
large list of options here so in this
case I want customers and items so we're
gonna scroll up so I can use the search
at the top here so we're gonna select
customers so here's a list of all the
customer objects they're available to me
at the moment so I'm going to select
item sales by customer in this case and
we're going to hit okay so this is
object 102
and I'm gonna hit next now these are all
the fields are available to me I can
apply filter I'm just going to select
all the fields in this particular case
now we could add a filter if I click add
filters I can you know add all sorts of
filters and create a very customized
list in this case I'm not going to apply
filter I'm going to hit publish and I'm
gonna hit finish so it's created a new
endpoint now if I go back to power bi
and we hit select get data and we select
our online services and business central
then we hit connect so I'm gonna select
my sandbox once again I'm gonna select
Cronus and now we can select webinar
might put that in the name here so I've
customer and items two for webinar so if
I select that as my list okay I'll see
that it's going to preview the in list
for me here's a list of all the items
that were purchased and I'm going to hit
transform data so it's now loading this
into power query editor for me to edit
in my model so the one first thing I
notice is is that quantities are
negative number so I'm just going to
change that quickly I'm just going to
transform that column and we're going to
change this to be multiply this by
negative one
just get a positive number and hit OK to
this
and the other thing I want to do is I'll
just leave that we'll just close and
apply for now so it's applying that
changes to my model loading the model
with the data into my power bi desktop
file so now I have I can easily create
something fairly simple so let's assume
that I want let's choose the item number
here so we're gonna go to item number
drag that on to the report we're gonna
choose a matrix and then I'm gonna
choose the quantity will drag that into
the values
so here's quantity now I could also do
something like I want description into
the rows and let's format that so it's a
little better so if I go to row headers
I could choose sorry rollers so we'll
expand that so we're gonna go to row
headers we're gonna take off the stepped
layout so everything's in one row then
I'm gonna go to the subtotals and we're
just gonna remove row subtotals so if I
expand this I have a nice sort of pivot
table chart our table here you can also
do something like adding the customer
name has a slicer into this report so
we'll just change that to a slicer so
now I can see item sales by customer
just by clicking through these so very
simple report to get business central
data into power bi
when trying to connect power bi to
Dynamics Cee
so we would open up the power bi desktop
and get data and like we normally would
and we're gonna choose more and we're
going to choose our online service and
dynamics 365 online and it connect it's
now going to prompt us to enter our Web
API URL there's a couple ways you can do
this and your basic so it's giving you
what it wants and the API how you would
go about finding what this URL should be
is if we connect to our Dynamics 365
environment and we go to advanced
settings or our settings tab
and we go to customizations and going to
go to developer resources you'll see
this is the service route URL here so
this is what we want to copy so we'll
copy that we would now paste this into
this box and hit OK at this point this
is going to now want to authenticate us
to that environment so we would select
organizational account and we can sign
in with our credentials so we're going
to get prompted for Nike our account our
email address and their office 365
username and pass the Associated
password
and we would hit connect so at this
point it's connecting us to our
environment and it will return
all the objects that we have access to
so here's my environments and if I you
can see I have accounts and cases if I
was to type into this field here for our
opportunities as you can see I have
access to pretty much every object
that's available to me so contacts so
contacts is there so I'm just gonna
choose accounts for right now just to
make it easy to work with but I could
choose multiple if I wanted to as well
so once we've selected accounts as you
can see it's not returning all the
fields that are available to us in the
accounts listing and I would normally do
what I normally do which is you know
transform the date at this point and
select only the fields that I want some
hit transform data
and it's it's now opened up power query
for us and I have all the fields
available to me so one of the downsides
with this method is it's actually
returning the goo for the certain lookup
fields it's returning their associated
goo it's to us instead of the label
value so an example of that would be
let's just choose a column choose our
columns here and you select everything
worried currency VL so transaction
currency ID transaction currency value
it okay so as you can see it's returned
the goo in to us and then obviously the
associated stuff related to that
currency so I could go in and return the
name of the currency name as an example
here but an associated good but I'm not
going to do that in this case I'm gonna
show you another way we can connect to
dynamic C data using a tool called XR x
RM toolbox which is a free product
another way to connect to Dynamics seee
data is to use a tool called xrm toolbox
so if you go to xrm toolbox comm webpage
you'll notice there is explanation of
what this tool is it's basically a free
tool with a bunch of tools that people
have built into it that to do to
accommodate and facilitate the process
of creating doing certain tasks within
the CRM or Dynamics seee system so from
this website I would just hit download
the latest version once I've downloaded
it it's gonna create a zip file I then
extract that zip file and I have this
application I've already launched the
application so I'm not going to do that
right now
first thing you were going to want to do
using the tool is to it's going to bring
you to the start page is you're going to
want to create a connection to your
dynamics ee environment or download a
specific tool so I'm just going to open
the tool library here just to show you
all the tools that are available in here
but there's a lot of tools as you can
see there's stuff from you know bulk
delete tool to data import tools
dashboard transfer tools currency
exchange rates automation all sorts of
things so I'm not gonna go over each one
of these tools but there's a large list
of tools that the community has provided
to us to use some of these are better
than others so the tool I'm going to
focus on today is something called power
the power query tool so I'm just going
to jump back there so I've actually
installed so you basically select the
tool click the install button up here to
install that particular tool I've
actually already done that in this case
so I'm not going to do that here
so I have the power query tool I'm just
going to click on it just to show you
how what it looks like so so this tool
that had does a bunch of things and I'll
go over this shortly but the first thing
as I mentioned what you want to do is
create a connection to your environment
so I would hit the connection or create
a new connection I've already created a
connection in this case but if I hit new
connection I get prompted with a wizard
where I entered my organisation URL
which I've already done in this case
that would be and then provide a
username your username and your Posse
ated password so I've done that I'm not
going to go through that step again once
I've created that connection I would
connect to that connect to environment
open the tool and now I'm ready to use
the tool so the power query builder tool
is first thing you want to do is click
load entities this will will load all
the associated entities within your
environment so in my case I'm gonna
select account and now I'm also shown
all the views that have created be it
system views or quick-find views that
are available in the system so in this
case I want all accounts now these this
view within the system is all accounts I
could easily create my own view I could
select certain fields I could add a
field like for example I want address 1
street 3 I could add that into the list
as wall here let's select it click Add
and now it's added into the field so
it's just a template or a beast to go
from so first thing I next thing I want
to do is select all the fields and then
I would go to fetch an xml query when i
do that
I first have to generate the effects of
XML so basic I'm creating XML code that
I can use within the power query editor
one of the things you want to do though
is you also need the service URLs so
once I've connected to environment I
would click generate service URLs there
are two service or elles that are
required one is the dine through 65c
base URL the other one is the service
route URL so what I would do is I'm
going to open up a new part bi desktop
file here and I would click get data and
select a blank query unless I've done
that I would now need to paste in my
base URL so I'm just gonna copy that
go back to power bi paste it in I also
need to rename this with the proper with
the exact same name so it would be dying
365 see base
biess URL
and I would do the same thing with the
service route URL so copy that whole
thing
we're gonna create another blank query
again
and rename this
servus root URL once I've done those two
things I'm now ready to go back to our
fetch xml query so a query that we
generated would be this I'm basically
just going to highlight this ctrl C to
copy it go back to power query editor
we're gonna create another new query
again blank row again and now instead of
just pasting it in there I'm just going
to use the advanced query editor delete
this information that's already in there
and then paste it into here so as you
can see I have no syntax errors
everything looks fine I will hit done at
this point it may prompt us to enter
credentials to connect to the data which
we would just sign in with our normal
username and password in this case it's
not prompting me and it's created my
data is now loaded into everyone I could
rename this query just to make it I
won't do that though so I have all my
data loaded into the view here I've also
added a link and the way I did that I'm
just going to jump back to the XML query
is when I am configuring the data source
and updating the fetch xml query which
is under
the FF Thunder the F fetch XML config
and I would select add record URL and by
doing that it's gonna add back into our
power bi query here it's gonna add this
link with our tenant and the account ID
and the rest of the information so then
I would close and apply this it's not
going to build the model
and I'm ready to start building
something so as an example I could drag
account name into here and I also want
to go to them if I was to drag the just
the link as an example into this table
typically would not be recognized as a
you're also the way you would do that is
you would select that field go to the
modeling tab and make sure that this
data category is set to web URL and then
it becomes a hyperlink that you can
click on so if I was to you know I have
a simple table here but if I click on a
particular record it'll now open up that
particular record within the 365
environment so this is a nice way to
create dashboards and have give your end
users the ability to drill back on
certain records or you know
opportunities or cases within dynamics
CE or really any entity backed into the
source system so
so webinar recap we reviewed how to
connect to be 365 CD from power bi we
also reviewed how to connect to business
central from power bi and we also
learned how to use the xrm toolbox to
connect to Dynamics 365 see join us for
upcoming webinars February 5th he
paystubs
February 12th office 365 security and
compliance in February 19th using flow
with dynamic CE thanks for watching
please go to prophet.ca/webinars
to register for upcoming webinars or
view on-demand videos also subscribe to
our You Tube channel a profit business
group if you want to talk about getting
started with power bi and connecting to
your data contact me Paul J at prophet.ca
to chat all things Power BI.  Thank you.
