Power BI, Dataflows, CDM, and Dynamics 365 – My Journey

PBIDataFlowsAndCDMSeries
Journey into Dataflows, Dynamics, and CDM

My company, Pragmatic Works, is working through a Dynamics 365 migration. As part of that migration, we need reports. We have made the decision, seemingly obvious given who we are, to use Power BI for all of our internal reporting. Through the process, I have been working with our migration team a lot and have been tasked to handle key reporting for the consulting team. We are implementing both CRM and PSA (Project Service Automation) as part of the rollout. I am responsible for reporting that supports the non-Sales operations for the consulting organization. This series of posts will follow my journey to get a good solution in place. I will give you the resources I used, the advice from the pros on my team, and anything else I can share to help your journey along as well.

I want to caveat that this is my journey through the process. I am sure some mistakes will be made along the way, but we should all learn together. I am not doing this in a vacuum. I have some very talented Power BI team members helping me with strategy and design. Their contributions will be reflected throughout the process, but want to give them credit now for sure.

Evaluating Power BI Dataflows vs Power BI Shared Datasets

I started the process by trying to determine what is the best option for building data models that can be used for ongoing report creation within our company. I was familiar with shared datasets and with the latest improvements in the service, it was a good place to start. However, I have been talking with the team about Power BI Dataflows (and Azure Data Factory Dataflows, but that is not relevant here). I put it out to the group above to discuss pros and cons. Overall, the team pointed out that Dataflows with Dynamics would be the best fit, however, there is not much out there on Dataflows in action.

Brian Knight and I were having a different conversation about the Common Data Model (CDM) and PowerApps for other projects. During that conversation, Brian also mentioned that the CDM was ideal for working with Dynamics data.

Coming full circle, the team agreed that CDM with Dynamics and Dataflows is a good way to go. And so starts the journey. I will be documenting the research and progress along the way here. I will be posting whenever I can. Here we go!

Power Platform Conference – 7/23-24/2019

Pragmatic Works is hosting a 2-day Power Platform online conference. If you are unable to get out to onsite conference but need some help with Microsoft Flow, PowerApps, or Power BI, this is a great opportunity to learn from some experts from around the world.power-platform-virtual-conference-v04_facebook Information About the Conference

This is a 2-day virtual conference covering Power BI, PowerApps and Flow. The cost is $49 for all sessions, recordings and bonus materials. There will be 12 sessions each day.

 Dates: July 23 & 24, 2019

Times: 7 a.m. to 7 p.m. ET

Website: https://powerplatformconference.com/

I hope to see many of you there virtually!

It’s Been a While …

Last Post – SQL Saturday MN 2018

When last I wrote, it was after I spoke at SQL Saturday in Minnesota. I co-presented a pre-con with Josh Owen. Prior to that was a few weeks of blogs on Azure and Azure Data Week. As Pragmatic Works moved to an Azure focused company, I, too, was learning much about Azure and what it takes to move workloads to the cloud.sqlsat796_header                                                  Post Linkden

The New Role – Director of Consulting

In October 2018, I was promoted to Director of Consulting at Pragmatic Works. Over the past 6 months or so, I have had the chance to lead and grow the consulting team at Pragmatic Works. This has been a great growth opportunity for me and my career. I am not perfect by any stretch, but the team is awesome! Pragmatic Works Consulting continues to grow and do awesome work. email-signature

The Beat Goes On …

As I look forward to the next few months, there are some great things happening at home. I get to see my youngest daughter graduate from high school, my second child and oldest son will be graduating from Liberty University next month, and then he will be getting married to his college sweetheart. It is will be a busy summer and into the fall at my house. But it will be great as well. Love watching the family grow up and move us all to the next stage all of life.

As Pragmatic Works continues to grow this year, I look forward to seeing new team members join our team and existing team members expand skills and capabilities. In the last two years, Pragmatic Works has transformed itself from a SQL Server based solution company focused on Microsoft Business Intelligence to an Azure solutions company specializing in bring customers to Azure and Azure Data Services. It is an exciting time in technology today and we are a part of that excitement.

