How to Transform Your CRM Data: Connecting Power BI & Dynamics 365

Visit Website View Our Posts

Your company has finally made the jump to the cloud. Great, but now how do you use the data in your Microsoft Dynamics 365 cloud environment? To report on all of the data entered into CRM by sales and marketing, you used to just hook up to the SQL database that is on the servers, import that data into Power BI, and start building. To connect Power BI and Dynamics 365, you must connect to Microsoft’s server over the internet.

Connecting Power BI and Dynamics 365

Step one in connecting Power BI and Dynamics 365 is to access your data using the OData connection string. This can be found in your instance of D365 under:

Settings > Customizations > Developer Resources > Instance Web API – Service Root URL

Then we take the D365 OData connection string and plug it into the OData connection in Power BI.

Get data > OData

Next, you will need to sign into your environment with your office credentials. From there, all you have to do is begin selecting the tables that you need and load them into the query editor as you would any other data source.

Now that we have pulled the data in from CRM using the OData connection string, we need to make some changes to the tables. The second part of this blog post will go over how to translate some of the CRM data so it is easier to read and take action on.

Transforming the Data from CRM

1. Connect the tables

The first thing that you will notice when you look at the table is that there is a good amount of GUIDs. We will use these as the keys to connect tables. In the images below, we can see that I have brought in two tables. The ‘System Users’ table has the users name and information. I have also brought in an activity table. In the second table, we can see that under ‘_ownerid_value’ we have a GUID and not a name.

Next, under the relationship view, you will use the two GUIDs as keys to join the tables so you can easily identify who the owner is.

2. Translate your data

The next step providing proper English names for the options that are picklists in CRM. You will see these columns in the image directly above as ‘statuscode’ and ‘socius_phonecalltype’. One option is to replace the values with words column by column—this is not recommend because it adds multiple steps to your query and with OData queries, the goal is to eliminate inefficiencies.

The other, more efficient option would be to create a table with the values and the corresponding English words as shown in the image below. Then, you will merge the tables. You can set the newly created table to not refresh or load so that it will not affect your load times.

To access the options that represent the values in a pick list you will need a metadata file. This can be obtained from your CRM team or from an application like XRM toolbox. If you are going to use XRM tool box, the plugin is called metadata document generator as shown below.

From there, sign into your CRM instance and click ‘Retrieve Entities and Languages’ then ‘Generate document’. The document will be created as an Excel workbook with all of the information about the CRM data.

3. Build your reports

After you have made the connection and transformed the data, the next step is to start building insightful visuals and reports.

For more information on optimizing your reports or other Power BI features, email a Power BI consultant today.

By Velosio,

