Woohoo - You can query Dynamics 365 (CDS) with SQL….again

When was the last time you heard anyone getting excited about SQL? #HoskWisdom

 

It’s a crazy world when Microsoft announces NEW functionality of being able to Use SQL to query data in CDS and the Dynamics/Power Platform community get excited about it.

Some things go together to make something beautiful,

  • Strawberries and cream
  • Chaz and Dave
  • Nerds and computers

Now after a lengthy absence we have SQL and Dynamics 365, back together

I was trying to think back when I wrote my last SQL statement, I’m guessing it was with on-premise Dynamics project in 2013. Before then I remember spending 2 days trying to format an SSRS report.

This made me realise there was more to life than SSRS reports

What is SQL back?

Microsoft announced that there is an SQL data connection available to the Common Data Service. This connection provides read only access to entities.

Be aware is a preview, soit might break and there might be bugs.

Read more here — Use SQL to query data (Preview)

You can query CDS with SQL Server Management Studio. Wow, I haven’t used that beast for a while.

Other interesting points

The security is based on the Common Data Service logins.

Supported operations

  • Filtering
  • Unions and Joins
  • Count & Max
  • Select
  • Batch operations

What won’t work

  • Updates
  • Inserts
  • Deletes

Anything that modifies data will not be allowed.

Non SQL data types won’t work (makes sense)

  • Binary
  • image
  • virtual
  • file
  • xml

FetchXML no more

FetchXML can be a pain but I was surprised by the positive support for the ability to integrate CDS with SQL queries. SQL is good but writing SQL queries doesn’t bring joy to my heart.

Why didn’t Microsoft create this or an SQL emulator years ago when Dynamics 365 went online, this would have made it easier for people to transition to Dynamics online, particularly the report writers.

SQL is more powerful than FetchXML and its easier to join tables. It’s useful to query the database to diagnose problems.

I don’t worry about what’s in the Dynamics database much anymore, I use it as a service and let Microsoft deal with it. The disadvantage of letting Microsoft sort out database issues, is after raising a support ticket it can take Microsoft a while to resolve the issue.

The last few premier support tickets I have raised have been resolved quickly and the engineers knew their stuff (but that’s not common in my experience of raising Microsoft support tickets).

SQL 4 CDS tool

I attended D365UG Birmingham virtual meetup and during the a talk from Matt Beard and he mentioned SQL 4 CDS an XRMToolBox plugin which gives you a SQL server management studio type interface created by Mark Carrington

I installed the app on my XRMtoolbox and its easy to use and has a nice autocomplete functionality.

It shows you the converted FetchXML statement from your SQL query and it allows you to delete data from Dynamics 365!

I encourage you to download and try it, rarely have I seen an XRM plugin that is so easy to use. It’s useful to have a tool which can query data in Dynamics without using the advanced find because Microsoft hides some of the entities and fields.

Replicate Dynamics to Azure SQL

An alternative to write SQL queries with Dynamics 365 is to replicate your Dynamics 365 database to an Azure SQL database using the Data Export Service. I discuss this in the blog post below

Dynamics 365 and replicating to an SQL database for reporting

other reading

 

How will IT projects change when the lockdown ends

The only way to make sense out of change is to plunge into it, move with it, and join the dance. Alan Watts

After much of the world has been in lockdown for the last month or more, the curve has been flattened and plans are being made to reduce the lockdown, go back to work and restart the economy.

What will it be like? Can we go back to normal until everyone is vaccinated or we get to herd to immunity.

Let’s imagine

The government will ease the lock down and non-essential work as long as

  • you can social distance at work
  • Anyone with symptoms will self isolate
  • if your job needs to close contact, you will need PPE or other protective measures

There is a probability that there won’t be a vaccine, there is no vaccine for SAR’s. Reading Bill Gates article What you need to know about the COVID-19 vaccine. The fastest ever vaccine was created in 5 years. They hope to save time by removing red tape, starting building manufacturing capacity now and with more projects it increases the chances of one or more being successful.

People are optimistic, but what is the outside view The outside view — how long will it take to create a vaccine for Covid-19?.

We might have to live and work with the coronavirus for the foreseeable future.

IT Projects

