Power BI – Best Practices for Metadata Modeling using Composite Models

/by

Microsoft recently released one of the most sought after features for Power BI. As of the December 2020 update, there is a new preview feature that allows you to build a partitioned metadata model in Power BI. Formally called “DirectQuery for Power BI datasets and Analysis Services”, this is essentially just an expansion of the Composite Model feature introduced in 2018. Previously, Composite Models did not support live connections to published Power BI datasets and Azure Analysis Services, but that is no longer the case with the latest enhancement.

So what is “DirectQuery for Power BI datasets and Analysis Services”? With this feature, Power BI users can now build an enterprise semantic Power BI metadata model for sharing (and enhancement!) across their organization. Starting with this “master” model, individual departments can quickly and flexibly extend and enhance the model as business needs dictate.  Updates made to the underlying master model are seamlessly inherited without impacting business continuity.

For example, IT creates a Product Master or Customer Master model in PowerBI.com as a dataset. Later, individual departments can then import and enrich the enterprise model to meet their own needs. If IT decides to update the Master model, all the dependent data set models will automatically inherit the updates.

The release of this functionality greatly enhances an organization’s ability to support and drive governance and master data efforts by allowing the promotion of a “single source of the truth” through Power BI.

So what are the details of this new feature? How does it really work?

  • Begin by publishing your enterprise Power BI dataset to powerbi.com.  An example might be a Customer Master dataset. 
  • Next, in Power BI desktop, enable the new preview feature.
  • You can now connect live to the existing dataset publish in Power BI Services.
  • Just like any other data source in Power BI desktop, you can extend the model by connecting to more data sources (e.g. an Excel file or a SQL database) and combine them into one semantic model, while still maintaining the relationship to the original enterprise model. If the enterprise model’s data is updated, then the newly created composite model will also see the new data.
  • A composite model does require you to have a local model. By default, if your PBIX model only has a live connection to your source data (like AAS) then you will not have a local metadata model in your PBIX file. If you start from a live connection to a data set then you’ll need to convert to a DirectQuery connection before you can import additional datasets. This will happen automatically once you import another data source. A local model is needed to “extend” the metadata model to include any new data source like Excel or MS SQL.
  • You can now extend and enhance your model by linking together additional data sources.  Important to understand is that extending beyond a “chain” length of 3 is not currently supported. Effectively this means that one cannot extend a data model that has already been extended. For example, if you publish an extended model to Power BI Service then another user can not further extend the model with additional data.

Additional suggestions to be aware of:

  1. If row level security is defined in the master model, it will be available in the extended model but it will not automatically apply to any newly imported data sources. You’ll need to define row level security on any data sources that you import into your composite model.
  2. Display folders, KPIs, date tables, row level security, and translations will not be imported from the source in this preview release. You can still create display folders in the local model. This maybe resolved before General Availability.
  3. You may see some unexpected behavior when using a date hierarchy. To resolve this issue, use a date column instead. After adding a date hierarchy to a visual, you can switch to a date column by clicking on the down arrow in the field name, and then clicking on the name of that field instead of using Date Hierarchy:
  4. Using on premise SQL Server Analysis Services (SSAS) as a DirectQuery source is not currently supported but Azure Analysis Services (AAS) is supported.

 “DirectQuery for Power BI datasets and Analysis Services” preview feature is a major step toward creating centralized and standardized metadata for any organization. There is no need to waste time remodeling and maintaining master data for each and every Power BI model. You can now simply extend your semantic models by leveraging a standard set of master metadata that is maintained in a central Power BI dataset. You can think of this new preview feature as Composite Models 2.0.

Getting Started with AI: Four Things to Consider

/by

So you’re thinking that 2021 is the year to infuse Artificial Intelligence/Machine Learning (AI/ML) into your business. You’ve read about the difference it’s making in other organizations. You want to beat — or keep pace with — your competitors. But where should you begin? 

Should you license AI/ML software? How do you find the right business problem to solve? And if you’re like most organizations, your data is imperfect. Should you focus there first? 

Ironside can help. We’re a data and analytics consulting firm with a track record of helping companies get started with AI.

Let’s start with four things we think every organization should consider on their AI journey. They’re not the only four things you need to know, but we know your time is valuable, so let’s start here:

  1. Develop an AI Use Case Catalog – One of the first places you’ll start is to develop a list  of possible business problems, opportunities or challenges that AI might improve. We assist our customers in building that list by talking to executives and functional area leaders and understanding the organization’s strategic goals, how they are measured and then considering challenges/pain and what information is missing that would improve decision making. The catalog of use cases should be enhanced with information from a thorough data analysis. Is there the data to support the use case? Is there enough of it? What’s the data quality? Can you forecast improvement of an important metric? What’s the return on investment? 
  2. Involve a variety of stakeholders. Executive sponsorship in some form is critical in funding and executing an AI project. But building a culture of AI across an organization starts by involving as many stakeholders as possible across functional areas. Even if the use cases surfaced by some stakeholders are not immediately pursued, people want to be included and to have a voice. Broader involvement will avoid roadblocks and seed a culture of AI. Organizational success will grow over time. 
  3. Start small – Rank the use case catalog and find one or two to test. Identify the relevant business sponsor and data and prepare a limited set of data, or features. Build a simple machine learning algorithm to see if there are results that show that AI could improve a desired outcome. If not, move on to the next use case in the catalog (see, that’s why we need a catalog). If the early results are promising, move on to building a more advanced machine learning model.  
  4. Limit your investment – For as low a cost as possible, get a model deployed and start using it in the business to begin to get the benefit. You’ll inevitably iterate on that model but expediting that process and limiting the investment — and the risk — is the goal. Now here’s where we answer the questions about hiring a data scientist or buying software. 

