• Implementing Header-Details in Power BI

    March 6, 2017 / No Comments » / 1733 Views

    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 » / 4686 Views

    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 » / 8076 Views

    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 » / 1453 Views

    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 » / 14292 Views

    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 » / 6821 Views

    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 » / 35491 Views

    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 » / 3821 Views

    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...

  • Customer Success Case – ZynBit

    January 23, 2017 / No Comments » / 8764 Views

    One of our customers, ZynBit, made the Power BI blog today! Initially, ZynBit was considering Tableau but abandoned it in favor of Power BI because of the Power BI superior data modeling capabilities and the cost effective licensing model of Power BI Embedded. Prologika helped ZynBit to transition their solution to Power BI, including designing the data model and integrating reports with Power BI Embedded. Read our case study here.

  • Monitoring Progress of UPDATE

    January 22, 2017 / No Comments » / 4236 Views

    How to monitor the progress of an UPDATE statement sent to SQL Server? Unfortunately, SQL Server currently doesn't support an option to monitor the progress of DML operations. In the case of UPDATE against large tables, it might be much faster to recreate the table, e.g. with SELECT … INTO. But suppose that INSERT could take a very long time too and you prefer to update the data instead. Here is how to "monitor" the progress while the UPDATE statement is doing its job. Suppose you are updating the entire table and it has 138,145,625 rows (consider doing the update in batches to avoid running out of log space). Let's say the UPDATE statement changes the RowStartColumn column to the first day of the month: UPDATE bi.FactAccountSnapshot SET RowStartDate = DATEADD(MONTH, DATEDIFF(MONTH, 0, RowStartDate), 0); Use these statements to monitor the remaining work by using a reverse WHERE clause. Make sure to...

Training

We offer onsite and online Business Intelligence classes! Contact us about in-person training for groups of five or more students.
For more information or to register click here!

Books

Learn Power BI at your own pace with our latest book Applied Microsoft Power BI. Targeting information workers, data analysts, pros, and developers, It is designed as an easy-to-follow guide for learning how to implement BI solutions spanning the entire personal-team-organizational BI spectrum.

Syndication