Standing room today for my presentation on the same subject at SQL Saturday Atlanta. I uploaded the slides to the event site and to my LinkedIn profile page. This week was packed with training events. One more event to organize for the Atlanta Power BI Group on Monday…
MS BI fans, join us for the next Atlanta MS BI and Power BI Group meeting on Monday, May 21st at 6:30 PM. Paco González will take us into a deep dive into Microsoft Cognitive Services and how to integrate them with Power BI. SolidQ will sponsor the meeting. And your humble correspondent will introduce you to Power BI Premium incremental refresh. For more details, visit our group page and don’t forget to RSVP (use the RSVP survey on the main page) if you’re planning to attend.
Atlanta BI Group May 21st Meeting Reminder |
Please RSVP to help us plan food by 2 PM on the day of the meeting by latest:
Please note that the voting system tracks votes by cookies. If you don’t see the option to submit the survey, clear your browser cookies, or right-click the Internet Explorer icon, and then click Start InPrivate Browsing. Free covered parking is available at South or North Terraces. Or, use the free open non-ticket parking next to North Terraces (coming north on Ashford Dunwoody, turn left on Perimeter Center Terrace and them immediately right).
| Presentation: | Artificial Intelligence, Cognitive Services, and Power BI |
| Level: Intermediate | |
| Date: | May 21, 2018 |
| Time | 6:30 – 8:30 PM ET |
| Place: | South Terraces Building (Auditorium Room) 115 Perimeter Center Place Atlanta, GA 30346 |
| Overview: | Microsoft Cognitive services is a series of services based on Microsoft Research using Deep Learning techniques, ready for you to enrich your unstructured data. Microsoft Services includes: Vision (Computer Vision, Emotion, Face, Video), Speech/Language (Speaker Recognition, Spell, Sentiment, Language, Key Phrases, Topic detection, Linguistic Analysis), Knowledge, and Search. These services are consumed by using the Cognitive Services APIs. Power BI enables you to consume the APIs to enrich your unstructured data inside your Power BI solutions. During this session, we will cover how to consume the API using Power Query to then visualize the results with Power BI visuals. We will also consume the Cognitive services API by using a R script inside Power BI. This session is structured with a B2C Survey case study solution with a real time Dashboard using a streaming dataset. |
| Speaker: | SolidQ North America’s CEO, Paco González specializes in helping organizations become “data driven” from a strategic and technical perspective. He is a speaker at small and large conferences such as PASS Summit, Ignite, DevWeek London, or PAW. Paco focuses in Business Analytics and Artificial Intelligence. Paco has published several books and whitepapers. A Microsoft Certified Trainer and Microsoft Certified Professional. Paco is based in Atlanta. |
| Sponsor: | The tools and technologies to manage information will constantly evolve, but the need for accurate and actionable information is timeless. SolidQ delivers services for Microsoft platforms that help you architect, integrate and optimize your use of data. The result? Our clients think bigger and move faster because we help them build the capacity and skills to interact with data in creative, collaborative ways that deliver new insights to the business. At SolidQ we measure our success by your satisfaction – and we guarantee it. |
BI and data integration projects often benefit from an operational data source (ODS), whose benefits and design I discussed in my “Designing an Operational Data Store (ODS)” newsletter. Think of the Microsoft Common Data Service for Apps (CDS for Apps) as a cloud ODS by Business and for Business. Officially introduced in 2016 and running on Azure SQL Database, CDS for Apps is now the entity and data model behind Dynamics 365. This is where Dynamics 365 stores its data. Microsoft is readying a preview of another CDS flavor (CDS for Analytics) in June 2018. Think of CDS for Apps as the OLTP version of the ODS and is designed for transactions (this is why it’s layered on top of SQL Server), while CDS for Analytics is oriented towards OLAP (this is why is layered on top of Azure Storage).
Both CDS types support standardized entities, whose definitions are documented in the GitHub repository of the Common Data Model. Currently, the schema of these entities is designed and controlled by Microsoft and it’s limited to Dynamics entities, such as Account, Opportunity, and so on. However, Microsoft hopes that other vendors will provide solutions and extend the CDS schema. Of course, because CDS is your database, you can extend it with your own custom entities. The rest of the document focuses on CDS for Apps only.
There is a lot to like about CDS for Apps. Let’s start with pricing. Other vendors, such as Oracle and Teradata, have similar visions and products but their offerings are very expensive. The CDS for Apps pricing is included in the PowerApps licensing model because PowerApps is the primary client for creating CDS for Apps-centered solutions. Using CDS outside selected Dynamics 365 plans (that include it already) will cost you at least $7 per user and per month.
CDS for Apps is more than just a data repository. It’s a business application platform with a collection of data, business rules, processes, plugins and more. In this regard, it resembles SQL Server Master Data Services (MDS). The modeler can:
Besides the original PowerApps canvas apps (like InfoPath forms), CDS for Apps also opens the possibility to create model-driven PowerApps applications (require PowerApps P2 plan). Model-driven apps are somewhat like creating Access data forms but more versatile. Because PowerApps knows CDS for Apps, you can create the app bottom-up, i.e. start with CDS for Apps and then generate the app based on the actual schema and data. For example, you can use PowerApps to build a model-driven app for implementing the workflow for approving a certain process. Model-driven apps are a new style of a PowerApps application that makes it easy to build entity forms, entity views, and workflows.
How do you get data into CDS for Apps custom entities? Your PowerApps app can write to it. Or, you can create and schedule a project that uses Power Query (yep, the same one as in Power BI) to load data from somewhere into CDS for Apps.
How do you get data out from CDS for App, such as to import data from some entities into a Power BI model? Microsoft has released a preview build of the Common Data Service for Apps connector for Power BI. However, this connector is even slower that the Dynamics connector. It uses the OData v4 Web API. Based on my limited tests, it took the connector about a minute to download 40,000 rows from Dynamics, clocking 10% slower than the Dynamics connector. To make things worse, the connector doesn’t support query folding, so Power BI must download the entire dataset before Power Query applies filters. Because the connector doesn’t support also REST filter and select predicates, so you can’t filter data or select a subset of columns at the source. Microsoft is actively working on improving the connector performance and it might get better in time.
Continuing down the list of limitations, CDS for Apps doesn’t support change tracking (to capture changes to a given row) and incremental loads, such as to load or refresh only the data that has changed yesterday or previous month. These are all essential features that could make ODS even more valuable.
For years people were complaining that after migrating from the on-premises Dynamics to the cloud, they lost the ability to connect to its database directly and they had to rely on the REST APIs (slow) or Data Export Service to export the data to an SQL Server Database (fast but requires additional effort and budget). Unfortunately, although CDS for Apps stores data in Azure SQL Database, Microsoft doesn’t expose its database directly to get data out fast and bypass the REST endpoint. When I raised this issue to Microsoft I got feedback that CDS for Apps is a business platform and there are layers on top of data to handle security, rules, calculations, and so on. However, the argument that CDS for Apps is more than just a database is nonsensical to me. Try to explain to a customer that cakes have layers and CDS for Apps has layers, and therefore getting something out of it is slow. As I mentioned, the “layered nature” of the CDS is conceptually like MDS. In fact, I see a lot of overlap. MDS also supports rules, security, etc. but it doesn’t force me to go through the web service interface if all I need is the raw data. Hence, my wish to support direct connectivity to the Azure SQL Database endpoint of CDS for Apps.
The success of Common Data Services for Apps will depend largely on adoption and contributions by Microsoft partners. Although it lacks typical ODS features and fast connectivity, CDS for Apps gains in “business platform” features. It will be interesting to see how much traction it will gain with business users as a staging area given that CDS for Apps is designed from ground up for business users.
Thank you to everyone attending the Power BI workshop by Prologika yesterday. We had another great turnout with more than 50 people from 30+ companies attending the event at the Microsoft Technology Center in Alpharetta! Kudos to Eric Flamm and Michael Folarinde for the great job proctoring the event. Here is some feedback that the attendees shared in the post-event survey:
And my next week is entirely booked with more Power BI training. It’s great to witness and participate in the great momentum surrounding Power BI!
In my “Why Business Like Yours Choose Power BI Over Sisense” blog, I discussed how Sisense seeks a competitive advantage by offering a user-friendly designer connected to the their ElastiCube, thus claiming that Sisense is a better choice with larger datasets because business users don’t have to use professional tools (Visual Studio) or deploy to external servers. Things have changed in Power BI since then. Microsoft increased the dataset size to 1 GB (Power BI Pro) and 10 GB (Power BI Premium). A 10 GB compressed dataset is a lot of data (probably allowing you to import over a billion rows). But fully refreshing a large dataset is no fun and it can take many hours (see my blog “Processing AAS Models Asynchronously” for some stats).
Fortunately, the May release of Power BI Desktop brings incremental refresh, which a feature that was previously only available in Analysis Services. As its name suggests, the primary goal is to reduce the time required to refresh datasets with imported data by processing only data that has changed instead of the entire dataset.
I was involved and provided feedback during the prerelease testing of Power BI incremental refresh. Without reiterating the documentation, here are some things you need to pay attention to:
You should appreciate how simple Power BI incremental refresh is to set up. BI pros know that SSAS partitions take some effort to set up. It gets more complicated when you need to set up rolling partitions and merge partitions. In Power BI, you only need to configure the refresh policy and Microsoft takes care of the rest. Behind the scenes, Microsoft introduced a new partition type that has a refresh policy and parameterized query using the RangeStart and RangeEnd parameters. When the model is refreshed, Power BI expands this partition type and creates the actual table partitions. Considering the above setup, it creates 20 yearly partitions, 1 Quarter partition (for Q1 2018), and 35 daily partitions, assuming the current date is May 5, 2018. These partitions get consolidated and merged over time. For example, once Q2 is full, all daily partitions are merged into a monthly partition, and when the quarter is complete, monthly partitions get merged into a quarterly partition, then quarters get merged into years. Power BI also takes care of removing older partitions when they fall off the range (sliding window).
As the documentation mentions, a “preview” limitation is that subsequent publishing from Power BI Desktop replaces the published refresh policy and triggers a full load on the next refresh. Remember that full load happens once the first time you initiate manual or scheduled refresh of the published dataset. Once the dataset is fully loaded, subsequent refreshes load the dataset incrementally (the last 10 days with the above configuration). Currently, there isn’t a way to reload the dataset (full refresh), such as when you discover the historical data has issues and you need to reload the history, except deleting the dataset and republishing it. I hope that Microsoft will enable access to the Analysis Services Tabular management endpoint to support such scenarios. Or, a better option might be to extend the Parameters section in the refresh page to include an option for full load so that the business user can trigger a full reload.
One important consideration to keep in mind is that incremental refresh works best with data sources that support query folding. Such data sources pass through some Power Query transformation steps, such as filtering and grouping, to the underlying data source to improve performance and avoid loading the entire dataset before filtering and grouping is applied. To determine if a specific step is “folded”, right-click the step, such as Filtered Rows, in the Applied Steps pane and observe if the “View Native Query” is enabled.
Although Microsoft doesn’t have an official list of data sources that support query folding and what steps are folded, typically relational data sources and data sources that can handle the SQL WHERE clause support query folding. Excel and text files do not. It’s important to check query folding because it the data source doesn’t support it, Power BI Desktop currently doesn’t prevent incremental refresh but it will load all the data before the filter is applied. Specifically, the query mashup (M) engine will apply the filter as it reads the rows before it gets loaded into the partitions. You might be able to mitigate this performance issue with non-foldable sources by applying the RangeStart/RangeEnd filter when the initial query is sent to the data source. For example, Dynamics Online supports a $filter clause that will work with incremental refresh:
= OData.Feed(“<endpoint url>/sales?$filter=CreatedDate ge ” & Date.ToText(RangeStart) & ” and CreatedDate lt ” & Date.ToText(RangeEnd)”)
Power BI incremental refresh is an important enhancement to the Power BI data architecture. A Power BI premium feature, incremental refresh brings the following benefits for tackling larger datasets: data loads are faster, data loads are more reliable (no long-running connections), and reduced resource consumption.
Everyone has a different opinion about what self-service BI should be. But to many people the journey starts and stops with Microsoft Excel. Excel is the self-service BI nirvana. If the user can’t see and filter the detail data, even the most sophisticated self-service BI tool cannot be trusted. And since everyone knows how to do this in Excel, why should they use other tools? To me, the best self-service BI is empowering business users to connect to an organizational semantic model, ideally with Excel as a front end. Most of my current projects use Analysis Services Tabular for implementing semantic models. Therefore, it’s so frustrating that Excel still lacks good support for Tabular. For example, it splits a Tabular table into two tables (“dimension” and “measure group”) because Excel still uses the MDX interface. To make things worse, all fields in the “dimension” table has a Text data type because it gets only the field caption and not the data type. As a result, you can’t filter and compare date and number fields from the “dimension” tables.
According to Microsoft, a better Excel-Tabular combo hasn’t yet bubbled up high based on the feedback that the Excel team gets from customers. In an attempt to get the proper attention, I just posted the “Better Support for Analysis Services Tabular” wish list on Excel User Voice. I bundled multiple items into one wish list.
Please vote if you care.
It’s about time to improve support for SSAS Tabular:
1. Auto-generate DAX.
2. Avoid splitting a table into a “measure group” and “dimension”. See how PBI Desktop handles metadata.
3. Carry over data types so that users can create reports, such as show me invoices where Due Date is greater than … Currently, all dimension table fields map to Text.
4. Many customers have expressed interest in connecting Excel tables directly to Tabular, bypassing Pivot Table, so they can use the column dropdowns and other Excel Table goodness.
5. Support descriptions as tooltips when the user hovers on a field in the Field List.
SQL Saturday Atlanta is a must-attend, full-day event for the community, by the community. This year SQL Saturday will take place on May 19th at Gwinnett Technical College – Alpharetta, 2875 Old Milton Pkwy, Atlanta, Georgia, 30009. Prologika will deliver two presentations:
According to the Top Business Intelligence Trends 2018 survey by BI-Survey.com and based on feedback from over 2,000 BI professions, the top BI trend this year is Master Data/Data Quality Management. Having consulted with a large insurance company on master data management with SQL Server 2016 for years, Neal Waterstreet has plenty of best practices to share on this topic, so join his session at 8:30 AM.
Want to know the position Master Data Management and MDS play in an organization’s overall data strategy? We’ll review the key concepts and the different roles and responsibilities to help you get your organization moving forward with MDM. We’ll then take a look at the features of SQL Server 2016 MDS that make it an excellent tool for MDM. This demo-packed presentation will cover architecture and installation, different ways of processing data, hierarchies, security and many other features that will get you up and running with MDS.
“Data discovery/visualization” and “Self-service BI” take the next top spots in the above survey. According to the 2018 Magic Quadrant for Analytics and Business Intelligence Platforms by Gartner (and I agree), Power BI is the best platform for address these needs. Join me at 2:15 PM to discuss how Power BI can augment your BI strategy.
Not sure what value Power BI brings to your BI projects? Join me to discuss 10 ways Power BI can help augment your existing or envisioned Power BI strategy. If you’re interested in the Power BI but you’re not sure how it fits within your organizational BI strategy, this event is for you. Discussion points include:
