Tag Archives: Services
Improve the Usability of Dynamics 365 Sales for Professional Services Firms with Power BI
If you’re already using the dashboards provided by Microsoft Dynamics 365 Sales, you may not see the need to use Microsoft Power BI as well. However, it can be an important tool to help professional services firms obtain better visibility on all the information they need, even data outside the CRM.
Dynamics 365 Sales dashboards only report on data found within your customer relationship management solution, but if you also want to consult information from outside the CRM, does it make sense to integrate all this data? Maybe not. That’s where embedded Power BI dashboards come in: Power BI lets you cross-reference information from virtually any database, letting you consult this information without having to bring it into the CRM first.
If you’re currently using your CRM for sales information and have another system to manage operations, you may also want to bring together the data stored in both systems For professional services firms, this could be what is coming up in the pipeline versus booked service hours, for example. The pipeline hours might come from the CRM and the booked hours from your , but your sales team needs visibility on both to know what your bandwidth will look like over the next few months. While bringing operational information into the CRM may not make sense for your organization, the business development team may still need access to this information to make informed business decisions.
With embedded Power BI dashboards, Dynamics 365 Sales remains your main point of entry, allowing your users to access and find all the information they need from a centralized platform. Having all necessary details gathered in one place keeps things simple and user-friendly, increasing the likelihood that users embrace the change and adopt your new solution.
Visit our website to learn more about or , or so we can learn more about your organization and help you meet your specific needs.
About JOVACO Solutions
JOVACO Solutions is a leading ERP and CRM solution provider operating in Quebec for over 35 years. As a specialist of Microsoft Dynamics business management solutions, we offer a wide range of products and services to meet all the needs of professional services firms and project-based organizations. We also offer specialized project management tools and timesheet add-ons fully integrated to Microsoft Dynamics solutions. Visit or for more information.
How Dynamics 365 Sales Can Optimize the Sales of Professional Services Firms
While a customer relationship management solution is an important tool for any industry, it may be easier for companies that sell products to make a case for the use of a CRM than it is for professional services firms. As the latter sell time and expertise, they may not need to track which products clients use, but it’s just as important to properly manage client connections and business relationships, and to catalogue communications and proposals sent to clients.
Manage longer sales cycles and proposals
Typically, the higher the dollar value, the longer the sales cycle. Depending on the type of service you offer, your sales cycles are probably longer than most, and the necessity to track what has been done in the past becomes even more important. Dynamics 365 Sales lets professional services firms tailor the sales cycle to their specific reality, as it probably involves quite a few more steps than just a quote and a follow-up.
A CRM solution lets all the various stakeholders involved through the sales cycle have access to the information they need to facilitate the hours required for a particular mandate. It also provides an easy place to store all your mandates, as the integration between Dynamics 365 and Microsoft 365 (previously known as Office 365) lets you store your proposals in SharePoint, facilitating collaboration when putting proposals together in Microsoft Word or PowerPoint.
Strengthen your business relationships
After the initial sale, other critical elements for professional services firms is better visibility on future cross-sell possibilities, as well as retaining all contact information and the different influencers and connections within your firm. Having all this information centralized ensures you never miss an opportunity. Your professionals can manage and foster each client to build strong business relationships and ensure appropriate follow-up on all tasks, activities and opportunities.
Streamline and automate your business processes
An automated approval process within your sales cycle is another functionality that offers major gains to professional services firms, as it allows you to personalize your business approval process depending on size, industry or service. Automations can also be used to send reminders for follow-ups, meetings and other client tasks. Dynamics 365 also lets you incorporate other business processes into your solution, such as your employees’ various competencies and specialties to find the ideal staff members for a particular project.
All these features make Dynamics 365 much more than a traditional CRM system. One of the major benefits of a CRM solution for professional services firms is having a platform that will help structure your organization. At a certain point, you will need to systemize and automate certain processes to maintain the growth of your organization. To learn more about how a management information system can help you in your growth journey, check out our eBook
By JOVACO Solutions,
NetSuite Expands Integration Capabilities with new REST Web Services
Posted by Tomas Novotny, Senior Product Manager, SuiteCloud Platform – Integration
SuiteCloud SuiteTalk REST Web Services are now generally available for all Oracle NetSuite products, extending a robust set of complementary SuiteTalk SOAP web services that have been part of NetSuite’s core integration solution for several releases. Including REST web services in SuiteTalk provides developers and administrators with even more options for integrating NetSuite with external applications, environments and data sources.
REST is a modern, lightweight, easy-to-use application program interface (API) based on a widely adopted architectural style. SuiteTalk delivers a full range of REST APIs exposed via a standard web services interface, securely accessible from external client applications.
REST web services provide users with seamless access to NetSuite record metadata, support API discoverability, offer link-based navigation and give users a uniform approach to interacting with both custom and standard records.
Another benefit: no server-side coding or management of custom scripts is necessary as is the case with server-side RESTlet scripts used to extend SuiteScript APIs when building REST-based integrations.
Here are some of the SuiteTalk REST Web Services Highlights from the latest update:
Metadata – Web services metadata provides schema defining each contract, enabling developers to dynamically discover API endpoints. This metadata contains all available resources such as records, data formats, values for input and output fields and supports HTTP methods and query parameters. Metadata can be returned either as JSON Schema, or via OpenAPI 3.0 (Swagger) format.
CRUD Operations – Create, Read, Update and Delete operations are now supported on records, sublists and subrecords within the parent record, and developers can make use of internal and external IDs to identify and reference these records. Additionally, upsert operations are now fully supported. It’s also possible for control the level of expansion for record subresources (sublists and subrecords) as a link or a fully expanded entity, and record subresources can even be accessed directly.
Query – The NetSuite 2020 Release 1 version of REST web services introduces powerful new query functionality, an area in which NetSuite will continue to invest. The NetSuite REST API now provides users with direct access to SuiteQL, a highly performant SQL-like query language. You can even execute advanced ad-hoc queries to perform actions such as multi-level joins, filtering, sorting, aliasing and paging. Logical, relational and equality operators are supported and make it possible to execute Saved Workbooks data sets.
Supported Records – All SuiteScript records available in the NetSuite 2020 Release 1 are supported in REST to maximize coverage of business objects. Please note that certain records may still be in beta status, so be sure to review the API and developer documentation to learn more.
For those who haven’t tried out these powerful new REST web services, spend some time exploring news ways to integrate NetSuite with other applications and environments managed by your organization.
To learn more, please refer to the Online Help articles available in NetSuite Help Center or SuiteAnswers, and get more specifics on the new features in the Release Notes.
by NetSuite filed under
Live Webinar: How Financial Services Firms are Using the Microsoft Power Platform to Accelerate CRM by “Minding the Gap”…Even Around Salesforce!
Have you been to London? Then it’s likely you’ve ridden the Tube (subway) and heard an announcement politely saying as you enter or exit: “Mind the gap.” This deceptively benign statement was put there to warn you to watch out for the space between the platform and the train because if you don’t “mind the gap,” you can trip or fall. That could mean going to the hospital or at the very least, having a bad day. Either way, you slow down or even end your journey to your destination—and you’ll probably slow down the train, as well.
What is the connection between minding the gap and financial services?
“Mind the gap” has been adopted by business experts as an analogy. In an article, entrepreneur Josh Linker said, “While the gaps in your organization aren’t screaming for attention, they can be a wellspring of opportunity…the rallying cry for you to explore what’s missing, what’s not there, and what could be.” The act of minding gaps in your operations could mean failing to realize your vision (or at least slowing it down) or reaching it faster.
How do you realize your vision…and realize it quickly? Use the Microsoft Power Platform to mind the gap
Nobody wants to delay realizing their goals or vision, and they certainly want to avoid missing goals and missteps along the way. You do that by identifying the gaps—processes, technologies, methodologies—then look for ways to address them with the right technology. The issue is, unfortunately, that traditional approaches of customizing off-the-shelf software that is already in place or spending lots of time and money building a custom application doesn’t work or is cost-prohibitive. That’s when it’s time to turn to the .
The Microsoft Power Platform: A low-code, rapid application development environment that bridges the gaps
Smart financial services firms are already using the Microsoft Power Platform to quickly and easily build apps, automate workflows, and extend solutions they already use—not just Microsoft applications. Even software like Salesforce, Oracle, and SAP can be extended by the Power Platform to bridge any gap.
This might sound too good to be true, but it works—fast and cost-effectively. Plus, you don’t have to uproot, change, or replace applications or systems you’re already using. If you’re happy with what you have but just have that one gap to fill, the Power Platform does it with finesse. Here are a just a few examples of AKA financial services clients that have put the Power Platform into action and are already seeing results:
Automated Client Reporting…From Salesforce: The asset management arm of a life insurance company was risking penalties and losing huge investors, but they didn’t want to replace Salesforce. AKA built a Power Platform solution “surrounding” Salesforce that streamlined the process, resulting in empowered, happy employees who can put their focus on delivering white-glove customer service.
Client Engagement Hub with Next Best Action…Within Dynamics 365 CRM: A global asset management firm had a goal to double sales growth, but their current system had a poor user interface, did not offer personas, and did not offer insights for next best actions. AKA designed in the Power Platform and rolled out in just 3 months a Client Engagement Hub with Next Best Action—fully integrated with Dynamics 365 CRM and Marketo.
Mobile Trade Approval App…Integrated with Salesforce: Portfolio managers for an investment firm were slammed with emails for trade approvals. They needed an easy, fault-proof way to approve/disapprove trades from mobile devices. In a one-day workshop, AKA designed a Power App that integrates with Salesforce, enabling the managers to approve trades with the click of a button on their mobile devices.
Build an app to bridge that gap
Would you like to see for yourself how the Power Platform works? , hosted by , where you’ll see how other financial services firms have used the Power Platform to accelerate their CRM systems by eliminating gaps:
LIVE Webcast
Bridging Gaps and Building Apps – IN DAYS, NOT MONTHS
How 5 Financial Services Firms are Using the Microsoft Power Platform to Accelerate their CRM–Including Salesforce
April 7, 2020 | 1 pm EDT / 10 am PDT
During the webcast you’ll learn about the Power Platform and what makes it the best choice above custom development or customizing existing software. We’ll discuss several typical gaps the Power Platform bridges, followed by 5 real-life examples of how the Power Platform is being used at by financial services companies.
ABOUT AKA ENTERPRISE SOLUTIONS
specializes in making it easier to do business, simplifying processes and reducing risks. With agility, expertise, and original industry solutions, we embrace projects other technology firms avoid—regardless of their complexity. As a true strategic partner, we help organizations slay the dragons that are keeping them from innovating their way to greatness. Call us at 212-502-3900!
Article by: Amy Spencer | 212-502-3900
Amy leads the team that is responsible for go-to-market strategies, messaging, and demand generation for AKA’s financial services practice. The better part of her 25+ year career has been dedicated to working for Microsoft’s most successful ERP and CRM partners, including The Taylor Group (aka ManagedOps.com & Surebridge), Navint (acquired by Tribridge and then DXC), Innovia Consulting, and now AKA Enterprise Solutions.
The Intelligent Professional Services Enterprise: Three Ways Forward
Will the largest services firms of the future have no employees? For years, the industry was built on the “leveraged talent” model: teams of highly educated people would grapple with a problem for a client and charge fees based on time and materials. Today, that model is giving way as professional services firms automate basic tasks, develop digital knowledge-based products, and rely more on a network of non-employee workers.
Never before has technological advancement offered more innovation and transformation opportunities for this industry. Intelligent technologies allow firms to grow revenue without increasing headcount. They can also empower employees to provide higher-value services for their clients.
But the question remains: how do organizations in the professional services industry move forward to become intelligent enterprises? The following strategic priorities are critical for making the transition.
Outcome-based engagements
Clients today expect professional services firms to deliver engagements where payment is based on a predefined set of outcomes rather than on the effort expended. Such outcome-based models transfer much of the project risk from the client to the service provider.
To minimize this risk, firms seek to implement frameworks for delivering outcome-based engagements successfully. They are starting early in the engagement process and leveraging capabilities such as intelligent deal-scoring, intelligent bid management, strategic execution, and post-delivery processes.
Critical to success is a smart, regimented bidding process that makes sure proposals are rigorously developed and accurate and use insights from past engagements to ensure that the outcomes can be realistically delivered with the planned effort. Firms are moving toward predictive analytics to monitor progress throughout the engagement to ensure that services are delivered as intended and that margins are realized.
Experience management technology is also playing an important role – enabling firms to quickly get accurate insights on the key drivers of the client experience. Based on data collected, firms can generate insights and implement programs to drive better business outcomes and deliver services more efficiently.
Knowledge as a service
Technology advances are allowing firms to innovate at speed and deliver expert knowledge in a digital package at scale. The goal is to unlock nonlinear growth by driving new revenue streams through innovative digital services that are instantly available, accessible, and affordable on an agile digital platform.
Today, firms are opening up their intellectual property to better monetize their institutional expertise via relevant digital services – such as tax or legal advice dispensed with help from intelligent natural-language processing.
By providing access to digitalized expertise, professional services firms are rewriting the underlying economics for their business. One critical capability moving forward is the need to understand the full lifecycle of the digital service – including the initial cost to develop, ongoing support, and lifetime revenue. For the knowledge-as-a-service model to work, firms also need to adopt a continuous innovation process and a digital cloud platform that contains all the required components. These components comprise a data platform, readily available business services, client feedback mechanisms for the product or solution experience, and intelligent technologies under the umbrella of an innovation system.
Talent networks
Today’s workforce is undergoing a massive cultural shift toward talented young professionals wanting a new work experience as free agents. The result is that traditional resourcing models are being disrupted.
Faced with the need to flexibly scale their workforce according to demand, professional services firms need to tap into talent networks to secure the right skills and resources at the right moment. Digitalization has enabled the emergence of an open talent economy that brings together people and work in a borderless workplace. Talent is readily accessible on a global basis, driven by the gig economy, crowdsourcing, and global platforms and networks.
Professional services firms, accordingly, are starting to use talent networks as an extension of the firm and seamlessly deploy these resources alongside their own employees, allowing them to flexibly scale resource capacity and access scarce skills. But execution depends on enhanced resource-management processes that enable a paradigm shift from “Who do I have with skills available?” to “Who is the optimal person (internally or externally) to deliver the service?”
Integrating talent networks, along with internal resource pools and even applicant profiles, will enable firms to make sophisticated “staff, hire, or contract” decisions that are based on a combination of skills, availability, cost, quality, and strategic criteria. Intelligent onboarding and personalized learning processes, meanwhile, are being used to integrate external talent with the company’s culture and working methodologies, key internal knowledge, and expertise. This is critical to provide clients with a consistent brand experience and level of service.
The future is bright
Ultimately, the basic value proposition of services companies – providing specialized expertise to their clients – will not change. But moving forward, the intelligent enterprise will prosper based not just on what they know but also how they deliver that knowledge.
Remember, too, that professional services companies are instrumental in helping their clients undergo digital transformation. Now is the time for those same firms to transform themselves.
Join our virtual event to learn more
Want to find out more about the latest trends and innovations in today’s global professional services business? Register today to join our flagship 60-minute virtual industry event:
SAP Industries Forum 2020: Professional Services
Experience the Intelligent Enterprise
Thursday, March 26, 2020
9:00 a.m. PDT | 12:00 p.m. EDT | 5:00 p.m. CET
Professional Services Companies: NetSuite 2020 Release 1 Helps Firms Thrive in a Time of Change
Posted by Adam Mayo, Master Industry Principal, Services
From globalization and automation to the way services are billed, professional services firms are forced to adapt to a fast-evolving marketplace. And with an expected compound annual growth rate of more than 10% over the next decade, it’s in their best interest to go along for the ride.
“We’re experiencing unprecedented disruption in how (services) businesses operate, driven largely by changes in customer expectations and behavior,” Frank Della Rosa, a research director at IDC, said last year.
Among those expectations: More pricing transparency and value-based services. Meanwhile, professional services firms face increased competition from globalization and pressure to keep up with emerging technologies such as AI. In such a volatile environment, it’s critical that firms closely monitor the health and finances of projects, and position themselves to capitalize on additional potential revenue streams.
With NetSuite 2020 Release 1, professional services firms can make themselves smarter, faster and more adaptable with an expanded array of capabilities that deliver visibility, automation and global financial integration:
An improved set of tools and smarter analytics simplifies management of project budgets. Organizations can now easily see how costs are impacted when adding and editing those project budgets. Automated cost calculations help to increase accuracy, and miscoded time or expenses can now be quickly identified. What’s more, NetSuite now offers system suggestions for updating time estimates when unexpected costs arise.
- New time modification enhancements provide better control of billable time. Time entries can now be easily modified, and an audit trail makes it possible to correct entries entered against the wrong project. Plus, NetSuite Analytics now supports time modification requests and entries, improving visibility into changes.
- Enhancements bring automation and flexibility to measuring the health of projects. Automated calculation of key financial data raises the accuracy of project, budget and work breakdown structure records.
In NetSuite project profitability reports, tracked and actual time are better categorized as either committed or actual costs, and forecast and actual charges are now easily included for better insight into project billing.
- On the global financial front, the combination of seamless banking integration via the Bank Feeds SuiteApp and a new banking plug-in featuring open banking APIs enables automated import of banking data into NetSuite from financial institutions around the world and delivers increased financial insight. An intelligent rules engine powers automated bank data reconciliation, resulting in improved matching and reduced clutter.
- Tracking and payout of bonuses now benefit from a single source for employee and financial data. HR now has the flexibility to support numerous bonus types, such as spot, referral, merit or custom, enabling it to better track incentive pay, while employees have visibility into current bonus potential, providing motivation. And all of this bonus data flows seamlessly into SuitePeople Payroll.
- NetSuite now enables managers to make employee change requests, while employees can approve vendor bills, all of which speeds processes, improves accuracy and frees up HR resources.
- Vendor bills can now be approved by employees, who are looped into the process, resulting in improved accuracy and efficiency. Vendor prepayments also can be streamlined and auto-applied to bills.
Learn More About NetSuite 2020 Release 1
These are just the highlights; there’s much more for services companies in NetSuite 2020 Release 1 than can’t be covered in a single blog post. For more specifics on the complete list and details of all the new features in the release, be sure to read the Release Notes.
New to the release process? Learn more about it here.
Finally, be sure to request access to your Release Preview account. There, you can test to see how the new features will work with your own data and get a jumpstart on maximizing the impact of NetSuite 2020 Release 1.
by NetSuite filed under
SuiteBilling Simplifies Complex Billing for Managed WiFi Services Provider
.jpg "SuiteBilling Simplifies Complex Billing for Managed WiFi Services Provider")
Posted by Barney Beal, Content Director
For Datavalet, a Canadian WiFi managed services provider, billing a customer can be a complicated endeavor. Managing WiFi services for hotels, restaurant chains and the like means customers vary widely.
“McDonald’s has more than 1,500 restaurants in Canada, and every one has a subscription,” said Jean-Claude Homet, vice president of finance for Datavalet. “I need to make sure I have the proper number every month. On the other end [of the spectrum] I have a single hotel or a chain with two dozen sites.”
A combination of growth, demand for greater visibility into operations and the frustration of finance led Datavalet on a journey to solve not just its billing challenges but to replace an outdated accounting system.
Spun off from Nortel in 1998 as a company that provided wired internet services to the hotel business, Datavalet’s growth took off as WiFi emerged. It now serves managed WiFi solutions to large enterprises, restaurants, banks, insurances, hospitals, airports, hotels and school campuses. Its services span installation, network and infrastructure management, and it has become the leading managed WiFi provider in North America with 10,000 client locations.
For years, managing billing at Datavalet was done manually with spreadsheets, forcing finance staff to carefully check to make sure nothing was missed. But when new investors acquired the company in 2017, they wanted greater visibility into operations. Datavalet tried to reconfigure its existing Acomba accounting system to meet its requirements but when that failed to meet expectations leadership realized they would need new software.
The company looked at Sage Intacct, SAP Business One and Microsoft Dynamics before ultimately choosing NetSuite.
“Subscription billing, consolidation, ease of access, out of the box reporting—no one did all of those things as well as NetSuite,” Homet said.
NetSuite didn’t just address the company’s accounting issues, it was able to solve its billing problems as well. NetSuite’s unified billing framework supports transaction, subscription, usage-based and hybrid models, all while managing revenue accurately and in accordance with the latest revenue recognition standards. While importing and setting up the roughly 200 subscription accounts Datavalet was managing took some work, once it was in NetSuite the results were impressive.
“As soon as July hit we were amazed how fast we were done with our billing,” Homet said. “By July 10 we were done and everyone was wondering, ‘what do we do now?’”
Indeed, the entire implementation took just four months using the SuiteSuccess methodology and NetSuite Professional Services, and it now has a unified platform for managing financials, its US subsidiary thanks to multi-subsidiary management with NetSuite OneWorld, and its complex billing scenarios with SuiteBilling. Billing has been simplified significantly.
“SuiteSuccess made a huge difference,” Homet said. “It’s their team, their product, that’s what they do day in and day out. To have a team dedicated to SaaS, made a huge difference to us. We really dealt with people who know what they’re doing.”
Datavalet was so pleased with the initial implementation, it quickly decided to add NetSuite Professional Services Automation (PSA) software for managing professional services. Datavalet had tried using a highly customized version of SugarCRM to manage its deployment department, but eventually gave that up and tracked resources manually.
“PSA will allow us to better integrate the deployment department into accounting,” Homet said. “It’s going to help us calculate cost of goods sold, deployments and internal labor costs. It’s also going to be easier to manage when we deploy a new customer with hundreds of locations.”
Datavalet is looking to double its annual growth of around 15 to 20%, as it expands into the US with a new partnership with AT&T. NetSuite OneWorld will allow it to continue to add and easily consolidated subsidiaries.
Learn more about NetSuite SuiteBilling.
by NetSuite filed under
What’s new for SQL Server 2019 Analysis Services CTP 2.3
We find great pleasure in announcing the public CTP 2.3 of SQL Server 2019 Analysis Services. New features detailed here are planned to ship later in Power BI Premium and Azure Analysis Services.
Calculation groups
Here is a question for seasoned BI professionals: what is the most powerful feature of SSAS multidimensional? Many would say the ability to define calculated members, typically using scoped cell assignments. Calculated members in multidimensional enable complex calculations by reusing calculation logic. Unfortunately, Analysis Services tabular doesn’t have equivalent functionality. Correction: it does now!!!
Calculation groups address the issue of proliferation of measures in complex BI models often caused by common calculations like time-intelligence. Enterprise models are reused throughout large organizations, so they grow in scale and complexity. It is not uncommon for Analysis Services models to have hundreds of base measures. Each base measure often requires the same time-intelligence analysis. For example, Sales and Order Count may require:
- Sales MTD, Sales QTD, Sales YTD, Sales PY, Sales YOY%, …
- Orders MTD, Orders QTD, Orders YTD, Orders PY, Orders YOY%, …
As you can see, this can easily explode the number of measures. If a model has 100 base measures and each requires 10 time-intelligence representations, the model ends up with 1,000 measures in total (100*10). This creates the following problems.
- The user experience is overwhelming because must sift through so many measures
- DAX is difficult to maintain
- Model metadata is bloated
Calculation groups address these issues. They are presented to end-users as a table with a single column. Each value in the column represents a reusable calculation that can be applied to any of the measures where it makes sense. The reusable calculations are called calculation items.
By reducing the number of measures, calculation groups present an uncluttered user interface to end users. They are an elegant way to manage DAX business logic. Users simply select calculation groups in the field list to view the calculations in Power BI visuals. There is no need for the end user or modeler to create separate measures.
Time-intelligence example
Consider the following calculation group example.
| Table | Time Intelligence |
| Column | Time Calculation |
| Precedence | 20 |
| Calculation Item | Expression |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Here is a DAX query and output. The output shows the calculations applied. For example, QTD for March 2012 is the sum of January, February and March 2012.
EVALUATE
CALCULATETABLE (
SUMMARIZECOLUMNS (
DimDate[CalendarYear],
DimDate[EnglishMonthName],
"Current", CALCULATE ( [InternetTotalSales], 'Time Intelligence'[Time Calculation] = "Current" ),
"QTD", CALCULATE ( [InternetTotalSales], 'Time Intelligence'[Time Calculation] = "QTD" ),
"YTD", CALCULATE ( [InternetTotalSales], 'Time Intelligence'[Time Calculation] = "YTD" ),
"PY", CALCULATE ( [InternetTotalSales], 'Time Intelligence'[Time Calculation] = "PY" ),
"PY QTD", CALCULATE ( [InternetTotalSales], 'Time Intelligence'[Time Calculation] = "PY QTD" ),
"PY YTD", CALCULATE ( [InternetTotalSales], 'Time Intelligence'[Time Calculation] = "PY YTD" )
),
DimDate[CalendarYear] IN { 2012, 2013 }
)
Sideways recursion
Some of the calculation items refer to other ones in the same calculation group. This is called “sideways recursion”. For example, YOY% (shown below for easy reference) refers to 2 other calculation items, but they are evaluated separately using different calculate statements. Other types of recursion are not supported (see below).
DIVIDE(
CALCULATE(
SELECTEDMEASURE(),
'Time Intelligence'[Time Calculation]="YOY"
),
CALCULATE(
SELECTEDMEASURE(),
'Time Intelligence'[Time Calculation]="PY"
),
)
Single calculation item in filter context
Here is the definition of PY YTD:
CALCULATE(
SELECTEDMEASURE(),
SAMEPERIODLASTYEAR(DimDate[Date]),
'Time Intelligence'[Time Calculation] = "YTD"
)
The YTD argument to the CALCULATE() function overrides the filter context to reuse the logic already defined in the YTD calculation item. It is not possible to apply both PY and YTD in a single evaluation. Calculation groups are only applied if a single calculation item from the calculation group is in filter context.
This is illustrated by the following query and output.
EVALUATE
CALCULATETABLE (
SUMMARIZECOLUMNS (
DimDate[CalendarYear],
DimDate[EnglishMonthName],
//No time intelligence applied: all calc items in filter context:
"InternetTotalSales", [InternetTotalSales],
//No time intelligence applied: 2 calc items in filter context:
"PY || YTD", CALCULATE ( [InternetTotalSales],
'Time Intelligence'[Time Calculation] = "PY" || 'Time Intelligence'[Time Calculation] = "YTD"
),
//YTD applied: exactly 1 calc item in filter context:
"YTD", CALCULATE ( [InternetTotalSales], 'Time Intelligence'[Time Calculation] = "YTD" )
),
DimDate[CalendarYear] = 2012
)
A calculation group should be designed so that each calculation item within it presented to the end user only makes sense to be applied one at a time. If there is a business requirement to allow the end user to apply more than one calculation item at a time, multiple calculation groups should be used with different precedence.
Precedence
In the same model as the time-intelligence example above, the following calculation group also exists. It contains average calculations that are independent of traditional time intelligence in that they don’t change the date filter context; they just apply average calculations within it.
In this example, a daily average calculation is defined. It is common in oil-and-gas applications to use calculations such as “barrels of oil per day”. Other common business examples include “store sales average” in the retail industry.
Whilst such calculations are calculated independently of time-intelligence calculations, there may well be a requirement to combine them. For example, the end-user might want to see “YTD barrels of oil per day” to view the daily-oil rate from the beginning of the year to the current date. In this scenario, precedence should be set for calculation items.
| Table | Averages |
| Column | Average Calculation |
| Precedence | 10 |
| Calculation Item | Expression |
|
|
|
|
Here is a DAX query and output.
EVALUATE
CALCULATETABLE (
SUMMARIZECOLUMNS (
DimDate[CalendarYear],
DimDate[EnglishMonthName],
"InternetTotalSales", CALCULATE (
[InternetTotalSales],
'Time Intelligence'[Time Calculation] = "Current",
'Averages'[Average Calculation] = "No Average"
),
"YTD", CALCULATE (
[InternetTotalSales],
'Time Intelligence'[Time Calculation] = "YTD",
'Averages'[Average Calculation] = "No Average"
),
"Daily Average", CALCULATE (
[InternetTotalSales],
'Time Intelligence'[Time Calculation] = "Current",
'Averages'[Average Calculation] = "Daily Average"
),
"YTD Daily Average", CALCULATE (
[InternetTotalSales],
'Time Intelligence'[Time Calculation] = "YTD",
'Averages'[Average Calculation] = "Daily Average"
)
),
DimDate[CalendarYear] = 2012
)
The following table shows how the March 2012 values are calculated.
| Column name | Calculation |
| YTD | Sum of InternetTotalSales for Jan, Feb, Mar 2012= 495,364 + 506,994 + 373,483 |
| Daily Average | InternetTotalSales for Mar 2012 divided by # of days in March= 373,483 / 31 |
| YTD Daily Average | YTD for Mar 2012 divided by # of days in Jan, Feb and Mar= 1,375,841 / (31 + 29 + 31) |
For easy reference, here is the definition of the YTD calculation item. It is applied with Precedence of 20.
CALCULATE(SELECTEDMEASURE(), DATESYTD(DimDate[Date]))
Here is Daily Average. It is applied with Precedence of 10.
DIVIDE(SELECTEDMEASURE(), COUNTROWS(DimDate))
Since the precedence of the Time Intelligence calculation group is higher than the Averages one, it is applied as broadly as possible. The YTD Daily Average calculation applies YTD to both the numerator and the denominator (count of days) of the daily average calculation.
This is equivalent to this calculation:
CALCULATE(DIVIDE(SELECTEDMEASURE(), COUNTROWS(DimDate)), DATESYTD(DimDate[Date]))
Not this one:
DIVIDE(CALCULATE(SELECTEDMEASURE(), DATESYTD(DimDate[Date])), COUNTROWS(DimDate))
New DAX functions
The following new DAX functions have been introduced to work with calculation groups.
| Function name | Description |
|
Returns a reference to the measure currently in context. |
|
Returns a string containing the name of the measure currently in context. |
|
Returns a Boolean indicating whether the measure currently in context is one of those specified as an argument. |
SELECTEMEASURENAME() or ISSELECTEDMEASURE() can be used to conditionally apply calculation items depending on the measure in context. For example, it probably doesn’t make sense to calculate the daily average of a ratio measure.
With ISSELECTEDMEASURE():
IF (
ISSELECTEDMEASURE ( [Expense Ratio 1], [Expense Ratio 2] ),
SELECTEDMEASURE (),
DIVIDE ( SELECTEDMEASURE (), COUNTROWS ( DimDate ) )
)
ISSELECTEDMEASURE() has the advantage of working with formula fix up, so measure-name changes are reflected automatically.
Power BI implicit measures
Calculation groups work with query scope measures, but not inline DAX calculations. This is shown by the following query.
DEFINE
MEASURE FactInternetSales[QueryScope] = SUM ( FactInternetSales[SalesAmount] )
EVALUATE
CALCULATETABLE (
SUMMARIZECOLUMNS (
DimDate[CalendarYear],
DimDate[EnglishMonthName],
//YTD applied successfully to model measure:
"Model Measure", CALCULATE (
[InternetTotalSales],
'Time Intelligence'[Time Calculation] = "YTD"
),
//YTD applied successfully to query scope measure:
"Query Scope", CALCULATE (
[QueryScope],
'Time Intelligence'[Time Calculation] = "YTD"
),
//YTD not applied to inline calculation:
"Inline", CALCULATE (
SUM ( FactInternetSales[SalesAmount] ),
'Time Intelligence'[Time Calculation] = "YTD"
)
),
DimDate[CalendarYear] = 2012
)
Power BI implicit measures are created when the end user drags columns onto visuals to view aggregated values without creating an explicit measure. At time of writing, Power BI generates DAX for implicit measures written as inline DAX calculations. This means implicit measures don’t work with calculation groups. To reserve the right to introduce this at a later date, a new model property visible in TOM has been introduced called DiscourageImplicitMeasures. In the current version, it must be set to true to create calculation groups. When set to true, Power BI Desktop in Live Connect mode disables creation of implicit measures.
DMV support
The following Dynamic Management Views (DMV) have been introduced for calculation groups.
- TMSCHEMA_CALCULATION_GROUPS
- TMSCHEMA_CALCULATION_ITEMS
OLS
Object-level security (OLS) defined on calculation group tables is not supported in the current release. They can be defined on other tables in the same model. If a calculation item refers to an OLS-secured object, it will return a generic error on evaluation. This is the planned behavior for SSAS 2019.
Planned for a forthcoming CTP
We plan to introduce the following items in a forthcoming SQL Server 2019 CTP.
- MDX query support with calculation groups.
- RLS is not supported in CTP 2.3. The planned behavior for SSAS 2019 is that you will be able to define RLS on tables in the same model, but not on calculation groups themselves (directly or indirectly).
- Dynamic format strings. Calculation groups increase the need for dynamic format strings. For example, the YOY% calculation item needs to be displayed as a percentage, while the others should probably inherit the data type of the measure currently in context. We plan to introduce dynamic format strings in an upcoming SQL Server 2019 CTP.
- ALLSELECTED DAX function support with calculation groups.
- Detail rows support with calculation groups.
Limitations of CTP 2.3
CTP 2.3 of SSAS is still an early build of SSAS 2019. It being released for testing and feedback purposes only, and should not be used by customers in production environments. This applies to models with or without calculation groups.
New 1470 Compatibility Level
To use the new features, existing models must be upgraded to the 1470 compatibility level. 1470 models cannot be deployed to SQL Server 2017 or earlier or downgraded to lower compatibility levels.
Differences between calculation groups in tabular and calculated members in multidimensional
Calculated members in multidimensional are a little more flexible and enable a few scenarios beyond calculation groups, but they come at the cost of added complexity. We feel calculation groups in tabular provide a great deal of the benefits, with significantly less complexity.
Single calculation-item column
Calculation groups can only have a single calculation-item column, whereas multidimensional allows multiple hierarchies with calculated members in a single utility dimension.
A DAX filter on a column value implicitly filters the other columns in the same table to the values of that row. Without introducing new semantics and complexity, multiple calculation-item columns in a single table would filter each other implicitly, so are disallowed. If you have a requirement to apply multiple calculation items at a time, use separate calculation groups and the Precedence property shown above.
Recursion safeguards not required
MDX supports recursion although there are known performance limitations. Quite often the same query results can be achieved using MDX set-based calculations instead of recursion.
The right-hand side of MDX-script cell assignments to calculated members created by the Business Intelligence Wizard for multidimensional include a reference to the real member from the attribute hierarchy. This is required to safeguard against recursion.
Since DAX doesn’t support recursion, so we don’t need to worry about this for calculation groups. The complexity bar is kept lower. If we ever decide to support recursive DAX in the future, we could perhaps introduce an advanced property to indicate that a DAX object is enabled for recursion, and only then require such safeguards to be in place.
Calculation items cannot be created on other column types
Multidimensional allows creation of calculated members on attribute hierarchies that are not part of utility dimensions. For example, a Northwest Region member can be added to the State hierarchy to aggregate Washington, Oregon and Idaho. This is useful for custom-grouping scenarios but can increase the likelihood of solve-order issues.
Calculation items cannot be added to other column types. This keeps semantic definitions simpler. As we enhance calculation groups in the future – for example, if we introduce query-scoped calculation groups – we will take care to learn from the solve-order lessons of the past and strive for consistent behaviors.
Calculation groups are currently engine-only features. SSDT support will come before SQL Server 2019 general availability. In the meantime, you can use the fantastic open-source community tool Tabular Editor to author calculation groups. Alternatively, you can use SSAS programming and scripting interfaces such as TOM and TMSL.
Pace of delivery
We think you will agree the AS engine team has been on a tear lately. This is the same team that recently delivered, or is currently working on, the following breakthrough features for Power BI.
- Arguably the biggest scalability feature in the history of the AS engine: aggregations
- Policy-based incremental refresh
- Opening the XMLA endpoint to bring AS to Power BI
Calculation groups is yet another monumental feature delivered in a relatively short period of time. It demonstrates Microsoft’s continued commitment to enterprise BI customers.
Download Now
To try SQL Server 2019 CTP 2.3, find download instructions on the SQL Server 2019 web page. Enjoy!
What’s new for SQL Server 2019 Analysis Services CTP 2.4
We are excited to announce the public CTP 2.4 of SQL Server 2019 Analysis Services. This public preview includes the following enhancements for Analysis Services tabular models.
- Many-to-many relationships
- Memory settings for resource governance
Many-to-many relationships
Many-to-many (M2M) relationships in CTP 2.4 are based on M2M relationships in Power BI described here. M2M relationships in CTP 2.4 do not work with composite models. They allow relationships between tables where both columns are non-unique. A relationship can be defined between a dimension and fact table at a granularity higher than the key column of the dimension. This avoids having to normalize dimension tables and can improve the user experience because the resulting model has a smaller number of tables with logically grouped columns. For example, if Budget is defined at the Product Category level, it is not necessary to normalize the Product dimension into separate tables; one at the granularity of Product and the other at the granularity of Product Category.
Tooling
Many-to-many relationships are currently engine-only features. SSDT support will come before SQL Server 2019 general availability. In the meantime, you can use the fantastic open-source community tool Tabular Editor to create many-to-many relationships. Alternatively, you can use SSAS programming and scripting interfaces such as TOM and TMSL.
New 1470 Compatibility Level
To use many-to-many relationships, existing models must be upgraded to the 1470 compatibility level. 1470 models cannot be deployed to SQL Server 2017 or earlier or downgraded to lower compatibility levels.
Memory settings for resource governance
The memory settings described here are already available in Azure Analysis Services. With CTP 2.4, they are now also supported by SQL Server 2019 Analysis Services.
QueryMemoryLimit
The Memory\QueryMemoryLimit property can be used to limit memory spools built by DAX queries submitted to the model.
Changing this property can be useful in controlling expensive queries that result in significant materialization. If the spooled memory for a query hits the limit, the query is cancelled and an error is returned, reducing the impact on other concurrent users of the system. Currently, MDX queries are not affected. It does not account for other general memory allocations used by the query.
The settable value of 1 to 100 is a percentage. Above that, it’s in bytes. The default value of 0 means not specified and no limit is applied.
You can set this property by using the latest version of SQL Server Management Studio (SSMS), in the Server Properties dialog box. See the Server Memory Properties article for more information.
DbpropMsmdRequestMemoryLimit
The DbpropMsmdRequestMemoryLimit XMLA property can be used to override the Memory\QueryMemoryLimit server property value for a connection. The unit of measure is in kilobytes. See the Connection String Properties article for more information.
RowsetSerializationLimit
The OLAP\Query\RowsetSerializationLimit server property limits the number of rows returned in a rowset to clients.
This property, set in the Server Properties dialog box in the latest version of SSMS, applies to both DAX and MDX. It can be used to protect server resources from extensive data export usage. Queries submitted to the server that would exceed the limit are cancelled and an error is returned. The default value is -1, meaning no limit is applied.
Download Now
To get started with SQL Server 2019 CTP 2.4, find download instructions on the SQL Server 2019 web page. Enjoy!
Future Analysis Services posts on Power BI blog
The Analysis Services Team Blog has served as the medium for many historic announcements and updates in Microsoft BI over the past 10 years. Well, Microsoft is moving off MSDN and TechNet blogs, so this will be the last post on this blog site. The site, including all historical posts, will remain online as a read-only archive.
‘So where will new Analysis Services blog posts be published?’ I hear you ask. We have made it clear that Power BI will be a one-stop shop for both enterprise and self-service BI on a single, all-inclusive platform. Power BI will be a superset of Analysis Services, so there is no better place to announce future Analysis Services features than, you guessed it, on the Power BI blog. The Analysis Services engine is at the foundation of Power BI and powers its datasets, so this just feels right.
Please stay tuned to the new Analysis Services category on the Power BI blog for future Azure Analysis Services and SQL Server Analysis Services announcements:
https://powerbi.microsoft.com/blog/category/analysis-services/