Project Oakdale (CDS Lite) – Exploring Data Migration

I will keep calling it CDS Lite in the smallest hope that this one day might get the name CDS Lite. It just makes perfect sense to give it a name like this. Allright, enough about the hope for a better naming future…

I know the system is only in preview and many things will change in the next months, but I thought I would give it a go at importing data from CDS to CDS Lite. I had no idea if this was possible, but I was curious to see how it would react.

At the moment there is no connecting to the database through normal tools I have had before

  1. XRM Toolbox
  2. SSIS – KingsWaySoft
  3. Excel Import

Every time I try to connect I get the response saying that I have to use an Oauth method to connect.

PS: Notice how Microsoft has put “LITE” in the error message? It’s a small hope, but it’s a hope 😂

So the only way I found out how to insert data in the Preview was via flow

Flow to the rescue

I am pretty sure that the current doc recommends using Flow for data to CDS Lite (Oakdale), but I am sure this will change in the future somehow. How else are you going to fill 2 million records (estimates from docs on the 2 gig DB size). Flow is not intended for data migration, but I had to use the tools I had available at the time 🙂

First I imported 5694 accounts to my CDS database (top row headings)

The CDS Lite (Oakdale) was empty for accounts, and only included the following columns.

The flow was a little tricky. I actually had to create the flow from the CDS Lite (Oakdale) environment. It did not work otherwise. Current environment connector was fine for writing to CDS – Lite (Oakdale).

The flow will only do 512 records if you don’t open up the paging. Go to settings of the connector and increase the threshold.

I started the flow and patiently waited.

27 minutes later the accounts had been migrated with a warning. I could only find 1 error in the migration, so I consider that a success. All of my accounts were now over to the CDS Lite (Oakdale).

What next

This is all just a beginning of a longer journey for the CDS Lite (Oakdale). Everything is in preview, so there are many things that will change once this is released, and a few years from now. I just wanted to see how to get data in to the system. The reason why might be more clear in a later post where I describe what I see in a future customer scenario for CDS and CDS Lite.

If Gus creates a top 10 PCF components, I hope this one makes it ;)

If you ever had a problem with navigating between entities for updating data, this PCF component will absolutely save a lot of time.

Clement Olivier
https://stuffandtacos.azurewebsites.net/
@Clement0livier – Twitter
https://github.com/carfup/PCFControls – download the tool

He created a PCF component for quick updating related entities without having to leave the record.

Updating related LOOKUP

Internally we have all of our active projects in a list. For every project we have a customer contact registered for continuous surveys (Power Automate, Forms Pro, SMS.. Cool Stuff). For the system to work correctly we need to make sure we have both the phone number and email address updated at any given time for the automated surveys to work.

What would we do without devs!!:)

DataFlows – Behind the Scenes

I have been posting a lot from the Data Flow in Power Platform, because I needed to use it internally. So far this tool is extremely interesting, but I am not sure it is 100% clear to me how it all works.

This is why i decided to import 94.000 contacts to 1 account, to see how fast/slow it was. I also wanted to monitor how much this affects the API or any other stuff in the CDS/Dynamcis base. This post is mostly for my own curiosity.

I decided to start with a Dynamics instances that didn’t have any users active, and no data. The following data is gathered from https://admin.powerplatform.microsoft.com.

Data:
Excel rows = 94884
Excel file size 4,7 megabytes

Excel File for Sample Data – Source for my list. I did clean it a bit before importing it in, and added a column for Account Number = 1234

Data BEFORE the import

I include a lot of pictures for the “before” situation. I was curious to see what happens after.

Import

As always the first thing we do is setup the Alternate Keys in Dynamics. For account, I was only using account number.

For contact I was using email as the unique identifier

Look at earlier post if you wonder how to upload the file to DataFlow. Integrations

This is what my Excel file looked like. I am using First, Last, Email (id) and Account for lookup.

Set the Account Lookup

The E-Mail is the unique identifier for contact

The work around

Open this post to understand why Customer lookup fails, and how to get around it. I will skip to the results here instead. https://crmkeeper.com/2020/03/10/data-flow-set-customer-lookup/

The Result – “Run Project”

The run itself didn’t show much of timestamps, so i opened an advanced find

First Record

Last Record

So it used 1 hour for 94883 records. Not really sure how we can measure this anymore, because of speed caps hitting a little randomly. I would say this is decent speed when importing.
94883 contacts / 60 minutes / 60 seconds = 26,35 contacts pr second

The storage grew a fair bit….
2.72GB -> 3.44GB = 720 MB = 0,007 MB pr contact

This is the old Address entity.. Anyone even use this anymore???

How about API calls?

The first time i ran this, it was only create. Then i did a second round, and all I got was 13,544 API calls. I am not sure I trust the Analytics yet!

Conclusion

After running the import 2 times, i never got any indication about API usage. There must be something wrong with the statistics here? I did almost 100k upserts, and there is nothing to prove that I have accept for the Creates under performing operations. This alone would make this an unbelievable integration platform for CDS because of the API’s we are saving. I understand that this is probably not the case, and there must be something wrong with the reporting.

That being said. No one has been able to tell me if this cost any money. If this is considered a part of Dynamics/PowerPlatform license i am impressed. A truly hidden GEM.

https://docs.microsoft.com/en-us/power-platform/admin/data-integrator

I really hope they continue to work on this product to make integrations simple! It’s the gives me the power to integrate without knowing code!! #lessCodeMorePower

DataFlows – Set Customer lookup

I still don’t know if this is local to my tennant, but I have tried in 2 different orgs, and I always get an error with Customer field for Contact. This post is almost identical to the Set Parent Lookup, but there are some differences in the end.

Setup Contact

I have one Excel file with 6 contacts. They are linked to the account with number 311. The unique identifier I have on contact is email.

I setup a key for email on contact, so that the system will understand what to write to.

Setup Account

On the Account I have 2 fields for number matching, because I have different systems that integrate, but it doesn’t matter in this case. PO Account Number is what we are going to use, and I will try to explain why (based on my findings).

PS: Both Account Number and PO Account Number are Alternate Keys on Account!!

Setup Data Flow

Go to https://make.powerapps.com and start a new Data Flow project. In this case I am choosing the Excel file for simplicity. I have uploaded the Excel file to One Drive, so that it is available at all times online.

In the Excel file I had to make sure that the first row was headers before i continue

During the mapping I will see both the Alternate Keys I have for account. Normally you probably only have 1 Alternate Key for Account, but my setup requires 2 because of 2 different systems integrating against 2 different numbers. For blog i am using value 311, so it doesn’t really matter here.

When done mapping Account Number (again it doesn’t matter if I choose one of the other) continue to the next step. I choose a manual refresh, and it get stuck here.

You will also see an error in the data flow projects.

https://admin.powerplatform.microsoft.com

Open data integrations, and open the one that failed.

From here you can open the mapping table

This is where you most likely will see a missing mapping. For some reason it cancels out my mapping. I have tried this in 2 environments and same issues. Every time i choose the Customer Lookup i have this problem.

Click on the destination field, and navigate WAY to the bottom.

This is where I believe the bug is located. Data Flows happens to be sensitive to what Alternate Key was created last. This key is the last thing that I published on Account, and therefore it is in the list. If you only have 1 alternate key, this will show.

Again, it doesn’t matter in this demo because i have 311 in both KEY fields.

The Work Around

After you save the changes, open Data integration. Here you will setup a schedule to run from the admin.powerplatform.microsoft.com.

Click save schedule and wait for it to run.
PS!! Remember to stop the schedule when done!

For the Final result you will see 6 contacts connected to the Account

Next up is how long does it take to import 94000 contacts to CDS via Data Flow!

DataFlows – Set parent lookup

This blog post is step 3 of the Data Flow setup in combination with On Premise Gateway. In the last post in imported all of our ERP accounts to the CDS database. Now I wanted to import all active projects, and connect them to the the Accounts in CDS. There are a few odd things you have to do in the setup to make this work, but I will try to outline this without being to detailed..

First off we need to get familiar with the Alternative Keys in Dynamcis. In Account I created alternative key for AccountNumber. I need to do the same for Project.

The field is also represented on the form. I choose to keep these as “Single Line Of Text”. Just keep it simple, because this doesn’t need to be advanced.

This is important so the import is able to match every time runs. Remember that the Data Flow is doing UPSERTS. It will create a new record if it can’t find the project number. Imports CAN’T connect to GUID’s because my ERP system does not have the CRM GUID.

Make.PowerApps.com

The most important thing you do here, is make sure you have the correct environment!! 😉

If you don’t understand this setup, look at my first blog about the DataGateway

The important pieces of information here are:
* Project Number
* Project Name
*Account Number

This is all i need to create a project in CRM and connect it to the Account

Connecting to Lookup

This is where you need to pay close attention. First you choose the project (or other entity) from a list of existing entities. The Alternate Key will display if you have done this correctly. If nothing is there you have to go back and start again with alternate keys

There are 2 lookups on project. Account and Contact. Account has 2 alternate keys, and Contact has 1 alternate key.

Account:

Contact:

This is why you will see 3 fields in the beginning of the mapping
1. ncg_Account.AccountNumber – Account looup identifier
2. ncg_Account.ncg_POAccountNumber – Accound lookup identifier
3. ncg_CustomerContact.EMailAddress1 – Contact lookup identifier

I am using the AccountNumber for a different purpose, so let’s just forget this one for now. I am using the Account Number from ERP to the ERP field on the Account entity to create a match.

Schedule

After you have set a schedule, you should see the results in CRM:) In a later post I will tell you why I am connecting the project records to dynamics. A quick hint, Forms PRO and NPS!

PowerPlatform Saturday Oslo 2019 – Attendee

Malin and Mark wrote good post detailing most of the event, but I thought I would add my personal thoughts from some of the other sessions

Both Malin and Mark Smith wrote great summaries
Malin Martnes
Mark Smith

Keynote

Sarah and Marius were speaking of Equal Opportunity. A highly interesting topic that is as relevant today as ever before. One slide caught my eye that I really liked.

Take ownership of your own environment. Don’t wait for others to create an equal opportunity environment, and then complain if they don’t. If you see the chance to own a situation, take it.

If there is a table you want to sit at with a chair, take the chair and sit down. Don’t wait for others to offer you a place at the table. You take it if you want to be a part of it!

Sarah Critchley (quote as I could remember it)

I have grown up in a family where the women are strong, have great educations and excel at their jobs. I am sure they have faced issues along the way, but they keep claiming their place at the table every time they can.

Malin Martnes – Extending Talent

I am sure you all know how proud I am of Malin, so I couldn’t go without writing about her. Talent is not a product with lots of customers yet, but we are working on a lot of exciting opportunities at the moment. We are always in the challenger position when talking about this product going head to head with companies that have delivered HR for many years. This often ends up in a function feature comparison, but one thing we now can focus on is the ECO-System.

Talent integrates to the CDS, and therefore we can leverage all of the cool features of the PowerPlatform. Malin showed a lot of cool examples where PowerApps were assisting in the hiring process.

Stay tuned for updates on Malins blog about talent for our customer journeys later this year!;)

Julian Sharp – IOT

What I liked about this session was hot interactive it was, and how “low code” he was trying to make it. I gained some insights to the IOT challenges when gathering messages in different ways. Also I see the impact of having a device you can pass around in the room that is working while presenting. It was showing live data on the screen, presenting different messages to us as we were passing it along. He later posted the slides on Twitter

Jukka Niiranen – Canvas vs Model

I find it interesting to listen to Jukka. He is like a rapper’s lyrics when talking. There is often some deeper meaning to what he says if you listen closely. During this session he was open and honest about his view on the Canvas apps, and the battle Canvas vs Model is now facing. Will we ever get to the point where these 2 become 1? The answer is most probably, but how!! 🙂

He also had a wonderful slide with the great Scott Durrow in focus. I think we all agree that Canvas is absolutely becoming a code app, even though it might not be complex code. As a functional consultant I say code, but a developer says “cody” to the syntax;) hehe

Mark Smith

The pictures bellow are from Mark’s session. I really loved this session because he made the participants work! The topic was CDS modeling and what you should think about. He made it clear what was out of the box, and how you should begin to think about creating new models not based on the standard applications.

Anything interactive like this is just AWSOME. The participants loved it, and had a great time while working together on the data model. Interactive learning is the best way to learn.

So in conclusion, I had a great time at this event. Hope to repeat this again next year in Oslo. I also hope to see you all again soon:)

PowerPlatform Saturday Oslo 2019 – Speaker

So Power Platform Oslo was a unique experience for me, since it was the first time I was on stage at a community event. Marius Agur challenged me to create a presentation that would showcase my view of Dynamics from a business perspective. I am not the most technical person, so I try to create solutions based on simplicity for the end user within a “do it yourself approach”.

3 ways the Power Platform can increase user adoption

I was using our own company as example for this demo, and demonstrating the issues we have had when migrating from HubSpot to Dynamics 365. A journey with lots of bumps in the road, and long nights to figure out the best solution.

1. Connecting Flow to API