It’s likely the guidance will be if you can work from home, you should. Many industries have shown they can cope with everyone working remotely. This will be the safest way to work and reduce the spread of coronavirus.

We can deliver IT projects,

Understanding the As IS

  • Screen shares of using existing software
  • Process maps can be created
  • Online work shops to discuss existing functionality

Gathering requirements

  • High level requirements are created for bid process
  • Workshops can be done via online meetings
  • User stories created, assessed and signed off by both customer and supplier

Development

  • User stories can be developed
  • Meetings and progress done via teams/slack or alternative
  • Test scripts written based on stories and tested when development finished
  • Demo the functionality at the end of the sprint
  • User can try the system and give feedback

Setting up environments

  • Configuration of IT systems is already done remotely
  • Environments are commonly cloud services or configured on Azure/AWS or Google.
  • Remote access can be setup and infrastructure is configured

The difficulties working remotely will be creating the relationship with the customer and collaborating is more difficult. Projects can and will be delivered remotely or with reduced contact.

Remotely working has been on the increase, Covid-19 has accelerated companies digitisation plans to the point they are no longer plans and are now a reality.

Effects of lockdown

Businesses will open up, but they will need to implement social distancing. This will reduce the capacity of many businesses.

Shops can open with a reduced number of people in the shop at the same time. If you are a pub, you can do takeout and maybe you could have a quarter capacity with table service.

Airlines will be severely hit, people won’t feel comfortable travelling and would need to reduce capacity to a half or a quarter. Will this make enough money for airlines?

The businesses that can work remotely will work remotely.

Will schools go back? it will be difficult for the economy to restart if parents have to stay at home to look after their children. This isn’t just availability of schools and nurseries because many parents will be worried to send their children to school.

Governments will say the economy is back open but there will still be many people who will be scared and won’t embrace the working with the virus.

The new normal

If we assume that 2019 was normal then we won’t get back to the 2019 levels of normal for potentially years. The world will have to learn to live with Coronavirus for the next year(s).

The world will go at half speed for much of the economy. It will drive with the handbrake on. This will cause many businesses to reduce their wage bill and reduce staff. The unknown will cause business to conserve cash and reduce spending on recruitment, marketing and other expenditure. This will add further pressure on the economy.

Talent

There will be many people made redundant because companies need to cut costs and staff are one of the biggest costs.

This is an opportunity to hire good people for the companies who flourish in this environment. The recruitment process will mostly done remotely and face-to-face interviews could be cancelled for some time. This makes hiring more difficult because body language plays an important part in creating a positive relationship.

Talented people who would usually be unavailable might consider moving if you can offer excellent opportunities and more safety.

An interesting article — now is an unprecedented opportunity to hire great talent

IT Projects

IT projects can be delivered remotely and many companies will need to upgrade their existing system to enable staff to work more effectively remotely. So there will be opportunities with companies speeding up their digital strategies but on the flip side there will be many companies conserving cash and reducing expenditure.

IT Projects could be stopped without warning and budgets will be tight. There will be opportunities for digitisation and in industries that are thriving in the new society that will need to leave with Covid-19.

Why are you so busy? And what can you do about it?

“It is not enough to be busy, so are the ants. The question is: What are we busy about?”– Henry David Thoreau

Whenever you ask someone how they are, they often reply busy. Everyone is busy, to-do lists grow longer each day, meetings take up half the day, and that’s before any problems appear from now where to cause disruption.

What about if I asked a different question?

Are you productive?

Being busy makes you feel useful but are you using your limitation time doing the tasks that will bring the most value. Ants and Bees are busy but they have to be busy to survive. Being busy isn’t enough to bring long term gains to your project or career.

Being busy is like treading water, it keeps your head above water but it doesn’t take you anywhere.

Army ants if they lose the pheromone track, will blindly follow the ant ahead. This can lead to ant mill, which is where ants get stuck in a circle and will eventually die of exhaustion. This minor version of this can happen at work, where you lose sight of where you are and get caught up doing tasks and being busy. 

It’s time to pick your head up and make sure you are heading in the right direction before you lose motivation and become exhausted.

Be less busy

The first step in understanding why you are busy is to understand what you are doing. Reasons for being busy.

  • Doing tasks you can delegate or other people can do
  • Tasks which are the responsibility of someone else
  • Habit
  • older priorities