Here’s to a great year!

 

SQL Saturday #796 – Minnesota, 2018

sqlsat796_header

First, many thanks to the SQL Saturday and MNPass team for putting on another great event and letting me participate.

I co-presented with Joshuha Owen (@JoshSQL) in a precon on Modern Enterprise Data Warehousing on Azure. Thanks to those who attended and participated in the conversations around changing the way we implement data warehouse capabilities in Azure. Josh and I will be talking more about this in the future.

Now, those of you who attended my Saturday presentation on Consumption Based Architecture, I wanted to get you the slide deck and reference materials here. Thanks again for attending.

The slide deck from the session is here.

cba-featured-pic

I also have blog post around this topic: Consumption Based Architecture for Modern Data Analytics. Feel free to join the conversation there around this.

ERPs and the Consumption Based Architecture Conversation

One of the key topics that came up during the session was related to handling ERPs with minimal change. The key issue surrounding ERP solutions is with the data structure in those systems. Whether you work with SAP, JackHenry, or Dynamics, you have a situation where the data model is very complex and definitely not user friendly. In Consumption Based Architecture, we try to minimize data transformation and reshaping, but ERP solutions are by nature cryptic and complex. By definition, they are not consumable. So in the consumable space, we typically recommend using the vendor supplied solutions such as JHKnow, SAP BW and so on. These solutions provide a vendor managed interpretation of the data in the ERP for reporting and other solutions.

Security in this Architecture

The question was raised during the session around how to secure this. This does not have a simple answer. Each solution may have implemented security differently. For instance, an Oracle database may use user names and not have AD integration. This means that you need to determine how to secure your consumable space. For instance, if you pick Azure Active Directory, you would move data to AAD compliant structures in Azure such as Azure SQL Database, Azure SQL Datawarehouse, and Azure Databricks. This means you might need to use a tool like Goldengate with CDC to update a SQL DB which you can apply security to. This will allow you to centralize security for your consumable data. You will need to plan for security in whatever you do and create what you need to support it.

Thanks again everyone for joining us at SQL Saturday.

 

 

Walmart Chose Azure, How About You?

Another major corporation has announced their decision to move to Azure. Perhaps you’ve heard of them? Walmart—yes, Walmart and Microsoft have announced an ongoing cloud partnership this year. While AWS would have been a competitor in this case, because Amazon put themselves in that position. But that’s not the real reason Walmart chose Azure. In this post, I’ll go over the key reasons why they went with Microsoft Azure. Clay Johnson, Walmart CIO and Executive VP of Global Business Services stated that Walmart is excited to accelerate their digital transformation using Microsoft and the Azure cloud.

In a joint press release, Microsoft and Walmart CEOs called out 3 key reasons why Walmart chose to use Azure and Microsoft Office 365:

Digital Transformation

We hear about the great concept of digital transformation all the time and we’ll see it in action with Walmart and Microsoft. They’ll work together to move 100s of applications to cloud based solutions. Not only will they do lift and shift, but in many cases, they’ll be looking for optimization opportunities to transform their business.

Looking at Azure or want to do more with it, join us next week for Azure Data Week!

Innovation

Walmart plans to build a global IOT solution on Azure and the advanced technologies to improve their performance in the world of energy conservation and to drive efficiencies in supply chain with machine learning to become a better company.

Collaboration and Agility

Using Microsoft 365 and a variety of Azure tools, Walmart is investing in their people to move to a more collaborative and productive workforce.

So, as you can see, Walmart put in a lot of thought and consideration before they made their choice to go with Azure. Look at these 3 key areas and at your business – can Azure help you improve your business in such a dramatic way by moving to the Azure cloud?

Making the move is not a technology decision, it’s a business one so you can do better business.

Thoughts on data, business analytics, and the SQL Server community

%d bloggers like this: