Blog – Page 37 – Prologika

SSRS Tabular Query Designer

March 14, 2017 / No Comments »

Analysis Services Tabular has gained a lot of momentum for implementing organizational semantic models but its toolset has been lacking. SSRS developers had to rely previously on the MDX Query Designer to send MDX queries to Tabular. If DAX queries were preferred (and they often are for better performance since DAX is the native Tabular query language), developers had to type in DAX manually. And if the query would take parameters then the ugly workaround was to use … the DMX Query Designer. As Chris Finlan announced yesterday, this will all change starting with the newly released Report Builder, SSDT 17 (currently in release candidate state), and SSMS 17 (also in RC state), as they now feature a Tabular Query Designer. SSMS also includes DAX Query Editor, as Christian Wade explains here. If you're familiar with the MDX Query Designer, you already know how to use the Tabular Query Designer. When you connect to...
Presenting at Birmingham SQL Saturday 2017

March 12, 2017 / No Comments »

I'm presenting "Embed Interactive Reports in Your Apps" at Birmingham SQL Saturday 2017, on March 18th at 1 PM. I'll discuss different options to embed Power BI reports in internal and external apps. You have the app. You have the data. What if your app could put the power of analytics everywhere decisions are made and allow your customers to gain insights? Modern apps with data visualizations built-in have the power to inform decisions in context—for any user and on any device. Join me to discover how you can embed data analytics in any app and on one device powered by Power BI Embedded in the cloud and Reporting Services on premises. I'll share my experience in helping customers embed Power BI reports. Learn how to: Create compelling interactive reports Embed easily for faster time to value Deploy quickly and manage with ease
Implementing Header-Details in Power BI

March 6, 2017 / No Comments »

Lots of things to like in the just released March update of Power BI Desktop! My favorite? We can now implement header-details text reports using the updated Matrix visual. Previously, Table and Matrix visuals wouldn't allow you to select a row to cross filter other sections. Now, you can use the Matrix visual for the header section. When you click a row in the "header", other visuals get cross-filtered. In the screenshot below, the Table visual shows all customers who have purchased the selected product. Also, notice that the Slicer visual has also been upgraded to allow sliding a numeric measure.
Auditing Row Counts in SSIS

February 26, 2017 / No Comments »

It's a good practice to have a custom ETL framework that augment the SSIS capabilities. ETL frameworks come in different shapes and sizes but what I'd like to see in a framework is: Configurable parallelism – Does your ETL take hours? The chances are that your packages run sequentially. Or, disconnected flows in the master package's control flow support limited parallelism, while your server probably has much bigger pipeline. By contrast, the framework we use support configuring the degree of parallelism and automatically distributes packages to be executed on different threads. Target and actual package execution duration – You must proactively monitor when the actual package execution exceeds its target duration. See my newsletter "Is ETL (E)ating (T)hou (L)ive?" of a real-life example of what could happen if you don't do this. Plan to implement an ETL dashboard for monitoring the package execution against its target and to let the...
Atlanta MS BI Group Meeting on February 27th

February 25, 2017 / No Comments »

MS BI fans, join me for the next Atlanta MS BI and Power BI Group meeting on Monday, February 27th at 6:30 PM. Paco Gonzales from SolidQ will introduce to Azure Cognitive Services and walk us through the process to build a bot. SolidQ will sponsor the event. I'll show two existing new Power BI features: clustering and binning. Rate this meeting http://aka.ms/PUGSurvey, PUG ID: 104 Presentation: Bot Framework and Cognitive Services Level: Intermediate Date: February 27, 2017 Time 6:30 – 8:30 PM ET Place: South Terraces Building (Auditorium Room) 115 Perimeter Center Place Atlanta, GA 30346 Overview: Cognitive computing (CC) describes technology platforms that are based on the scientific disciplines of Artificial Intelligence and Signal Processing. These platforms encompass machine learning, reasoning, natural language processing, speech and vision, human-computer interaction, dialog and narrative generation and more. Join this session for an overview of the Microsoft Bot Framework and Cognitive Services....
Vote to Give Tabular SE More Memory

February 25, 2017 / 1 Comment »

I'm a big fan of Analysis Services and I'm doing a lot of work lately with Tabular. I rejoiced a lot after Microsoft included Tabular in SQL Server 2016 Standard Edition to increase its adoption but my enthusiasm was dampened by its maximum memory size of 16 GB. Interestingly, Multidimensional enjoys 64 GB of RAM in Standard Edition although you probably never need that much memory for OLAP cubes. As a memory-resident technology, Tabular is memory hungry. The current limitation of 16 GB is impractical. Twice the memory is required to process the database, leaving us with a database size of no more than 5 GB to be on the safe side. So, I started a quest to convince Microsoft to bring Tabular on a par with Multidimensional and increase its memory limit in Standard Edition to 64 GB. If you agree, please vote for my feedback on Connect.
Gartner’s 2017 BI and Data Analytics Magic Quadrant Shows Microsoft Leading

February 19, 2017 / No Comments »

Power BI is enjoying a tremendous momentum and unprecedented popularity. Just within this month, your humble correspondent has been teaching Power BI four times in a row. It looks like industry observers are taking notice of this momentum. As Kamal Hathi (General Manager, Microsoft BI) announced, the newly released Garner Magic Quadrant for Business Intelligence and Data Analytics gave Microsoft a very high score. The image below shows the Microsoft's lift between last year and this year in the Gartner magic quadrant. I'm not surprised about the Qlik drop given they sold out the company. What's still surprising to me is that Gartner ranked Tableau and Microsoft almost the same on the ability to execute. Although the report is not out yet, judging by the stub, Gartner used the same 14 criteria as last year, but added one more which is unknown at this point (probably real-time where Microsoft can score very...
Types of Power BI Real-time Datasets

January 31, 2017 / 2 Comments »

Everyone wants real-time BI, even when it doesn't have to be really "real time". Today Microsoft announced General Availability of Power BI Real-Time Streaming Datasets. There are actually three types of Power BI real-time datasets, as mentioned in the documentation. Push – Power BI permanently stores the data, enabling historic analysis, and reports creation atop the dataset. Behind the scenes, Power BI provisions an Azure SQL instance when the dataset is created. New data is pushed into SQL. Power BI then connects to that dataset via DirectQuery. Query Refresh (sending new queries to Azure SQL to update dashboard visuals) occurs whenever data is pushed in. When you create the dataset programmatically, you can specify a retention policy (defaultRetentionPolicy setting). When defaultRetentionPolicy is set to None, the dataset accumulates data to the maximum allowed Power BI limit (currently 1 GB). When set to basicFIFO, the dataset holds up to 200,000 rows and...
Atlanta MS BI Group Meeting on January 30th

January 27, 2017 / No Comments »

MS BI fans, join me for the next Atlanta MS BI and Power BI Group meeting on Monday, January 30th at 6:30 PM. One of our most experienced consultants, Neal Waterstreet, will share his real-life experience in master data management with SQL Server MDS. I'll update you on the latest with Power BI and SQL Server 2017. Prologika will sponsor the meeting. Rate this meeting http://aka.ms/PUGSurvey, PUG ID: 104 Presentation: Master Data Management with SQL Server 2016 MDS Level: Intermediate Date: January 30th, 2017 Time 6:30 – 8:30 PM ET Place: South Terraces Building (Auditorium Room) 115 Perimeter Center Place Atlanta, GA 30346 Overview: In this presentation we'll first discuss the position Master Data Management plays in an organization's overall data strategy. We'll review the key concepts, different roles and responsibilities that members of the team typically play, potential risks, and best practices to help you get your organization moving forward...
Unblocking the On-premises Data Gateway

January 27, 2017 / No Comments »

Scenario: You have configured the Power BI on-premises data gateway for centralized data access and verified that its data sources test just fine. Direct query connections work. However, when you go to Power BI Service and attempt to schedule a data refresh for a dataset, you might find that the data gateway is disabled. Solution: The most common reasons for Power BI to disable the on-premises data gateway for refresh are: Unlike the personal gateway, the on-premises data gateway requires you to register data sources. You must go to the gateway properties and create data sources for all data sources used in your Power BI Desktop file. Unfortunately, as it stands Power BI doesn't allow you to select which data sources in the Power BI Desktop file will be refreshed and which ones don't require a refresh. It's all or nothing proposition. So, if one data source is not compatible...