If you don’t protect your time and prioritise, you work on the priorities of others, at the cost of your priorities.

To get a project moving forward, I did my job and another role because a person wasn’t doing their job well. It was difficult to give those tasks back; the work was being done.

When you do the work for someone it’s a short term fix because their performance is hidden. If someone isn’t doing well in their role, they can improve their skills (training, mentor, help), put in more effort or move to a position they will enjoy and feel motivated to do.

When you see a problem, stop and think whose problem is this? If it’s not your problem, highlight the issue and help the person but don’t do it for them. Help them learn to fish, don’t just give them the fish.

Saying no is a superpower, you need to understand your priorities and your responsibilities. Don’t overload capable people, create and train more.

Just because you can do something well, it doesn’t mean you should. If it fits with your goals, great but make sure you think before you say yes. You have a finite capacity, use it on the tasks that matter most to you.

Doing it yourself

One reason to be busy is being delegated tasks, another is not delegated tasks. A trap newly promoted employees fall into is doing too much of the work themselves because they trust their output. Before long they find themselves overloaded and their team with spare capacity.

If you can delegate, you should delegate

In the short term delegating allows you to focus on more important tasks and in the long term it develop other people. Long-term benefits cost you in the short term because you need to give more help whilst the person learn and develops the skills needed.

Growing people helps the career of everyone, as a team you will be able to do greater things.

Think long term

To get somewhere fast, know your destination, where your career is heading, so you can get the skills, knowledge, experience and relationships you require.

Once you identify your long-term goals, you will see opportunities to work towards them.

Visualise your future role, you need to work towards being that person today. We can lose motivation because it’s not always enjoyable doing things we aren’t good at. Theory helps us improve, but the fastest way is by doing and learning from the feedback.

You don’t get to be a talented programmer by reading/watching about writing code, you become a brilliant programmer by writing lots of code and reading/watching.

Dynamics 365 — how solution layering can help you resolve solution updating problems

A problem clearly stated is a problem half solved. Dorothea Brande

I was importing a managed solution into my test environment, only to find it wasn’t updating the Canvas App. There can be many reasons for solutions not updating and this post will discuss the cause and the solution.

Environment setup

Below are the environments we use.

Dev → Dev Master → QA/UAT/Training

Developers make changes in the Dev environment and creates a patch, deploys to Dev master. Dev Master is the environment is used to create our managed solution, which is imported into the non development environments. It’s managed because no one should change customisations outside of the Dev environment, this helps to control the environments and keep them all in Sync.

Once your environments are out of sync your testing becomes unreliable because you can’t be sure what customisations are triggering and working to in the whole solution.

The problem

A model driven app was updated, adding more views to an entity but when deployed to the QA environment the new views were not showing.

  • Dev + Dev Master — 10 views
  • QA — 3 views

What was causing QA to only show 3 views?

I wanted to confirm the changes had been checked into source control and the AppModule.xml had been updated.

The solution packager breaks down all the customisations in your solution into xml files. This is great for checking into source control and managing your customisations. Below are the folders it creates.

In the AppModules folder, I found these

I found the entity I had changed (no it’s not really called HoskEntity)

<AppModuleComponent type="1" schemaName="HoskEntity" /><AppModuleComponent type="26" id="{aba4dde6-7a10-ea11-a811-000d3a4ab96a}" />
<AppModuleComponent type="26" id="{b174159f-04b9-e911-a82e-000d3a47cb1d}" />
<AppModuleComponent type="26" id="{2fe72247-43b9-e911-a82e-000d3a47cb1d}" />
<AppModuleComponent type="26" id="{b3589b99-42b9-e911-a82e-000d3a47cb1d}" />
<AppModuleComponent type="26" id="{001594bd-43b9-e911-a82e-000d3a47cb1d}" />
<AppModuleComponent type="26" id="{7bd435d8-43b9-e911-a82e-000d3a47cb1d}" />
<AppModuleComponent type="26" id="{3a2bdec8-7a10-ea11-a811-000d3a4ab96a}" />
<AppModuleComponent type="26" id="{19a41b6a-43b9-e911-a82e-000d3a47cb1d}" />
<AppModuleComponent type="26" id="{1670e091-43b9-e911-a82e-000d3a47cb1d}" />
<AppModuleComponent type="26" id="{ffa5f67d-43b9-e911-a82e-000d3a47cb1d}" />

If you can’t read Dynamics xml, this page AppModuleComponent helps

  • Type 1 = Entities
  • Type 26 = Views

My entity has 10 views, which is what I expected and confirmed the problem isn’t here. Bug fixing isn’t about finding the problem straight away, every assumption you clarify takes you closer to the cause of the problem.

QA Environment

In the QA environment to the model driven app and pressed the solution layering button. This is a useful tool enabling you to see what customisations are layered on top of each other. Different customisations in different orders can produce different results.

Solution layering button has two other useful features.

you can click on the layer and see the customisations

There is a button which allow you to remove those customisations (if you click the three dots).

Read these articles to learn more

The customisations on the default layer, show the xml of the AppModule. I searched for the entity I had changed and found it only had three views.

<AppModuleComponent type=”1" schemaName=”HoskEntity” />
<AppModuleComponent type=”26" id=”{b174159f-04b9-e911-a82e-000d3a47cb1d}” />
<AppModuleComponent type=”26" id=”{2fe72247–43b9-e911-a82e-000d3a47cb1d}” />
<AppModuleComponent type=”26" id=”{b3589b99–42b9-e911-a82e-000d3a47cb1d}” />

The manual layer was the highest layer and above of the managed layer. This is a common scenario that unmanaged changes can block/freeze and stop managed changes.

Repeat the Mantra

Only changes customisations in DEV

Out of sync environments hide bugs which then appear in production. The testing you do in an environment that isn’t the same as production is not as useful for predicting how production will work.

Fix

To resolve the problem I removed the unmanaged layer which was the layer above my managed solution. When I removed the unmanaged layer, the managed solution then popped to the top I then saw the 10 views I was expecting.

In this case I knew there shouldn’t have been an unmanaged solution because no one should be making changes in QA, any changes to customisations should happen in DEV and be pushed through to QA.

Unmanaged changes in an environment are a common cause of customisations not updating and the solution layer button is good method for finding these.

Related articles on bug fixing

How development used to be (15 years ago)

Sometimes it pays to stay in bed on Monday, rather than spending the rest of the week debugging Monday’s code. — Christopher Thompson

I was listening to the Orb song little fluffy clouds and it made me think of what development was like when I was young. Whilst on lockdown and working from home, it highlighted how much the world has changed for developers.

I have been a developer and other roles for the last 20 years and the development process has changed a lot, my knowledge has grown and no code solutions are on the rise.

Starting off as a developer is tough because everything is challenging and you have no knowledge or skills to help you. The only way to improve is to try fail and learn.

You start off getting stuck every 10 minutes and keep asking your colleagues question, slowly you improve and make fewer mistakes.

Here are some of my memories from 10 to 15 years ago when I was an eager junior developer

Builds

Builds used to take hours and sometimes the best part of a day. Due to the length of the build we only released once a month or some ridiculous length of time.

I worked for smaller companies to begin with, so we didn’t have time to automate the build, this was only something I read about. Why I didn’t invest time in this area is mystery, but then no one else did either.

Build and releases were lots of manual tasks, tick lists and fixing odd bugs.

Setting up your development machine

Once you set it up and got it working, DO NOT CHANGE IT.

I remember new starters trying to setup a new machine and wasting days with it not building, no one could remember what they had done to setup their machine.

Everyone’s development machine was different.

Servers were real servers that sat somewhere in the building.

There were no online services.

Frameworks

MVC was a big thing, it was the coolest thing in town.

For Java work I started off using Struts and then moved onto Spring. I say framework, what I mean is a bunch of xml files which you configured a few things.

I remember having my Tomcat server to host the pages on my dev machine.

Merge Hell

You could hit with merge hell at any time and this was a serious problem. It would take hours to resolve all the merges.

I think the reason merging was more painful was because we merged the code every 3 or 5 days. Not every day like we do now.

Formatting

There used to be big debates on formatting your code. Do you put the bracket on a new line.

