Solution Archives - Business Intelligence Info

Tag Archives: Solution

Complete Field Service Solution with Auto Scheduling, Optimized Routes, Check In-Out, Territory Management, Radius search, et al, within Dynamics 365 CRM / PowerApps!

November 25, 2020 CRM News and Info

At the heart of any company’s success are its customers. The success and growth curve of any company can be predicted by gauging its customer satisfaction levels and market reputation. One vital component that has a significant effect on these attributes is the quality of customer field service. Are they quick? Are they thorough? Are they reliable? Are they worth the cost? These are just some of the questions that an organization has to prove for every customer they have.

With Dynamics 365 CRM, Microsoft has allowed organizations to leverage data and understand where their customers are and when is the ideal time for them to show up. With the emphasis on a customer-first approach, ‘ask and you shall be given’, has become the norm in customer engagement and support. Booking for services like consultations, deliveries, repairs, etc. at a customer’s fingertips has made convenience a necessity rather than a luxury.

This makes on-field customer servicing an essential business process for many organizations. Companies turn to technology for helping their managers in allocating, routing, and managing their field service agents as well as optimizing and maximizing the overall efficiency of their end-to-end field service process.

This is where Maplytics comes in! Maplytics Field Service Solution handles all your field service requirements as an end-to-end Field Servicing Solution Our efforts in creating this solution have even been rewarded with the community’s constant appreciation and Microsoft’s Preferred Solution badge on Microsoft AppSource.

The best way to grip the power of Maplytics end-to-end Field Service Solution is to see it in action! So let’s explore this with a use case. Consider Josh is the manager at a fast-growing carpentry company ‘BuildsThat Last.’ His company is getting recognition for its quick carpentry services and quality customer support.

Josh has a group of total 7 field service agents under his management and today he is managing one of the highest sales regions, Staten Island, New York. On average, they get a minimum of 10-15 customers daily from this region and his responsibility is to serve all of them. Therefore he has two of his best field agents, William and Joe, prepped to be deployed.

Mindful of the volume of requests, Joshis committed to do an exceptionally good job. He is handy with Maplytics so he knows he will have no trouble allocating route-optimized schedules that will save his field agent’s time and transportation costs. Josh uses the Auto Scheduling feature of Maplytics to do this. First, he plots all Active Cases on the map as shown below and then uses the Auto Scheduling option in Mass Actions to open the Auto Scheduling card.

Now Josh selects Joe and William as the users for whom the schedule is to be generated. To keep the integrity of previously created schedules intact, Josh thinks to select the checkbox of Consider Existing Meetings. This option would take the existing meeting of his field agents into consideration while generating the schedule. He can also choose whether to add these existing meetings to their current schedule or reschedule them. But when checked, he finds his field agents don’t have any existing meetings on the current day, so he decides to leave it unchecked.

He then uses the Advanced Preferences option to set a maximum of 10 meetings in a day where each meeting lasts 30 minutes or less. If there was a situation where Josh experienced a spike in requests, he could easily create a multi-day schedule to resolve all requests in a span of multiple days. Once all the preferences are set, he clicks Proceed to create the Auto Schedule.

Josh now has an optimized schedule for his field reps that was created within minutes using Auto Scheduling! The color-coded nature of the routes makes it simple to clearly distinguish between Joe’s and William’s routes.

Josh then creates an activity for this schedule. He chooses to create an appointment and fills in all the required details as shown below. Once filled, he creates this activity for William and Joe.

Now let’s transition to the other end of the solution. Once the activity is created by the manager, the field agents can then go to the field and start using the Maplytics app on their mobiles or tablets. They can start navigation on their assigned routes using either Google Maps or Waze App and view turn-by-turn navigation while following the route on the field.

Let’s shift to the perspective of the on-field agent William and explore his experience with Maplytics. William has a schedule assigned to him for the day and he’s ready to get started. Firstly, he opens the schedule assigned to him on the map to understand his day as shown below. Once plotted, he clicks on the Navigate button to start his schedule.

As William clicks on the Navigate button, he gets redirected to Google maps where he can easily view the route for turn-by-turn directions. This happens because Williams has set the ‘Navigation Within’ option to Google App in the user configuration detail record.

Following directions is a piece of cake now with the easy-to-follow directions on the map. With the optimized route and the turn-by-turn navigation, William reaches the destination in the least time possible. When he reaches the service location, he uses the Check-In button to register his arrival to the service site as shown below.

William’s appointment is for the inspection and restoration of an antique table. He diligently begins to inspect the table but after 10 minutes she concludes that the table is beyond the scope of restoration and deems it as a lost cause. He uses the Check-Out button to register that he is done with the appointment. Now since his appointment is cut short he’s left with free time. William sees this as an opportunity and quickly decides to schedule another meeting with a nearby customer.

To do this, he opens Maplytics on his phone and clicks the Locate Me button on the Detail Map to plot his current GPS location. Next, he opens his Plot Records card and enters a 2 and 3-mile radii as Search Radius since his next location is more than 3 miles away. He then selects Leads as the data source, My Open Leads as the View and then hits the Search button.

Now William has all the leads plotted on the map which lie within 2 to 3 miles from his current location as shown below.

He uses the tooltip cards of the respective pushpins and looks at the Line-of-Sight Distance as shown below to understand which location is the nearest and most suitable to him from his current location. He sees that the closest customer is only .40 miles and it is also aligned with the route to his next customer! William proceeds to contact the customer to seek confirmation for a preponed service. Once he receives a confirmation, he makes his way to the customer’s location by right-clicking on their pushpin and adding it to the route. He uses Check In to register his appointment, performs the service, Checks Out, and moves on to continue with his remaining schedule.

While the field agents are continuously logging their progress, Josh can monitor how many requests have been met and also track the agents who are fulfilling the maximum numbers of requests by plotting the Check-Ins and Check-Outs on the map. This gives him an overview of the day’s performance by the entire team and individuals.

The above use case illustrates just a glimpse into the everyday life made simple for the managers and field agents in the organization using Maplytics’ end-to-end field service solution. While this solution simplifies organizing and managing field agents for managers, it optimizes convenience and maximizes productivity for the field agents.

Maplyticsis a 360-degree geo-analytical solution that revolves around customer satisfaction and therefore, you can find a huge array of other productivity-enhancing features that allow you to auto create territories, manage territories, perform along the route search, set area of service, and much more within the Maplytics features set!

So if customers are your priority and you would like to introduce the next level of intelligence for your field service, you need to get hands-on experience of Maplytics today with our free 15-day trial. You can download Maplyticsfrom our Website or Microsoft AppSource (don’t forget to check out our shiny official badge of Preferred Solution there!)

Need someone to walk and talk you through this end-to-end solution? We’ve got our support team waiting for you just an email away. You can draft now and click crm@inogic.com to get started with your free and personalized Maplytics demo!

Until the next time –we’ll be around on your map!

Let’s block ads! (Why?)

CRM Software Blog | Dynamics 365

Quil launches in-home health monitoring solution as telemedicine usage explodes

October 12, 2020 Big Data

Quil Health, the health care joint venture of Comcast and Independence Health Group, today announced plans to launch Quil Assure, a platform that leverages sensors, voice-activated tech, and coordination tools to connect caregivers with loved ones. Quil says over time Assure will integrate with its existing product, Engage, to help customers manage their health and wellness needs.

In response to the novel coronavirus pandemic, companies like Current Health and Twistle have teamed up with the Providence and other health care providers to pilot at-home health-tracking platforms. Even before the pandemic, nine in 10 seniors said they’d prefer to stay in their homes over the next 10 years, highlighting the need for remote health monitoring solutions.

ABI Research predicts that by 2025 spending on AI in health care and pharmaceuticals will have increased by $ 1.5 billion as a result of COVID-19. Meanwhile, the personal emergency response systems market is anticipated to pass $ 11.1 billion within that same time frame.

Quil says Assure will “get to know” customers over time using activity-monitoring sensors (e.g., motion, temperature, and air quality sensors) that send real-time alerts and notifications. Assure’s voice capabilities will enable caregivers to reach out through in-home speakers and check in on loved ones, and the platform will allow clinicians to work together to support patients and better manage transitional care.

Quil plans to launch Assure by Q3 2021, following a series of pilots this December.