Sometimes the answer is yes but for many organizations the answer is “no.” They’re just not sophisticated enough. And big costly failures could sour your organization on pursuing AI and set you back years

Ascend AI 

So what should you do? One option to get started is Ascend AI, a data science as-a-service solution that Ironside developed. Ascend AI lowers the risk out of diving into AI on your own. It is  underpinned by a custom configured and scripted cloud-based architecture as well as our highly skilled data scientists. 

We bring the data scientists and engineers and the technology. You provide the data and the business problems. 

We start with your leading use cases or help you develop them in a use case catalog. Then we perform rapid viability assessments on the leading use cases selected and if signs are good we would then build out full machine learning algorithms. Finally, we could deploy and host and manage the algorithms. At any point, depending on customer preference and maturity, we would hand the IP back to our customers and help them develop AI competency in house. We’re not a black box. 

Of course there’s more to getting started with AI than these four points and data science as a service might not always be the answer. The thing to remember is that AI should be consumed in bite sized-chunks and is attainable to even the most technologically immature organizations.

Natural Language Query – How Today’s Top Vendors Use It

/by

What is Natural Language Query?

Natural Language Query is the ability to use natural language expressions to discover and understand data and accelerates the process of finding answers that data can provide. Another way to think about it would be a translation mechanism that helps bridge the gap between technical and non-technical users who may not understand which database has the data, which field to use or how to create calculations to answer their questions.

An example might be “How many customers made a purchase this month?” And the idea is that the tool would respond and give you answers and visualizations that answer that question or at least help you on the path to finding it.

From an industry perspective, in 2017, Gartner predicted that by 2020 half of all analytics queries will be generated using natural language processing. As of 2021, we have seen all of the leading vendors in the analytics space adding functionality like this and many have had this functionality for 2+ years.

Tableau – Ask Data

Tableau released Ask Data in version 2019.1 (February 2019) and has continued to enhance and improve its functionality. To use Ask Data, simply navigate to the desired data source in Tableau Online or Tableau Server, type in a question and Tableau will answer that question in the form of an automatically generated visualization. From there, you can customize the visualization, add additional filters and save your analysis as its own report. Ask Data will also recommend questions based on your data source and offer suggestions to refine your question as you’re typing. 

Another feature of Ask Data is the ability to create synonyms for fields so similar terms can be mapped to an existing field. If your business users are used to referring to customers as clients, you can add the word client as a synonym for the customer field in order for Ask Data to interpret the word client. For data source owners and Tableau administrators, Ask Data provides a dashboard that displays the most popular queries and fields, number of visualization results that users clicked, etc. to understand habits and behaviors of those using Ask Data with a given data source.

Power BI – Q&A

Power BI’s natural language query tool, Q&A, was released in October 2019 and is available in both Power BI Service and Desktop. In Power BI Service, Q&A is available in the upper-left corner of your dashboard. Similar to Ask Data, you can type in a question and Power BI will pick the best visualization to display your answer and if you’re the owner of the given dashboard, you can pin the visualization to your dashboard. It’s important to note that Q&A will only query datasets that have a tile on the dashboard you’re using so if you remove all the tiles from one dataset, Q&A will no longer have access to that dataset. To use Q&A while editing a report in Desktop or Power BI Service, select “Ask a Question” from the toolbar and type your question in the text box that appears.

Teach Q&A is a feature that allows you to train Q&A to understand words it doesn’t recognize. For example, someone asks “What are the sales by location?” but there is no field called “location” in the dataset. Using Teach Q&A, you can indicate that location refers to the region field and moving forward, Q&A will recognize that location means region.

Cognos Analytics – AI Assistant

AI Assistant was released in version 11.1 in September 2018 and can be used to explore data in Dashboards and Stories. AI Assistant is available by clicking the text bubble icon in the Navigation panel. Unlike the tools mentioned above, the AI Assistant interface appears more like a chat window where your conversation history is saved. You ask a question about the data, receive an answer, then can continue asking additional questions and scroll back in the history to view the whole “conversation”.  After asking a question, the AI Assistant will respond with an auto-generated visualization, that you can customize if desired, and then drag onto your dashboard canvas. 

Amazon QuickSight – Q

Amazon QuickSight, the newest of the tools discussed, released a preview of their natural language query tool, Q, in December 2020. Like the tools mentioned above, Q is a free-form text box found at the top of your dashboard where you can specify the data source you want to explore and ask your question. If Q does not sufficiently answer your question, you can provide feedback to correct the answer and that feedback is sent to the BI team to improve or enhance the data.

Overall

Tableau – Ask DataPower BI – Q&ACognos Analytics – AI AssistantAmazon QuickSight – Q
Release DateFeb 2019Oct 2019Sep 2018Dec 2020
Suggests Questions✓✓✓
Create Synonyms✓✓✓
Auto-Generates Visualizations✓✓✓✓
NLQ User Log✓✓

Overall, these tools are all similar in how they are used/function and all have the same goal – to make it easier and faster for business users to get answers from their data.

This blog post originated from our Take30 session around Natural Language Query, presented by Ursula Woodruff-Harris, Scott Misage, & John Fehlner.