5 thoughts on “How to Transform Your CRM Data: Connecting Power BI & Dynamics 365”

  1. hi I'm dr. McQueen I'm going to show you good luck anti-aging products only at
    the drugstore so follow me try to find products that have peptides a typical example of a product that contains peptides may be the Olay Regenerist here we are targeting fine lines and wrinkles whenever we apply this device overnight one of the very obvious manifestations of skin aging are fine lines and wrinkles the ingredient to consider for treating
    this dilemma is retinol a brand called ROC is extremely fabled for its retinol containing products when applied overnight with time
    you might find a slow correction in the looks of fine lines and wrinkles skin aging does start to manifest when you're in your 20s so it's important to begin young so you stay young a
    good example of a brand that's really targeting
    a younger crowd is Garnier and Garnier does so by
    depending a lot on plant derived ingredients
    but this system is very good because it not just has salicylic acid that may gently exfoliate the skin but it also has
    soy that will be noted for its anti-aging benefits whatever age
    you're I creams are really important the key ingredients to look for here are items that will de-puff
    the eyes and those include such things as caffeine I'm a book in theobromine a great exemplory
    instance of a strong puffing eye cream could be the Loreal eye defense and you might find results really
    quickly with the products similar to this because it does support the caffeine which constricts the puffiness around the

  2. the absolute most abundant protein in your body isn't
    in your muscles it's actually collagen and collagen comprises for one-fourth of the overall body weight that just offers
    you a concept of how prevalent it is in the body and I don't
    believe that we're prioritizing it enough it's pretty darn important you see
    collagen is a protein that's made up predominantly of
    non-essential amino acids now I've done articles discussing amino acids before but basically what non-essential proteins are are amino acids that
    do not have to be derived from the diet they are already contained within the human body so that's a good thing but when our anatomies
    are under stress or were aging or were flat-out stressed out
    our anatomies begin to extract those proteins for other uses which can
    weaken collagen and decrease collagen within the body now throughout this article when I explain a few of the benefits of
    collagen you're likely to see why it's extremely important to make sure that that's in check you
    see the main thing collagen affects our joints you
    see it's got these amino acids in it called prolene glycine and then glutamine they are super good
    for the joints but they're also beneficial to healing and for
    digestive health so when our body starts to break down collagen we don't have
    enough of that it can in fact hurt healing and it could actually hurt our joints and I'll get into greater detail on that in just a moment also
    once we age our bodies produce less collagen that's why we tend to
    see wrinkles you see collagen plays a part in the outer skin elasticity and
    as that starts to deteriorate and go away we see more wrinkles and we start to have a little bit more brittle
    in terms of our skin even our nails and our
    hair so let's enter into the advantages of
    collagen and how you can choose the right kind of collagen for you personally the first benefit I wish to discuss is when I started to mention already and that is joint health you see collagen makes up.

    our tendons it makes up our ligaments it makes up lots of the soft tissue that
    helps our joints like cartilage so if you're a player and you're moving around
    a great deal or even you're even just a recreational lifter
    you're putting a lot of stress on that collagen it's essential that you replenish it through your diet
    you see collagen has a couple of components which can be very
    critical one of which is glucosamine which
    you've probably seen on the shelves of a supplement store before
    and the others are glycosaminoglycans or GA G's these ga-ga are responsible for helping a joint move smoothly if you've ever moved your elbow you move your knee and you type of felt that clicking or perhaps doesn't feel like it's moving slowly you're
    probably depleted in those GA G's and consuming collagen can dramatically help that in reality one 24
    weeks study of 147 varsity and club level athletes
    found that if they certainly were to take some type of collagen hydrolysate supplement that they had a remarkable reduction in joint deterioration over 60 to
    90 days that tells us a great deal about how quickly the human body begins to make use of collagen especially
    as it pertains to joint recovery now another benefit will probably
    be our hair and skin plenty of this might
    of disregard our hair and skin but the thing is they
    could be telltale signs so if you're a man and you're not really focused on your skin layer
    and hair that's something but when you're women you probably are concerned about
    your skin and hair and collagen will make a huge difference in the and
    the vitality of your hair and your skin overall now for men brittle hair and
    brittle skin and wrinkles will actually inform you as a precursor that you might
    start having some joint issues so pay attention compared to that if you're brushing
    your hair you're combing your hair you're gelling your own hair and you start to believe it's brittle you
    could want to appear into consuming some more collagen one
    recent research study found that going for a simple collagen supplement over the span of 60 days
    showed over a 25 to 30 percent reduction in wrinkles in skin that's quite a definite difference there so again another thing that tells
    us collagen plays a huge part in how we look and feel now the main reason I do believe you need to consider consuming more
    collagen and that is digestion you see we hear collagen we think hair skin joints nails but we don't
    really consider digestion but here's something that's really interesting for
    it you see the way in which our digestive tract works can it
    be has little teeny gaps within the digestive system
    that enable us to absorb nutrients now we're talking very small gaps here microscopic gaps.

    now what happens through ways of inflammation and bad
    diet environmental factors eventually the digestive system
    sort of gets a little bit brittle those gaps that
    would ordinarily allow nutrients to come in get so
    swollen that they really become somewhat brittle and those gaps become bigger when those gasps become bigger we absorb a great deal more of what we're eating and
    this means we're absorbing undigested food particles but additionally toxins that can dramatically affect how we feel well here's the thing our gastrointestinal
    system contains a lot of collagen and if we consume collagen eat
    the best foods and take the best kind of supplements we can restore that and
    actually start to close the gaps so we can stop that means of ultimately poisoning the human body and so the question is how do you get your collagen what's the best way to accomplish it well the quickest
    simplest and most down-to-earth way would be consuming bone broth now I've done articles on bone broth before that discuss the benefits of
    it but basically the core benefit is really a collagen you see when you make broth out of a bone you're getting plenty of the ligaments you're getting lots of tendons
    you're getting a lot of the minerals and the collagen
    that's in the marrow in the bump so if you make bone broth and you put it in the fridge you'll observe that it gelatinizes it becomes only a little bit
    more of a good well that's because of the collagen you see gelatin is a byproduct of the break down of collagen so right then and there you've got a
    great source one other option is always to eat all cuts of meat eat a little bit of the fattier cuts eat a few of the parts that may not be probably the most appetizing to you but the next and simplest way that
    you can probably get some collagen is using a collagen supplement they're becoming
    a whole lot more prevalent you can find a collagen supplement more or less any
    online store now but the thing you desire to look closely at is you need to obtain a hydrolysis collagen protein it's a lot simpler to absorb doesn't have
    as most of the gelatin breakdown properties your body's going
    to work with it a lot moreso miss the glucosamine skip the
    chondroitin you go directly to getting a quality
    protein that the body can assimilate the body feeds as always
    keep it locked in here on my articles and I'll help you within the next one

  3. You could certainly see your enthusiasm within the article you write.
    The world hopes for even more passionate writers such as you who aren't afraid to say how they believe.
    All the time go after your heart.

  4. I already take Vitamin D3 & K2 from Nutravita and have done for
    some time. I believe they are a great supplement, as I am generally clear of ailments and
    I really hope to help keep it like that with continued
    use. I bought a container of Nutravita's Vitamins and Minerals as they were
    an excellent price for a year's supply and I have began to utilize them
    as an addition to the D3 and K2, which Natalie at Nutravita confirmed
    was fine to do. As said before, I'm lucky that I am without any ailments, which I think is
    down to me taking Nutravita products to help keep me healthy, hopefully the Vitamins and Minerals will support my continued good health.

  5. Great post. I used to be checking constantly this blog and I'm impressed!
    Very useful information specifically the last part 🙂 I maintain such info much.
    I used to be looking for this particular info for a long time.
    Thanks and good luck.

Comments are closed.

Show Buttons
Hide Buttons