“Consumer health platforms fall short of delivering on the promise of engagement, wearable monitoring technologies are limiting, and caregivers are often left in the dark,” Quil CEO Carina Edwards said in a statement. “Quil is rethinking the whole aging at home experience with an integrated platform that fits into our daily lives. Personalized education and step-by-step guidance to engage consumers and caregivers combined with in-home smart sensors to monitor health in real time are just the beginning.”

Launched in 2018 as a partnership between Independence Blue Cross and Comcast, Quil provides health itineraries via an app that can be prescribed by doctors or offered through insurance carriers. The company also provides educational content, including documents, surveys, and videos covering pre- and post-op care and at-home physical therapies.

Comcast subscribers can say the phrase “Quil Health” into their television remotes to access Quil’s on-demand content. In addition, the cable giant offers a home security system that includes sensors and AI capable of detecting customers’ movements for health monitoring.

Quil Health recently launched the COVID-19 Preparedness Tool, which provides up-to-the-minute information on the novel coronavirus, including a symptoms checklist, recommendations from the U.S. Centers for Disease Control and Prevention, and tips on how to care for a family member with COVID-19. The tool focuses on overall wellness, providing instructions on self-care, stress reduction, and in-home exercise techniques, as well as finding a work/life balance while working from home, best practices for child care, and guides to homeschooling.

Let’s block ads! (Why?)

Big Data – VentureBeat

Inogic Dynamics 365 CRM apps get the Preferred Solution badge on Microsoft AppSource!

September 11, 2020 CRM News and Info

We have great news! All your favorite Inogic Dynamics 365 CRM/PowerApps apps now proudly exhibit the badge of Preferred Solution on Microsoft AppSource! This is significant because of what it takes to be certified by Microsoft. For an app to get a Preferred Solution badge, it has to undergo a well-defined and scrutinizing quality control test. A panel of Microsoft experts use their refined quality process to validate each app based on their quality, performance and level of customer satisfaction.

Today, we are happy to announce all Inogic apps proved their competency and now parade the favorable results of this test with its badge of honor. This wouldn’t have been possible without the constant appreciation, support and feedback from our users as well as Partners, we want to take a moment to Thank You all for this.

If you aren’t well aware of the advantages of all the Inogic apps yet, go ahead and download a free trial from AppSource, and let us give you an overview of what you can expect from the Inogic Preferred Apps for Dynamics 365 CRM and Power Apps.

Maplytics™ seamlessly integrates Bing Maps and Dynamics 365 CRM/ Power Apps to provide geo-analytics with features like Territory Management, Radius Search, Appointment Planner, Optimized Routing, PCF Controls, Census Data, Heat Maps, Truck Routing, etc. Maplytics helps businesses globally to plot, plan, visualize and analyze Dynamics CRM data on the map and offers powerful mapping services. It is also certified for Microsoft Dynamics 365 (CfMD).

InoLink provides integration between Dynamics 365 CRM and Intuit QuickBooks. It is a cloud-based productivity app that synchronizes transaction history and aging details from QuickBooks to Dynamics 365 CRM to provide a 360-degree view of customer information. Furthermore, tax calculations for transactions can be done within Dynamics 365 CRM to send Quotes/Orders/Invoices from Dynamics 365 to QuickBooks.

User Adoption Monitor is a monitoring and tracking app that monitors and tracks the actions performed by users in Dynamics 365 CRM. UAM monitors both the common actions and special messages of end users such as Create, Read, QualifyLead, Win, etc. periodically, like daily, weekly, or monthly basis. Using UAM dashboards managers can get the brief details of user actions per Entity/Action/Period, thereby improving the user adoption of Dynamics 365 CRM.

Click2Clone enables users to clone/copy Dynamics 365 CRM records from any OOB as well as custom entities of Dynamics 365 CRM to another record. It also enables users to easily create multiple copies and clone/copy related child records of any given Dynamics 365 CRM record. The repetitive data entry is considerably reduced by automating and simplifying the process of creating new records, this way Click2Clone becomes a strategic enabler for business control and agility.

Click2Export provides an easy user experience of exporting any of the Dynamics CRM Reports/Word/Excel templates and CRM Views data in just one click. With Click2Export, users can export the data in 5 different file formats – Excel, Word, PDF, CSV or TIFF. They can also schedule and export reports/word/excel and CRM Views data at regular intervals as well as attach as Note or upload to SharePoint all with 1 click.

Click2Undo enables users to undo changes and restore records to its previous known state with just a single click. Be it the last change made to the records or changes that were done in the past, be it a single record or multiple records; Click2Undo can do it without any hassle. In a way, Click2Undo is your own ‘ctrl+z’ button for Dynamics 365 CRM and Power Apps.

Alerts4Dynamics creates, schedules, and manages alerts and notifications for different entities in Dynamics 365 CRM. The alerts in Alerts4Dynamics can be viewed as pop-ups, form notifications or sent through emails. It is a productivity-enhancing app where users can define alerts for an individual record or set of records based on rules which can be displayed as notifications indefinitely or for a certain period. Users can access this notification from anywhere in the Dynamics 365 CRM.

Attach2Dynamics provides native integration between Dynamics 365 CRM and cloud storage like SharePoint, Dropbox and Azure Blob Storage. Users can use features like drag and drop, browse, and choose multiple files or a folder at a single instance to upload to the cloud storage of choice. It has a user-friendly UI for viewing all the files & folders in the configured cloud storage against the current record. Users can further rename, create, email, delete, preview files/folders and generate a shareable link of the file or the folder to provide it in the email directly from within Dynamics 365 CRM.

SharePoint Security Sync is a comprehensive solution for integrating Microsoft Dynamics 365 CRM / PowerApps with SharePoint for document management. SharePoint Security Sync enhances user experience and makes the integration experience seamless between the two systems by allowing for associating document folders against CRM records as well as having the security privileges synced from Dynamics 365 CRM to SharePoint. This ensures secure and reliable access to confidential documents stored in SharePoint.

Kanban Board uses the Card-based Kanban view to visualize your Dynamics 365 and Power Apps data. With the ability to categorize the data into lanes, it helps with a quick review of the records in a card-based view and easily identifies the status of the records. Users have the option to use a visual tool to move the records across lanes and rows to quickly update the underlying field values. Apart from this it also allows for quick action shortcuts to record activities against these without too many clicks.

Relationships can be mapped using Mind Map within Dynamics 365 CRM data on a form. Users can visualize relationships between Entities or related records in a single view. Map My Relationships provides easy navigation to related records and quickly logs various activities like phone calls, emails or appointments for the related records. Users can thus get information at a glance, leading to quick decisions and swift execution of tasks.

Round Robin Routing & Assign distributes work items (or any other entity records) recorded in Dynamics 365 CRM and systematically allocates or distributes them. Round Robin ensures a fair distribution of workload within each team by honoring their individual capacity. Managers can therefore allocate and distribute workloads in an organized way. With automated distribution and assignment, Round Robin Routing & Assign helps to improve the efficiency of Dynamics 365 CRM users resulting in higher sales and profit.

Auto Tax Calculator automates tax calculation directly inside Microsoft Dynamics 365 for Sales. Users can finally eliminate manual calculations, increase accuracy, save time for the entire team and get taxes applied correctly the first time when tax season rolls around. Users can also easily manage Tax rates within Dynamics 365 CRM. The tax rates can be either manually entered or be integrated with AvaTax from Avalara.

There’s more! Our recently acquired two new Billing Products – Recurring Billing Manager & Subscription Management – will be shortly made available on Microsoft AppSource and within your quick reach.

Unlock the full potential of your Dynamics 365 data with these productivity-enhancing apps, only a few clicks away!

You can even get a personalized demo by dropping a mail to us at crm@inogic.com.

Until the next good news – Stay Safe, Stay Healthy, and Buy Preferred!

Let’s block ads! (Why?)

CRM Software Blog | Dynamics 365

Dynamics 365 Daily Solution Backup

August 1, 2020 Microsoft Dynamics CRM

Credits

Kevin Neely

Phil Rittenhouse

Our goal was to build a system which would provide daily snapshots of a Dynamics 365 environment in daily flux as modifications were made continually by a development team. Such a system would guard against catastrophic tenant events, have the added benefit of speeding deployment to new tenants and streamlines the steps to move between environments (dev, test, prod).

For such a solution to be effective, the following conditions or their analogues should exist:

A git repository exists for the project, where resides the source code for any customizations (plugins, workflow actions, etc.).
The development team is storing their configuration changes (new/changed entities, workflow processes, etc.) in either their own solutions or in a central working solution, and not the Dynamics 365 Default solution.
A Virtual Machine, local machine, or some other host environment exists to run the PowerShell script. (If this is not the case, alternate approaches may be used).

Approach 1: PowerShell Script

The first approach centered on a PowerShell script and might best be applied in on-premise or IAAS scenarios.

The basis of this solution is the PowerShell script provided in this document. In our case, it was configured to run at a certain time each day on an Azure Virtual Machine that was already running 24/7. A Windows Scheduler job was created on the Virtual Machine using saved credentials that had appropriate permissions to both the D365 Organization and the git repository in the teams’ Azure DevOps instance.

Daily backups generated by this script were saved in the git repository under a folder structure according to Month and date.

######################################################################
#                                                                    #
# Script to backup daily git repo changes and D365 solution packages #
# Prepared by:                                                       #
#    pHil Rittenhouse (philirit)                                     #
#    and                                                             #
#    Kevin Neely (keneely)                                           #
#                                                                    #
######################################################################

# Allow and install the required modules
Set-ExecutionPolicy RemoteSigned -Scope CurrentUser
Import-Module Microsoft.Xrm.Data.Powershell

# Get today's date for use as the folder name
$  date = Get-Date -Format MM-dd-yyyy
$  month = Get-Date -Format MMMM

# Create the folder name from the date variable
$  path = "C:\<path>$  month$  date"
$  gitpath = "C:\<path>\"

# If today's folder already exists, delete it
If(Test-path $  path) {Remove-item $  path -Recurse}

# Create today's folder
New-Item -ItemType directory –Path $  path 

# Change working directory to new folder
Set-Location -Path $  gitpath

# Pull from github
git pull

# Get the environment details
$  CrmConTimeOutMinutes = 10
$  CRMServerURL = "https://<org>.crm.dynamics.com"

# Build a connection string
$  cs = "RequireNewInstance=True"
$  cs+= ";Url=$  CRMServerURL"
$  cs+= ";AuthType=Office365"
$  cs+= ";SkipDiscovery=True"

# REPLACE WITH YOUR USERNAME/PASSWORD
$  cs+= ";Username=<user>@<org>.onmicrosoft.com"
$  cs+= ";Password=<password>"

# Create a connection using ConnectionString (for Online)
$  Conn = Get-CrmConnection -ConnectionString $  cs -MaxCrmConnectionTimeOutMinutes $  CrmConTimeOutMinutes

# OR # Create a connection using ConnectionString (for On-Premise)
#$  Conn = Get-CrmConnection -ConnectionString "AuthType=AD;Url=https://myserver/Contoso;Domain=contosodom;UserName=user1;Password=password" $  cs -MaxCrmConnectionTimeOutMinutes $  CrmConTimeOutMinutes

# Publish ALL updated customizations:
Publish-CrmAllCustomization -conn $  Conn -Verbose

# Export each of the solutions - APPEND TO THIS LIST IF ADDITIONAL SOLUTIONS ARE CREATED
Export-CrmSolution -SolutionName _pHil -SolutionFilePath $  path -SolutionZipFileName _pHil.zip
Export-CrmSolution -SolutionName _Kevin -SolutionFilePath $  path -SolutionZipFileName _Kevin.zip

# Add changes
git add .

# Commit changes
git commit -m "Backup for $  date"

# Push changes
git push

# To enable mobile push notifications signup at notify17.net
# Notify upon completion
$  notifyParams = @{title="Solution Bakup Completion";content="Solutions have been backed up for $  date"}
Invoke-WebRequest -Uri https://hook.notify17.net/api/raw/<api> -Method POST -Body $  notifyParams

# DEPRECATED - use if want to collect all output into a single zip file
# Get the name/path for the zip archive
#$  archive = "C:\<path>\Solutions_$  date.zip"
# If the archive file already exists, delete it
# If(Test-path $  archive) {Remove-item $  archive}
# Add-Type -assembly "system.io.compression.filesystem"

Approach 2: Azure DevOps Pipeline

The PowerShell/on-prem/IAAS solution was effective and met the need, but we saw the advantage in leveraging newer technologies and rebuilt the solution as an Azure DevOps Pipeline.

Prerequisites

The following components or configurations are required for this approach:

A Dynamics Organization.
- Appropriate access to publish customizations and export solution packages will be required.
- As in the other approach, developers should be using named/specified solutions in Dynamics rather than customizing the Default Solution directly.
Azure DevOps Organization, with the following configured:
- A Repository for the project. A Git repository was used in our case. If Team Foundation Version Control is configured for your project, changes will need to be made in the scripts and configuration elements of the Azure Pipeline. These changes are out of scope of this version of the solution documentation.
- Admin rights to the ADO Organization in order to build and configure the Pipeline.
- PowerApps Build Tools will be used as a key component in the Pipeline.

Building and Configuring the ADO Pipeline

Identify which solutions are to be backed up from Dynamics.
In Azure DevOps, select Pipelines from the left navigation:
Create a new Pipeline.
Select “Use the classic editor” on the first screen:
Select the appropriate options here and click Continue:
Click Start with an Empty Job:
Save & Queue the Pipeline. Make sure the Repo exists or the Job will fail.
Next, we must ensure the identity running the Pipeline has appropriate permissions. Click the Settings icon and then Repositories:
Select the Repository to be backed up, then the User for the Build Service, and change the Contribute permission to “Allow”:
Return to the Pipeline and enter Edit mode to start creating the steps.
The first two tasks needed are PowerApps Tool Installer and PowerApps Publish Customizations:
To configure these steps, we must first configure the environment connection. Click “Manage” for the PowerApps Environment URL:
Click on “Create Service Connection”.
Select “Generic” and click “Next”.
Configure the connection with your endpoint URL and credentials. Click Save when complete.
Return to the Pipeline build and select the newly created URL:
Next, we will add the script to set up the dated folders. Click Add Task > Utility > Command Line:

The script for this first Command Line step is as follows:

for /f "tokens=1-4 delims=/ " %%i in ("%date%") do ( 
   set dow=%%i 
   set month=%%j 
   set day=%%k 
   set year=%%l 
) 
@echo ##vso[task.setvariable variable=year]%year% 
@echo ##vso[task.setvariable variable=month]%month% 
@echo ##vso[task.setvariable variable=day]%day%

Create three variables for the Pipeline: “year”, “month”, and “day”:
Add and configure a PowerApps Export Solution task:

Add another Command Line Script task to handle the Git commit, using the following script:

echo commit all changes 
git config user.email user@domain.com 
git config user.name "Automatic Build" 
git checkout master 
cd Backup 
mkdir $  (year)$  (month)$  (day) 
cd $  (year)$  (month)$  (day) 
git add –all 
git commit -m "solution init" 
echo push code to new repo 
git -c http.extraheader="AUTHORIZATION: bearer $  (System.AccessToken)" push origin master

The last step is to ensure the Job can use your OAuth token. Click on the job name, and scroll down to enable the OAuth checkbox:
Save & Queue, then Run the job.
Once the Job has completed, go back to your repo and verify that the backup folder and file(s) have been created:

Let’s block ads! (Why?)

Dynamics 365 Customer Engagement in the Field

A Powerful Problem Loan Management Solution for Lenders in the Digital Age

July 2, 2020 CRM News and Info

Everyone in the banking industry is following the news, you see the statistics and the predictions. There is no doubt that there are going to be problems in your loan portfolio. Smart lenders should be asking:

Have my policies and procedures kept pace with the changes of the last decade, and am I prepared to execute them in a digital space?
Is my staff assembled, aligned, and trained to work out problem loans according to best practices and the chosen strategy of the bank?
Are we equipped with technology to enable efficient problem loan management at a high quality?
I know problem loans are coming, what should I be doing right now?

To help answer these questions, Crowe has developed a Problem Loan Management Framework.

In our recorded presentation “Identify and Address Problem Loans With Technology and Foresight” we discuss these three different areas in detail:

Identification
- Problem Loan Management
- Covenant Testing
- Annual Review
- Financial Statement Receipt and Review
- Delinquencies Review
Assessment and Monitoring
- Loan Documentation Review
- Site Visits
- Financial Condition Assessment
- Borrower Conversation
- Security Perfection Assessment
- Collateral Valuation
- Pre-workout Agreement
Strategy
- Bankruptcy
- Foreclosure/Liquidation
- Restructure/Return to Lending Group
- Exit (Borrower driven or loan sale)

Plus, we look at the kind of people, policies, procedures, and technology that can help.

To address the issues outlined in our Problem Loan Management Framework, we have developed the Crowe Problem Loan Management for Microsoft Dynamics 365. This solution makes it possible to stay on top of, and even be proactive with, distressed borrowers in good times and bad, including:

Deferral tracking
Watched asset reporting
Reserve calculator
Relationship monitoring
Action item tracking
Touchpoint reminders
Problem loan onboarding

Take action now to prepare for a wave of problem loans.

With the right guidance and technology, you can take care of your borrowers, your organization, and your books. Crowe services and solutions can help you find and address your credit portfolio risk.

Request more information and assistance for managing problem loans

By Ryan Plourde, Crowe, a Microsoft Dynamics 365 Gold Partner

www.CroweCRM.com

Let’s block ads! (Why?)

CRM Software Blog | Dynamics 365

2 Ways to Streamline Your Microsoft Dynamics 365/CRM Solution

June 11, 2020 Microsoft Dynamics CRM

Microsoft Dynamics 365 is a complete ERP (Enterprise Resource Planning) /CRM (Customer Relationship Management) system with many cutting-edge features and an abundance of useful tools. However, some of those valuable tools may have been designed for businesses very different from yours.

Do you feel that your Microsoft Dynamics 365 CRM is overly complicated? Are there features and tools that you never use? Having more technology than they need can be frustrating for users.

Option 1: Consider a more personalized system

One option for smaller, less complicated businesses is to switch to P2xRM, an affordable custom-built CRM solution. P2 can even work with you to transfer your Dynamics 365 licensing costs so you can stick to your budget. We ask questions, we listen, we learn about your requirements, and then we will build a system that automates your key business processes. We won’t complicate your system with bells and whistles you’ll never use. We’re all about giving you just what you need – nothing more, nothing less.

Another option is to stay with your Microsoft Dynamics 365/CRM solution, but adjust it to accommodate the specific needs of your business.

Option 2: Consider a Microsoft Dynamics 365 makeover

There are reasons you may choose to stick with your Microsoft Dynamics 365 solution. You’ve invested in the system. Perhaps it is mandated “from above,” or your company has an “all Microsoft” policy. Or maybe changing your CRM system, after a long and often painful evaluation, implementation, and extensive training, would not even be considered by the rest of your team. In that case, a makeover would be a viable solution.

Before we change anything, we’d want to review your business processes and the way you’re using your Dynamics system. Sometimes less is more, and you may find that you prefer to bypass unused tools and work with a streamlined version that does just what you want it to do.

For instance, if you are not in a business that gives quotes, you don’t need a quoting bar. If you don’t do follow-up calls, you won’t need that bar either. A customized “quick links” pane can provide access to what’s most important to each individual or team.

You might wonder if you’d lose something important if you remove features or tools, or you may reason, “Just leave it in there. I paid for it. I might as well keep it.” But in fact, unnecessary gear just serves to confuse everybody. It makes training and user adoption more difficult and increases the chance of user error.

Exactly what you need to run your business. Nothing more, nothing less. Doesn’t that sound reasonable?

You know your business. We know CRM. Together we can determine precisely what you need and don’t need in your CRM system. Our goal at P2 Automation is to make CRM simple. Even a robust system such as Microsoft Dynamics 365 can be more user-friendly and efficient.

If you would like to review your current Microsoft Dynamics 365 CRM system to determine ways it can work better for your organization, contact our experts at P2 Automation today to schedule a strategy session.

By P2 Automation, p2automation.com

Let’s block ads! (Why?)

CRM Software Blog | Dynamics 365

Turn D365 into a Customer Relationship MAXIMIZING Solution

June 2, 2020 CRM News and Info

What do we really want out of customer relationships? Yes, we all want to ensure customer satisfaction. Yes, we all want to keep customers engaged with our brand and products.

But let’s cut the malarkey: we mainly all want revenue. (And satisfied, engaged customers are the revenue drivers.)

Today, we’ll share a few tips on tweaking the “M” in CRM from management to maximizing. And it starts at the very first touch point.

“We have received your inquiry and will get back to you shortly.” Talk about a missed opportunity.

D365 (Sales, Marketing, Customer Service, Field Service, et al) gives you every means imaginable to fire off an email to people who have downloaded a whitepaper, signed up for a webinar, filled out a contact form, or otherwise interacted with you. And there’s no better time to get an open for an email, so why do so many businesses waste what’s basically a guaranteed open with a robotic reply?

Make sure every autoresponder you create in D365 is relationship maximizer: did they download a whitepaper on economic trends? Send them a link to a webinar on economic trends. Did they contact you for a demo? Send them a link to a one-pager or other “demo-primer.”

Don’t just thank them for reaching out to you. Build on why they reached out: give them more than they expected, and you’ll eventually get more in return.

Have you integrated your Dynamics 365 CRM with your ERP system, connecting marketing info (contacts, geo’s, etc.) with critical financial info? Have you connected LinkedIn Sales Navigator to provide “career context” for every customer communication? Have you created compelling customer journeys in D365 Marketing that feed data to customer service and sales reps in real-time?

Microsoft has a greater depth and breadth of tools and platforms than ever before, and they’re almost all centered around empowering a business with a completely automated, instantly available 360-degree view of every customer. If you’re using only D365 CRM to understand your customers’ needs, wants, and behaviors, you’re getting only a sliver of the big picture.

Yes, technically, you’re “managing” those customer relationships. But once you roll in the data of, for example, each customer’s website and social media behavior, career details, and other demographics data the Microsoft stack can provide, you move from customer relationship management to customer relationship maximization.

As stated, in the end (and usually the beginning and middle, too) customer relationships are all about maximizing revenue possibilities. Which means your sales quotes are one of the most critical communications you have with a customer.

Yes, a sales proposal is, in many ways, just another customer communication… just like a contract is just another communication. I.e., it’s important.

Not only that, but in many cases, your sales quote will be the first communication you send your customer that reaches a wider audience of decision makers, so ensuring it’s a relationship maximizer is absolutely critical.

You want the quotes you send to both build your brand and reflect your customer’s as well. You want the product and pricing configurations in your quotes to be priced optimally for you, and fairly for your customer. You want to dot T’s and cross I’s and ensure no detail is missed and no reply is late. You want a system dedicated to these features and functions, and within D365, which is why companies are adding CPQ to Dynamics.

CPQ maximizes every quote, making it more professional and more targeted, and optimizes the quote tracking process as well.

If you’re serious about customer relationship maximization, it’s time to get serious about how you’re creating, sending, and tracking the most critical step: the sales proposal.

Let’s block ads! (Why?)

CRM Software Blog | Dynamics 365

Monitoring the Power Platform: Power Automate – Management and Admin Connector Deep Dive into My and Solution Aware Flows

May 9, 2020 Microsoft Dynamics CRM

Summary

Microsoft Power Automate provide users the ability to quickly create and integrate applications within the enterprise. With this ability comes concerns with governance to ensure users are allowed the appropriate permissions as well as identifying and highlighting specific connections created across the organization. Flows offer a wide array of connectors and the ability to identify which of these are used is essential.

This article is designed to examine common operations that administrators would use to govern environment flows and how best to work with both the Power Automate Management and Power Automate Admin Connectors.

The Common Data Service and fundamental concepts, such as packaging and distributing customizations with solutions, are paramount to providing an automated and auditable delivery process. With that in mind, we will also explore how the Management and Admin connectors are impacted by the usage of both My Flows and Solution Aware Flows.

Power Automate Management Connector and Actions

Inside of Power Automate and Canvas Apps resides the Power Automate Management Connector which allow administrators the ability to manage flows.

[Author’s Note: Click on each image for clarity]

Monitoring the Power Platform: Power Automate Management and Admin Connector Deep Dive into My and Solution Aware Flows

With this connector within Power Automate Flow, administrators can schedule or run instantly a report of activities within a specific tenant, environment or even a flow itself. Reported items include listing environments, connections, flows and their owners and users. Actionable items include creating and deleting flows, turning flows on and off and modifying permissions and ownership on a flow.

Permissions

The Power Automate Management connector, dependent on DLP Policy, can be used by makers who may not have the appropriate permissions. If the maker does not have admin access they will get an error. This is important to address before we move further into common operations and scenarios with both connectors.

Monitoring the Power Platform: Power Automate Management and Admin Connector Deep Dive into My and Solution Aware Flows

From the article “Announcing new Admin and Maker Connectors for PowerApps and Flow” by Denise Moran:

“Office 365 Global Administrator or Azure Active Directory Global Administrator permissions if you need to search through another user’s resources. (Note that Environment Admins only have access to those environments and environment resources for which they have permissions, and global admins can see all resources across the tenant.)“

Common Operations and Differences between Solution Aware and My Flows

Some of the actions I find useful regularly within this connector include List My Connections, List Flows as Admins, Get Flow as Admin and List Owners and Run-Only Users of Flows. For this section we will focus on utilizing these actions and how they can be used with both My Flows and Solution Aware Flows.

List Connections

List My Connections will list the connections and provide user context and connection status. If a connection has an error code, it can be found here and reported.

Monitoring the Power Platform: Power Automate Management and Admin Connector Deep Dive into My and Solution Aware Flows

List Flows

List Flows as Admin provides a list of flows within an environment. Each flow returned contains the current state, the flow name and when it was created and last modified. For solution aware flows, we can also find the workflow primary key for use with the Power Automate API.

Monitoring the Power Platform: Power Automate Management and Admin Connector Deep Dive into My and Solution Aware Flows

The Common Data Service API call, utilizing the WorkflowEntityId, shown below can be used to find further information about the flow.

 https://<org>.crm.dynamics.com/api/data/v9.0/workflows(0f67aeac-b03e-ea11-a812-000d3a54663d)

Its important to point out the difference between List Flows as Admin and List My Flows. Comparing the output of both we can begin to see that List My Flows only contains flows that are not solution aware aka My Flows. 
Solution Aware Flows will have the WorkflowEntityId as shown above as well as another value to help distinguish it from My Flows. As shown below, the sharingType property will be “CommonDataService” for Solution Aware Flows.

Get Flow
 
Get Flow as Admin can help admins determine the different triggers and actions that define a flow. This could be helpful to retain a sort of history of My Flows allowing versioning with source control. Get Flow and Get Flow as Admin seem to both return My Flows or Solution Aware Flows. Remember, solution aware flows are packaged within solutions and can also easily be stored in source control with the Solution Packager tool.

Listing Flow Owners and Run-Only Users
 
Working with Owners and Run-Only Users we see another difference between My Flows and Solution Aware Flows. My Flows will return the current owner and run-only users while Solution Aware Flows will not.

Here is a Solution Aware Flow using the List Flow Owners action.

That said, let’s continue to modifying flow owners to understand how we can leverage additional connectors to help out!
Modifying Flow Owners and Run-Only Users
 
Modifying My Flow Owners allows Flow makers to add (put) or remove (delete) other makers or users to become owners. This works the same with Run-Only users as well.

For Modifying Owners for a Solution Aware Flow, the Modify Flow Owner action not only doesn’t work as expected it will throw an error.

Sharing error:
Sharing through Flow is not currently supported for solution-aware flows. Permissions management should be conducted through the environment-linked XRM instance.

However, referencing back to our knowledge that Solution Aware Flows are CDS (XRM) records, we can leverage the Power Automate (CDS) Web API to help. It just so happens the reference to the Power Automate Web API provides examples to listing users who flows are shared and how to share and unshare a flow.
First, let’s review the Flow as is from the Flow Portal. The image below shows a single owner of the flow with no run-only users.

Moving into the Common Data Service, we can see that by locating the flow within the Common Data Service, the flow is not shared with anyone.

Using the Common Data Service current environment connector, we can call an unbound action (GrantAccess) to modify the PrincipalAccess of the Flow to use a systemuser or team. 

PrincipalAccess Formula:
{
  "Principal": {
    "@{string('@')}odata.type": "Microsoft.Dynamics.CRM.systemuser",
    "systemuserid": "{objectid}"
  },
  "AccessMask": "ReadAccess, WriteAccess"
}

Now, reviewing the Common Data Service record for the workflow we can see the updated principal access. The image below is not an accurate representation to the one above. To find out how permissions and AccessRights work, review this document.

For a complete example of this flow, showcasing how to share access to a solution aware flow, click here.
Looping back on Listing Flow Owners
Now that we have discussed the Common Data Service, let’s circle back to the listing flow owners scenario. Reviewing the Power Automate Web API we can see that to list users we need to use a CDS function called RetrieveSharedPrincipalsAndAccess. Here is the CDS documentation. Unfortunately the Common Data Service (current environment) connector currently doesn’t allow for calling functions. In this case we can leverage an Azure Function custom connector or call the Web API using the HTTP connector.
Using the HTTP connector with the CDS Web API, you’ll have to make a call to get the authorization bearer token which can then be used for subsequent calls to the API. The image below shows this conceptually.

NOTE: This technique is based on the documentation found here in the article “Power Automate Web API“. That said I cannot speak to nor guarantee the supportability of what is shown here.
Turn On and Turn Off
 
The Turn On and Turn off actions, as described, can be used to enable or disable flows. From my testing I was able to successfully turn on and turn off both My Flows and Solution Aware Flows using the two actions.

If desired, a sample showing how to update the flow workflow CDS record to turn on or off can be found here.
Power Automate for Admins Connector and Actions

 
Operational differences between My Flow and Solution Aware Flows
 
Enable and Disable
 
Enabling and Disabling flows using the Power Automate for Admins Connector look to work very similar to the Power Automate for Management actions. However, the Power Automate for Admins Connector actions will not work for Solution Aware Flows as they will for the Power Automate for Management connector. Below are example error messages when attempting to start or stop a Solution Aware Flow.


Error:
“CannotStopUnpublishedSolutionFlow” – “An unpublished solution flow cannot be stopped.”
“CannotStartUnpublishedSolutionFlow” – “An unpublished solution flow cannot be activated. Please authenticate the flow connections and save the flow to enable activation.”
Edit Flow Owner
 
This flow action allows an administrator to assign a edit (CanEdit) or view (CanView) role to a user. Unfortunately this action will not work with Solution Aware Flows and like the Management actions, will require the Common Data Service API.

Error:
“EntityFlowSharingNotSupported” – “Sharing through Flow is not currently supported for solution-aware flows. Permissions management should be conducted through the environment-linked XRM instance.”
Actions Tested
 
Power Automate Management Connector
 

 
Power Automate for Admins Connector
 

 
Common Data Service (current environment) Connector
 

 
HTTP Connector
 




Action
Description




Send HTTP Request
Used for the CDS Web API for calling functions and other unavailable actions




 
Making My Flows Solution Aware

 
As discussed in the article “Monitoring the Power Platform: Connectors, Connections and Data Loss Prevention Policies“, Solution Aware flows can be very useful but come with some current known limitations. Some current limitations include:


Button triggered flows are not available in solutions.


Flows triggered from Microsoft 365 applications such as Excel are not available in solutions.


Flows that connect to SharePoint are not available in solutions.


Importing a solution containing a flow will not automatically create or associate required connections. The flow must be edited to fix the connections.


That said, there are benefits to using Solution Aware Flows such as enhanced capabilities like Child Flows or even additional monitoring with Get-FlowRun (more on that in a future article!). If its needed to move a My Flow to a solution and as long as the known limitations do not apply, there are a couple of options an administrator has.
The first option is to manually add a flow from the Flow Portal. This process is fairly straight forward, simply click Add existing, locate the flow in the Outside of Solutions tab and add. This process will create activity records in the Office 365 Security and Compliance Center which can be used for auditing purposes.

The second option which works well with our goals of automation and continuous governance is to use the PowerApps Administration for PowerShell cmdlets. Included in the Microsoft.PowerApps.PowerShell module is a cmdlet called “Set-FlowAsSolutionAware“. This allows us to programmatically add a flow to a solution by providing the environment, flow name and the solution.
Set-FlowAsSolutionAware -EnvironmentName {environmentName} -FlowName {flowName} -SolutionId {solutionId}

This technique can allow us to automate the migration of My Flows using DevOps platforms such as Azure DevOps or GitHub Actions to building custom webhooks with Azure Function or Azure Automation.

Next Steps

 
In this article we have covered working with the Power Automate Management and Admin Connectors. Each offers unique actions to help administrators report and taken action on environment flow. Based on the type of flow we are working with, each connector action provides us different outcomes. 
The actions tested have been listed out for a reference and sample flows have been provided. Finally, we wrapped with the steps needed to migrate My Flows into solutions. 
If you are interested in learning more about specialized guidance and training for monitoring or other areas of the Power Platform, which includes a monitoring workshop, please contact your Technical Account Manager or Microsoft representative for further details. 
Your feedback is extremely valuable so please leave a comment below and I’ll be happy to help where I can! Also, if you find any inconsistencies, omissions or have suggestions, please go here to submit a new issue.
Index

 
Monitoring the Power Platform: Introduction and Index
Let’s block ads! (Why?)
Dynamics 365 Customer Engagement in the Field

Action	Description
Send HTTP Request	Used for the CDS Web API for calling functions and other unavailable actions

How to Customize Your Microsoft Dynamics 365 Sales (CRM) Solution

April 30, 2020 CRM News and Info

We recently talked about the benefits of customizing Microsoft Dynamics 365: from fields to forms and various business processes and workflows, you can adapt almost anything to your specific needs and realitiesBy ensuring your customer relationship management solution works well with your organization’s business processes, you can increase productivity, efficiency and user adoption across your organization.

Standard customizations such as views, forms, dashboards and entities ensure you get the information you need, but it’s also possible to put more advanced customizations in place. Microsoft’s vision over the past couple of years has been to provide several tools within the Microsoft Power Platform to allow customers to elevate their Dynamics 365 solution with advanced features and customizations on top of the basic system.

Here are a few of the tools at your disposal:

Microsoft Power Automate: Previously known as Microsoft Flow, Power Automate lets you create workflows, i.e. specific actions based on trigger events of your choice. These can be anything from simple notifications to multi-step workflows connecting several applications. Both robust and intuitive, Power Automate connects your various tools and applications to automate various processes.
Microsoft Power Apps: The Power Platform lets you build no-code or low-code apps to streamline processes and better meet your objectives. Configured from a simple interface, Canvas apps can be built for web, mobile and tablet applications, connected to your choice of 200 data sources. Model-driven apps use the Common Data Service (the platform on which Dynamics 365 applications are built) as their data source and are suited to more complex operations.
Microsoft Power BI: Power BI is a cloud-based business intelligence solution that fully integrates to Microsoft Dynamics products to increase your reporting possibilities and data analysis capabilities. Personalized dashboards and reports tailor the data according to users’ roles and needs for better visibility and insights, and this information can even be shared easily outside the Dynamics 365 user base.

All these tools extend the possibilities and capabilities for reporting and integration of your Dynamics 365 Sales solution. The trick is not to think of your CRM solution as only a tool for your sales team: its flexibility and customization options make it a powerful platform that can increase productivity and efficiency across your organization. Speak with your provider to know how to best leverage the customization tools at your disposal.

By JOVACO Solutions, Microsoft Dynamics 365 implementation specialist in Quebec

Let’s block ads! (Why?)

CRM Software Blog | Dynamics 365

Translating a SQL Server Schema into a Cassandra Table: Part III Many-to-Many, Attribute Closure and Solution Space

April 25, 2020 BI News and Info

The series so far:

This last in the series continues with an exploration of the many-to-many relationship. Then it returns to an analysis by functional dependency with attribute closure which is important in establishing “uniqueness” in the Cassandra key and other table properties. Finally, the four solutions to the original problem introduced in Part I are given. Note that code used in this article can be found here.

First, though, is a basic definition of redundancy.

Redundancy

Previous articles gave examples of redundancy. This section is part review and part new information. Download all code used in this article here.

Structuring in redundancy is inherent to the Cassandra design process. Redundancy between rows in the same partition or between partitions, though, can result in inconsistent data. Still, it is to be managed not avoided. Now an informal definition:

Redundancy occurs whenever a determinant of a given value and any of its non-trivial dependents could be in more than one row.

A trivial dependent set Y has the form X → Y where Y ⊆ X (reflexivity from Armstrong’s Axioms.)

Recall that form ‘X → Y’ establishes set ‘X’ as determinant, the ‘→’ as symbol for ‘functionally determines,’ and set ‘Y’ as dependent. Whenever rows repeat the exact same values in X columns, the Y columns must be invariant.

This definition is true regardless of whether the determinant and dependent sets are all in the key, all as non-key attributes, or as spanning key and non-key attributes.

(Redundancy, as defined here, is not related to replication, in which a partition and its rows are duplicated and synched over more than one node in the cluster(s).)

As an example, here is a query readout from the CQL shell over a sample table:

(Partition columns are red, clustering columns are aqua, and regular columns mauve.)

Figure 5 below is the dependency diagram created for the article series problem (and discussed later). The sample table here is bound by three of its functional dependencies, SC → G, S → N, and CS → P where S: state, C: city, G: city rating, N: state region and P: city population. Of them, only data involving FD S → N can be duplicated between rows in the same partition. The design is viable and the two rows shown don’t violate the FD – although they could.

Erroneous input into a different partition as above, however, causes all three FDs in the table to be violated. As you know from the previous article, there is no mechanism in the Cassandra system to prevent this and finding it with CQL only can be difficult.

FDs can change as requirements are better understood. Say SC → G becomes ISC → G, where “I” means “rating type;” prepend it to the clustering column list:

a screenshot of a cell phone description automati Translating a SQL Server Schema into a Cassandra Table: Part III Many to Many, Attribute Closure and Solution Space

As with SC → G, dependency ISC → G can only be broken in rows spanning partitions, but S → N as before along with CS → P now can break in the same partition:

word image 59 Translating a SQL Server Schema into a Cassandra Table: Part III Many to Many, Attribute Closure and Solution Space

This sketch applies both to one-to-many and one-to-one relationships – the functional dependencies as they occur in the unified relation. It also applies, in a way, to the many-to-many relationship as well.

Many-to-Many

Not discussed in the series so far is the common case of the many-to-many (m:n) relationship. The restrictions placed on attribute sets for functional dependencies in the primary key – see section Placement of Functional Dependencies in Part II – don’t apply to key attributes on either side. Consider this simplified ERD snippet:

a picture containing text map description automa Translating a SQL Server Schema into a Cassandra Table: Part III Many to Many, Attribute Closure and Solution Space

Figure 1. Conceptual trial model (Chen notation)

In the unified relation, the “site” one-to-many relationship establishes an FD with trial ID as determinant and courthouse name as dependent.

In contrast, the “participation” binary m:n relationship does not establish the m-side or n-side attributes as FDs in the unified relation. The relationship indeed combines two one-to-many relationships. However, they are not mutually independent and cannot be modeled in ERD as two distinct relationships. As a result, the lawyer identifier does not functionally determine trial attributes, nor vice-versa.

a picture containing map description automaticall Translating a SQL Server Schema into a Cassandra Table: Part III Many to Many, Attribute Closure and Solution Space

Figure 2. Not equivalent in meaning to Figure 1 “participation” m:n relationship

However, in relational and Cassandra modeling, the two one-to-many relationships are directly applicable. Say “lawyer name” and “trial ID” are both in the Cassandra key. If lawyer Stuart appears K times in combination with different trial IDs in “participation”, then pairings of Stuart and trial ID in the key yield K duplicates for each associated Lawyer entity attribute placed as a regular column. The n:1 relationship with the Trial entity is parallel.

Placing identifiers for both lawyer and trial in any part of the key in either order is always correct. They can both be in the partition, and their dependents made static (if clustering columns exist). If one or both is a clustering attribute(s), the second identifier implies expansion in the conceptual row nesting disc layout (see Part I).

For example, take C to mean courthouse, L lawyer and T trial identifiers. Given they are all in the key, restrictions are that C comes before T because T → C and T and C cannot both be in the partition for the same reason. Functional dependencies and m:n relationships mix easily in the primary key and table. Legal keys, then, include ((C)TL), ((C)LT), ((CL)T) and ((L)CT). Satisfy yourself that every successive clustering attribute in each key expands row nesting.

Any n-ary degree m:n relationship can cause significant redundancy within a table. Say the key is ((S)LCT) where S is the state wherein the courthouse resides (C → S). If lawyer Rudy has appeared in three courthouses and argued in three trials in each, then needed attributes in a relationship with Rudy appear as regular columns in nine rows. Attributes related to each courthouse are duplicated three times. If Rudy has a role in trials in more than one state, this redundancy is spread over multiple partitions.

This section concludes with a final example in logical and physical design based on Figure 1. Here is the access pattern for which to solve:

Q_n. Find trials started in a time frame by courthouse and lawyer

Notice first that courthouse and lawyer are in a m:n relationship.

To satisfy the access pattern’s implied range queries, Trial’s non-key attribute “start date time” – lower case “t” – must be a clustering attribute. It must follow lawyer and courthouse IDs in the key since by CQL rules, once a clustering column is sliced by an inequality operator in the WHERE clause, no clustering columns following it in the list may be restricted.

The trial ID (T) could follow t in the key because T → t. still, it seems that given a lawyer and a point in time, the combination should yield a trial, i.e. the FD Lt → T. This cannot be deduced using Armstrong’s Axioms. It requires a new business rule: A lawyer can start only one trial at a time. Now T must be removed from the key and made a regular column along with its related attributes (precisely because Lt → T).

Logical key possibilities then are ((C)Lt), ((CL)t) / ((LC)t) and ((L)Ct). The first ((C)Lt) would create the largest number of rows on fewest partitions, and the second, ((CL)t), the smallest number on most partitions. These are considerations for the physical stage and somewhat in the logical as well. The decision is made to use key ((L)Ct).

a screenshot of a cell phone description automati 1 Translating a SQL Server Schema into a Cassandra Table: Part III Many to Many, Attribute Closure and Solution Space

Figure 3. Table for intermediate-sized partition

Access pattern Q_n can now be satisfied with equality or range queries over time:

a screenshot of a cell phone description automati 2 Translating a SQL Server Schema into a Cassandra Table: Part III Many to Many, Attribute Closure and Solution Space

This table may be part of a two-pass query, the second retrieving documents and other trial details given the trial ID; refer to the application workflow.

Attribute Closure: Implications for Cassandra Design

This section returns to analysis by functional dependencies only.

These next subsections dovetail with techniques for discovering further properties of Cassandra tables and primary key design. The first presents a diagram of functional dependency relationships inferred from the SQL Server normalized physical design in Part I. The second, a code module that references the diagram as input for computing attribute closures given sets of initial attributes.

a screenshot of a cell phone description automati 3 Translating a SQL Server Schema into a Cassandra Table: Part III Many to Many, Attribute Closure and Solution Space

Figure 4. Normalized physical design (Part I)

The Functional Dependency Diagram

Functional dependencies placed into the unified relation come from the primary and alternate keys, the latter not shown by the SQL Server design tool. One FD not included (the dotted arrow) indicates that realtor location additional data is not relevant to the series problem’s access pattern.

Figure 5. Functional Dependency diagram from normalized schema

I’ve abbreviated attribute names to single letters, intuitive when possible. Herein I’ll often refer to attribute names as their single-letter counterparts.

The arrow recipients in the diagram are the dependents, and the arrows emanate from the determinants. Composite determinants are grouped in boxes; the double arrow implies a (1:1) relationship, I’ve elided redundant arrows in such relationships and so on. Not surprisingly is the double arrow between Y and SC (City# and State/City names}. (In the SQL Server logical design, the latter was key for the City relation, and in the physical design, the surrogate City# was added as primary key, and the composite was enforced as alternate key). Follow the arrows to see the partial and transitive dependencies.

By visual inspection alone, you know that L, EZ and H functionally determine, directly or transitively, all other attributes. As each is irreducible, each is a candidate key. The diagram is an important artifact. What is needed, though, is an additional tool for proving certain properties of key and table design choices.

The Attribute Closure Module

The elementary code module below does a closure over an initial set of attributes in the unified relation given the relation’s set of functional dependencies. Its purpose is to determine which key attribute additions make for “uniqueness,” and which attributes become static or regular attributes in different Cassandra key design choices. This analysis is essential for larger, more complex relations.

a screenshot of a cell phone description automati 4 Translating a SQL Server Schema into a Cassandra Table: Part III Many to Many, Attribute Closure and Solution Space

Figure 6. Attribute closure function

I wrote the solution in Scala. If you’ve done C# or C++ or Java or other imperative/object-oriented language, the algorithm shouldn’t be difficult to follow. The one twist is that Scala also fully supports functional programming. This style allows functions to appear wherever a variable could. Thus, I’ve nested the “go” function inside function “attributeClosure” rather than placing it separately as a private function in the wrapper class (or object) where it most certainly wouldn’t be reused by other methods.

Essentially, each recursive invocation of the inner function uses set operations to glean the next set of dependent attributes implied by two structures. The first structure is the invariant set of FDs (“fdependencies”) as passed into the outer function. Second, the inner function’s parameter (“attrClosureSet”): the set of already captured attributes, acting as the source of possible determinant combinations. This set is initialized from parameter “closureAttrs”.

At each recursive step, if new (dependent) attributes are added to the closure, iteration continues. If not, the inner function returns the closure to the outer. The outer then returns a two-tuple consisting of the closure plus the Boolean decision as to whether the initial attribute set attrClosure is a superkey for the relation.

The dependencies input, “fdependencies”, mirrors the dependency diagram (Figure 5):

a screenshot of a cell phone description automati 5 Translating a SQL Server Schema into a Cassandra Table: Part III Many to Many, Attribute Closure and Solution Space

Figure 7. Sample structuring of functional dependencies in Scala

The input dependencies for this implementation are irreducible, meaning it meets these criteria: a) right-irreducible: each dependent is a singleton; b) left-irreducible: each determinant set is minimal; and c) there are no trivial FDs (A → subset(A)) or FDs derivable from other FDs. In this implementation, c) is waivable. For any implantation, b) is required.

Beyond the design stage, functional diagrams and this algorithm can be instrumental in tracking down FD data errors in tables, e.g. via a Spark application.

Sample run readouts are deferred for the next section.

Solution Space

The series began with the stated goal of transforming a normalized SQL Server schema (Figure 4) into a Cassandra table designed for this access pattern:

Q_k Find listings for a realtor company in a city

Realtors and cities are in a m:n relationship, so their ordering in the key doesn’t matter.

The solutions can reference the results of applying the FD set in Figure 5 and initial attribute sets to the closure function in Figure 4.

a screenshot of a cell phone description automati 6 Translating a SQL Server Schema into a Cassandra Table: Part III Many to Many, Attribute Closure and Solution Space

Figure 8a. Sample closure runs (eclipse software)

Incidentally, Scala runs in PowerShell; see readme.txt file included in the downloads for instructions on installing and using Scala.

a screenshot of a cell phone description automati 7 Translating a SQL Server Schema into a Cassandra Table: Part III Many to Many, Attribute Closure and Solution Space

Figure 8b. Sample closure runs

I’ll repeat a central point. The closure is an important method for deciding whether and which attributes need be appended to the primary key for “uniqueness”: i.e. to cover all attributes using minimal collections. This is not the same as making the key unique; the Cassandra key is always unique. Collection types make this possible. If the key were R, e.g., its singleton row would have collections structured over combinations of CS-Y and L and their related attributes. You saw this in Part II.

To satisfy the access pattern, RSC or RY – not both! – must be in the key as search attributes. There are now two decisions to make. The first, as you expect: is it RSC or RY? In consultation with the analytics team, secondary stakeholders to this OLTP database, they would prefer RSC as it simplifies querying over RS by itself.

The second concerns selecting partition attributes. In the logical model, R by itself is sufficient; in the physical, assume the row count and size are manageable and performant, so the base key remains ((R)SC). Closure results indicate, among other conclusions, that attributes T, B and D must be static.

This discussion ends what has been considerable background, except for one more tool.

Design A: The Minimal Superkey

As per the closure, L is a relational candidate key. Could it be the partition and full primary key (no clustering attributes) for its singleton row while satisfying the access pattern?

a screenshot of a cell phone description automati 8 Translating a SQL Server Schema into a Cassandra Table: Part III Many to Many, Attribute Closure and Solution Space

Figure 9. Possible logical model from the KDM

The Kashlev Data Modeler (KDM) is a tool for automating Apache Cassandra logical and physical designs (see references). It produced the logical design above given key L and an ERD. KDM uses Chebotko symbols to identify Cassandra column types. The “K” is for partition column.

Here is the actual table definition in CQL I devised (not from KDM):

a screenshot of text description automatically ge Translating a SQL Server Schema into a Cassandra Table: Part III Many to Many, Attribute Closure and Solution Space

Figure 10. Physical design featuring minimal superkey and UDTs

This is the query showing the first three rows returned (almost):

word image 60 Translating a SQL Server Schema into a Cassandra Table: Part III Many to Many, Attribute Closure and Solution Space

Well, the CQL shell tried. The error message with terms “data filtering” and “unpredictable performance” means this: The system can’t initially use the WHERE clause predicate conditions but must visit every singleton row in every partition – essentially, a table scan. Only then can It filter out rows not meeting the (RSC) search conditions. Depending on several factors, the query may involve many or most or even all cluster nodes, potentially vitiating the excellent performance of which Cassandra is capable. Then again, performance may be fine – just a warning and you can append the ALLOW FILTERING clause. Still, it’s an unacceptable warning for the main access pattern for which a table was designed.

Secondary indexes help in some cases but not this one. In short, the table is too highly transactional, and the ALLOW FILTERING clause would still be required. I placed indexes on all RSC columns and Cassandra did use the one with the highest selectivity – the one on the city name:

a screenshot of a cell phone description automati 9 Translating a SQL Server Schema into a Cassandra Table: Part III Many to Many, Attribute Closure and Solution Space

Among several examples of data duplication here is owing to dependency CS → GP (city to rating and population), or alternatively Y → GP (city UUID to same). Thus (San Francisco. California) yields (4.7, 884,363) for every listing row in this city in different partitions. This particular redundancy is better managed in the remaining solutions, which build from the base ((R)SC).

I stress again that the major problem is the table scan. Regardless, you may have concluded that the candidate key approach given Q_k would work better later in the application workflow with a different access pattern. There, its attributes can be more specific to the listing such as a video, floorplan, and information about schools.

Design B: Key Sans Uniqueness

In this solution, the key is ((R)SC). The redundancy owing to CS → GP is eliminated. However, as per closure results (Figure 8), it can’t reach all attributes – specifically, those for the listing. Since the listing identifier functionally determines RSC, the solution requires placing the listing identifier and its directly reachable attributes coalesced into a collection:

Figure 11. Physical design where key lacks uniqueness column

Read this to mean that a realtor company in a city has zero or more listings.

Notice that given R → BDT and ((R)SC), Design B introduces static (UDT) column “address” (Figure 10). The static designation ensures that address is not repeated into each row in a partition. It further enforces the first rule of functional dependencies as introduced in Part II: a determinant (R) and its dependents (BDT) can never appear together in the partition key.

The query result shows listings for a single row. For the testbed, the design collapses six individual listings into one row. A realistic example could have hundreds of listings. In querying, the client potentially can access numerous rows. The size of the return set (each row listing collection being indivisible), the highly transactional nature of the collection and other factors render this solution unsuitable.

The recommendation is to use collections when data is smaller, bounded and need little updating. Think, as a simple example, book authors in a list.

Design C: The Non-Minimal Superkey

More to standard is appending the listing identifier as uniqueness attribute to make ((R)SCL); logical and physical designs are as follows:

a screenshot of a cell phone description automati 10 Translating a SQL Server Schema into a Cassandra Table: Part III Many to Many, Attribute Closure and Solution Space

Figure 12. Recommended designs for access pattern Q_k

Continuing Chebotko logical notation, the C↑ is clustering column ascending order, and the S is static.

By constraining on the prefix of key columns short of listing ID, the query satisfies Q_k as the first three rows returned from the sample query show:

a screenshot of a cell phone description automati 11 Translating a SQL Server Schema into a Cassandra Table: Part III Many to Many, Attribute Closure and Solution Space

Listings that were compressed in Design B are now each in their own row. No less information is retrieved – in fact, the design accounts for more redundancy as seen in regular columns for state and city. Unlike B, however, row modifications in this highly transactional table are simpler and safer, and clients can take advantage of paging of listings over realtor-city pairings.

Design D: The Non-Minimal Superkey with Additional Ordering

Sometimes a table is correctly designed but still falls short of needs. The problem, then, is the access pattern itself: it needs a fuller description.

Perhaps searching on or organizing data by zip codes, or range querying over asking price is also desired. Say the access pattern is altered thus:

Q_k Find listings for a realtor company in a city sorted from least to highest asking price

Starting from Design C, pull the asking price out of the listing standard UDT (Figure 10) and wedge it between the search and uniqueness columns: ((R)SCAL):

a screenshot of a cell phone description automati 12 Translating a SQL Server Schema into a Cassandra Table: Part III Many to Many, Attribute Closure and Solution Space

Figure 13. Logical and physical designs with ordering on asking price

Client software can now page through the table already sorted by price, or do a range query as seen here:

a screenshot of a cell phone description automati 13 Translating a SQL Server Schema into a Cassandra Table: Part III Many to Many, Attribute Closure and Solution Space

Always think conceptual row nesting on disc. As a general rule, inserting a new attribute(s) into the clustering list must expand row possibilities for key attributes defined before it. You may want to review Part I for specific rules on clustering column type and placement.

Last Word

Relational SQL Server and NoSQL Cassandra are meant for different use cases. As such, there may be more differences than similarities between them. You’ve seen a number of differences, including rules for key design and the ability to enforce integrity constraints. I won’t list them or introduce others here, but I will leave you with one final thought.

If conventional wisdom says that the relational model and theory has no valuable insights for NoSQL systems, do you (still) believe it?

References

J. King, “DS220: Data Modeling,” https://academy.datastax.com/resources/ds220-data-modeling, a DataStax Academy online video series course on Apache Cassandra.

DataStax Documentation, “CQL for DataStax Enterprise 6.7,” https://docs.datastax.com/en/dse/6.7/cql/cql/cql_using/queriesTOC.html, primary key and querying.

J. Carpenter, E. Hewitt, Cassandra The Definitive Guide Distributed Data at We Scale, 2nd ed. Sebastopol: O’Reilly Media Inc., 2016.

A. Kashlev, “The Kashlev Data Modeler,” http://kdm.dataview.org/, an online automated tool for developing Apache Cassandra logical and physical designs.

DataStax, “Data Modeling in Apache Cassandra 5 Steps to an Awesome Data Model,” https://www.datastax.com/sites/default/files/content/whitepaper/files/2019-10/CM2019236%20-%20Data%20Modeling%20in%20Apache%20Cassandra%20%E2%84%A2%20White%20Paper-4.pdf?mkt_tok=eyJpIjoiWmpnMk4yWTVaR0l6WTJOaiIsInQiOiJWTFQyYW5LRkljSWt2Q28xSDR5RXBBNmNlM1pLbThFTFFwSHJLd0E1NXdYM2VmbHJaam1HelFSSThCYW1JRVZVSE9uM1YzTmYzbDVTUlB5c3RESlNyTTJYMSs3bE90QlNiVmRrZkg2ZDZINlYrS1MralJ1Zzh2K1pKNVZ0K0g2aSJ9, White paper .pdf.

C.J. Date, An Introduction to Database Systems, 8th ed. Boston: Pearson Education Inc., 2004, esp. Chapter 11 Functional Dependencies.

D.R. Howe, Data Analysis for Database Design, 3rd ed. Oxford: Butterworth-Heinemann, 2001, esp. Chapters 9 Properties of relationships and 10 Decomposition of many:many relationships.

Let’s block ads! (Why?)

SQL – Simple Talk

Tag Archives: Solution

Credits

Approach 1: PowerShell Script

Approach 2: Azure DevOps Pipeline

Prerequisites

Building and Configuring the ADO Pipeline

Summary

Power Automate Management Connector and Actions

Permissions

Common Operations and Differences between Solution Aware and My Flows

List Connections

List Flows

Get Flow

Listing Flow Owners and Run-Only Users

Modifying Flow Owners and Run-Only Users

Looping back on Listing Flow Owners

Turn On and Turn Off

Power Automate for Admins Connector and Actions

Operational differences between My Flow and Solution Aware Flows

Enable and Disable

Edit Flow Owner

Actions Tested

Power Automate Management Connector

Power Automate for Admins Connector

Common Data Service (current environment) Connector

HTTP Connector

Making My Flows Solution Aware

Next Steps

Index

Redundancy

Many-to-Many

Attribute Closure: Implications for Cassandra Design

The Functional Dependency Diagram

The Attribute Closure Module

Solution Space

Design A: The Minimal Superkey

Design B: Key Sans Uniqueness

Design C: The Non-Minimal Superkey

Design D: The Non-Minimal Superkey with Additional Ordering

Last Word

References

Recent Posts

Categories

Archives