Part One showed how we can connect flow to an API and receive real time data from sources like Bisnode, Proff.no or in my case BRREG. I will blog about how to do this soon.

2. Creating a PowerApp for simplicity

Part 2 was showing how you can create a powerapp to simplify not only the user input, but also create a bridge between those who don’t like Dynamics CRM, but accept using a PowerApp, because it is new and fun. Blog about this will also come soon.

3. Integrating to onpremise data source

The last step was showing how we could integrate our CDS/CRM solutions to an onpremise ERP solution without a single line of code. Read more about hit HERE

People to thank

Chris Huntingford: @TattooedCRMGuy
I would never have even thought of doing community or personal exposure if it were not for your exceptional outgoing personality motivating me when you where in Oslo. I know it sounds odd, but I wanted to be like you, and spread excitement and joy to others. Somehow you made something in my brain say “why not” create a blog and speak my mind! MASSIVE thanks..

Megan Walker: @MeganVWalker
Getting to know you and understanding how you use the tools in regards of blog, wordpress, youtube has been very important. You were also so kind to be my first interview subject 🙂

Mark Smith: @nz365guy
Thank you for hosting the 90 day Mentoring Challenge. Even though you were speaking to the masses, you make it personal to participants. I have also shared many conversations with you that mean a lot to me. Thank you for guidance in som many different levels in regards to creating a profile. I might not be the fastest at getting everything done, but I will get there.

Malin Donoso Martnes: @MalinMartnes
Thank you for being persistent in your work. It inspires others to try:) You gave me the final push to submit my talk, and I don’t regret it!

PowerApps + CSS

I know the title be a little misleading. PowerApps doesn’t support CSS as of now (maybe sometime in future), but you can create a CSS light for simple tings while we await the custom Themes for PowerApps.

I find it extremely annoying when creating a large app and you need to customize visuals. If you make a change to one “screen” you have to make the same change over and over again.

In this example I declared a few variables in the beginning of the PowerApp, and one of these is the button color “RED”.

As you can see, my button color will be read when I choose to give them this color.

So at some point I decide that I want the color to be the same as my heading, and all I have to change is the color code in the app “onload”.

The result is matching buttons without having to manually change each color setting!:)

NB! after changes in the “onload”, you have to refresh the browser to see the effect.

Interview – Julian Sharp

Julian Sharp was recently in Norway to teach the App in A Day. I was one of the lucky few that got a spot in his class. Sure I have been in Dynamics since 2007 and should know it all, but that is the only once certainty. I will never know it all, and there is always something to learn from a person that has a different perspective on things.

Thank you Julian for the great class, and the advice during the interview! See you again soon in Oslo for the PowerSaturday August 24th.

Follow Julian in the following channels
Blog
Twitter

PowerApps and Microsoft, Where does it fit?

While some people say that PowerApps is Office, others will say PowerApps is Dynamics. They are both correct.

Canvas

Canvas apps si exactly what it sounds like, a blank canvas you create. Here you can choose where to store data: SharePoint, SQL Server, Office 365, OneDrive for Business, CDS , Excel, and many other data sources. For the ease of use I often see storage within SharePoint lists here.

Model Driven

Model Driven app is essentially the Dynamics database, but without all of the bells and whistles that come with Sales/Service/Marketing/Field Service/Project Service etc. It is a clean database where you can start from “scratch” and create fairly complex applications within a short period of time.

Why make a fuss about it?

Well, if you know Microsoft as an organization you will understand me asking this question. Office is now called Modern Workplace, and Dynamics is Business Applications. PowerApps is both, so where does it belong. What team will be the ones promoting the product, and who will gain from selling PowerApps licenses?

Up-sell, Down-sell or Cross-Sell?

Office – “Up-Sell”

While Office consultants se this ass an “Up-sell” and potentially expensive
Office E3 20$ + Plan 1 7$ = 27$
+ Plan 2 20$ = 40$

Dynamics – “Down-Sell”

Dynamics sees this as a “Down-sell”
Dynamics Sales 95$ —> Plan 2 40$ = 55$ savings pr customer

PowerApps – “Cross-Sell”

If you read Jukka’s post about PowerApps maybe what we are doing is creating a bridge between it all, the “Cross-Sell”. I can no longer only focus on Dynamics, and have to embrace PowerApps Canvas and Model Driven. Eventually I think the Office users also will see the benefits of the Model Driven, and then we can start adding value inn all areas to a common database.