The use of Indents could involve discussions with multiple developers for hours. The end result would be everyone would go back to their own way of doing it.

Databases

We connected to databases in the code with ODBC connections. Every needed to know SQL and be willing to create tables and write queries. 

Blogs and the internet

When I started out you were lucky if there was one blog post on the topic. Now there are blogs, documentation, walkthroughs, videos and online training courses. The barrier to learning isn’t a lack of material, it’s a lack of time.

The lack of information made integrating and using new tools tricky. I remember trying to get things to work and struggling to get samples to work.

The plus side was you had to learn to do things yourself , instead of having the answers given to you and the code to copy.

Books

At the early stage of your career the books you read can make a big impact on the way you see the world.

I remember reading Effective Java and it blowing my mind how professional and skilled a software engineer could be. To design simple code which could manage dependencies and be reused was great. This book showed me how far I had to learn to become a good software engineer.

Other influential books

For more recommends read the blog post below

Recommended reading for software engineers

Visual Basic

I first saw Visual Basic at version 5 but it became big with Visual Basic 6. Visual basic mixed a GUI front end with code. You could quickly create an application quickly.

Creating web pages with Java or ASP.NET was painful, everything was very manual.

The downside is the language was like writing Excel formula’s, which would turn into chaos if you the application grew in size.

I’m sure there are some Visual Basic contractors making a good living supporting applications written in Visual Basic 6.

Writing code is difficult, writing it in Visual Basic 6 was verging on impossible.

Environment

The developers used to sit together in the same area, a developer zone. In my first few jobs I worked for companies who developed products. We didn’t have noisy open offices, we had areas where the different teams would nest.

In early teams I worked in there would be one or two moody developers who wouldn’t talk to anyone. They would be given work and a week later that work would be done and they would get more.

Every company would hire one nutter, who happened to be developer. This would be a rich source of amusement and gossip in the company.

There were no agile teams, daily stand ups and noisy open offices.

Working from home

This didn’t exist, it wasn’t an option. The first few jobs had desktops, and it wasn’t until 2008 I got a laptop. You can’t work from home with no internet and your computer is a big fat tower desktop machine.

Fridays

Friday was going down the pub at lunchtime. Having a pint and some bar snacks.

Conclusion

I’m not saying it was better, I am saying it was different. What strikes me is my lack of knowledge and how difficult it was to know what to learn and find resources to learn it.

Getting things to work was a fear and a motivator, it shows how reliant we have become with the internet. It highlights how great the developer community is and how much knowledge we share with each other.

Dynamics 365 and creating a reporting database

Data migration takes at least 4 times longer than everyone estimates #HoskWisdom

It’s a common requirement to create a different data source for reporting this allows data to be aggregated and reduces the load on your production instance of Dynamics 365.

This post will look at the options to replicate your Dynamics 365 database to a SQL database.

Azure SQL database

Azure SQL is common database that is familiar with many report writers. The common requirement is to copy your Dynamics 365 database to an Azure SQL server.

What are the options for you to do this

The Data export service

The Data export service is a good choice because it’s free and can works with Dynamics 365. Choosing what entities you copy is setup with configuration with no code needed.

The Data export service is an add on that replicates your Dynamics 365 database (or CDS) into an Azure SQL Database. After the initial copy it syncronises the changes by using changing tracking.

It copies to Azure SQL subscription, or SQL Server on an Azure VM

Here are some links to learn more

Pro’s

  • It’s free
  • integrates with Dynamics 365 and Azure SQL with configuration
  • It has an API but it can work with configuration
  • Monitoring and diagnostics on sync status
  • Full initial syncronisation
  • Metadata changes are fully syncronised

Con’s

If you do any of the below in an environment, you the export profile must be deleted and recreated

  • Restore an environment.
  • Copy (either full or minimal) an environment.
  • Reset an environment.
  • Move an environment to a different country or region.
  • You need to turn on change tracking, which can slow down the system a bit I believe

KingswaySoft

KingswaySoft is a popular tool for data migration, it works with SQL server and has a connector with Dynamics. You can use KingswaySoft to synronise data to an SQL database.

Some useful articles

Pro’s

  • KingswaySoft is powerful and you can transform the data.
  • There are lots of developers with experience and the learning Kingswaysoft is quick
  • Free developer edition
  • Cheaper than Scribe

Con’s

  • Purchase a KingswaySoft licence
  • Developer to write the packages (it’s not code but does need a professional approach)
  • You need to create an SQL server with SSIS and configure firewalls
  • You need visual studio downloaded

Scribe online

Scribe is tool to integrate and syncronise business applications and it has an out of the box connector for Dynamics 365. Scribe online is a service, so you don’t need to download anything.

I haven’t used Scribe or been on a project with Scribe but people who have used it, usually recommended it.

Pro’s

  • It’s configuration
  • online service, so now need to download or have servers
  • easy to learn and use
  • no knowledge of SQL or SSIS needed

Con’s

  • Purchase licence
  • Developer to write the packages (it’s not code but does need a professional approach)
  • no knowledge of SQL or SSIS needed
  • Can be slow sometimes and no way to speed this up
  • Costly. At least 4 times more expensive than kingswaysoft

Scribe versus KingswaySoft versus Data Export service

It seems the Data Export service is the best choice and it is if you only want to replicate your Dynamics 365 database. Kingswaysoft and Scribe can do it and a whole lot more. KingswaySoft and Scribe can integrate between systems with powerful transformation functionality.

In the scenarios where integration has been needed Kingswaysoft has been chosen over Scribe because Kingswaysoft is a lot cheaper and the project has had developers on it. The big benefit of Scribe is you don’t need to know SQL or SSIS.

I haven’t used Scribe or been on a project with Scribe but anyone who has usually recommended it.

Data migration and integration always take longer than you think and is more important than most realise.

Other options

Microsoft Power Automate and Logic apps are no code tools to integrate Microsoft business applications. These are power no code solutions with Azure capacity. The connectors and improvements Microsoft have made in Power Automate has made it a powerful tool.

What use to take C# code and a week, can now be done in a few hours with Power Automate and Logic apps. If there are a lot of integrations, this choice might lead to more maintenance and there is some missing functionality which could make it unsuitable.

Azure Data Factory

Azure data factory is a is Azure cloud ETL service that can run SSIS packages. It’s a service so you don’t need to worry about any infrastructure. It has a code free UI, it’s configure rather than code.

It’s offers Azure scale and connects with Microsoft services out of the box. The pricing seems competitive and based on compute/usage.

The downside is few developers have experience using it, so there would be upskilling involved.

Azure Data lakes

Microsoft are investing in Data lakes, making it easy to export to and promoting it. Azure Data Lakes are the direction of travel and I have the feeling Microsoft are going to invest and make this attractive for people to use.

Exporting Common Data Service data to Azure Data Lake

Azure Data lakes are made for large datasets and as we capture more data, it’s likely we will need to upgrade the tools we use.

I haven’t used a Data Lake yet but I believe this will change going forwards, so it’s something to watch and investigate if you have time.

20 things you know only if you have worked on an IT project

Plans are worthless. Planning is essential~ Dwight D. Eisenhower

  1.  The road to a late project is paved with extra requirements
  2. Trying to cleanse data in a legacy system is as easy as staring at the sun
  3. ERD’s are always wrong but no one wants to fix it
  4. 5 minutes after creating a project plan it’s out of date
  5. The sales team always over sold the solution
  6. Data migration takes at least 4 times as estimated
  7. I have seen developers 99% finished on development and the last 1% take as long as the first 99% 🙂
  8. There are many projects Agile in name but chaos in reality
  9. Adding more people to a project can make it go slower
  10. Code is created by trial, error, error, error, error, error, error, success, error, success
  11. All bugs are blamed on developers who have left the project
  12. Projects are a team sport, played by individuals
  13. 50 percent of problems on IT projects are people, the other 80 percent are estimates and expectations
  14. Assumptions cause bugs, incorrect requirements and wrong estimates
  15. Estimates are considered commitments by management, best guesses by developers
  16. Nothing integrates out of the box
  17. Every project is different. What worked on previous projects might not work on current project.
  18. The solution you end with is never the solution designed at the start
  19. Customer don’t know what they want until you show them what you done, then they know it’s not that
  20. When trust is gone, project failure won’t be long

Articles you might like