• Running ETL on AlwaysOn Primary Replica

    June 28, 2017 / No Comments » / 107 Views

    Scenario: Consider a SQL Server configured for AlwaysOn Availability Groups where a set of databases can fail over to another node. You have SSIS jobs and you created them on both nodes. However, you need to check if the current node on which the job is running is the primary node. If you don't do this, the job fails because the database isn't accessible. Solution: In every SQL Server Agent Job, add a first step to check if one of the replicated databases in the AlwaysOn availability group is the primary replica: IF sys.fn_hadr_is_primary_replica ('<replicated dataset name here>') = 0 BEGIN EXEC msdb..sp_stop_job N'DW Daily Load'; END This script checks if the database is a primary replica. If this is not the case, the script stops the next step, which in this case is the "DW Daily Load" step.

  • Scaling out SSIS in SQL Server 2017

    June 11, 2017 / No Comments » / 6687 Views

    SQL Server 2017 introduces the ability to scale out SSIS. The primary scenario is to enable customers to scale out their SSIS execution at the package level. Imagine a retail business that has about 300 packages to run at the end of the day to push transactional data/finance data from multiple stores to the centralized data warehouse for reporting. All the 300 packages need to run at night but they also need to finish by 6 AM in the morning before new business day starts. As business grows, data size grows, and it takes longer time to run the 300 packages and it is becoming more difficult for the ETL processing window to finish before 6 AM. With SSIS scale out feature, you can now set up the scale out cluster with 1 master and N workers, so that the master can automatically distribute the packages to the workers based...

  • Power BI Excel Publisher Doesn’t Load

    June 1, 2017 / No Comments » / 3862 Views

    Issue: You want to use the Power BI Excel Publisher but it doesn't load. You look at the Excel add-ins and you see that it's deactivated. You try to activate it and then you get an error. Solution: Copy the following Office PIA files to the add-in folder (C:\Program Files\Microsoft Power BI Publisher for Excel\bin). Microsoft.Office.Interop.SQLIS.12.0.nupkg OFFICE.dll Unfortunately, I don't know where these files are located. They were given to me by the Microsoft Support and I can't redistribute. If you can't find them, you can call MS support and reference support case: 117041015581533. Now when you open Excel, you should see the Power BI ribbon tab.

  • “Understanding Power BI Premium” Events

    May 31, 2017 / No Comments » / 6878 Views

    I'll present "Understanding Power BI Premium" at three public events in June. June 8th, Carolinas Power BI Group in Greensville, SC at 6 PM June 24th, SQL Saturday Chattanooga, TN at 1:15 PM June 26th, Atlanta MS BI and Power BI Group, Atlanta, GA at 6:30 PM My calendar has the details. Microsoft Power BI has enjoyed a lot of attention and success since it became generally available in July 2015.  But its licensing model and cloud-hosted limitations barred wide adoption, especially with larger organizations. The recently introduced Power BI Premium will change all of this. Join this session to learn how Power BI Premium will allow your organization to achieve: • Flexibility to license by capacity • Greater scale and performance • Extending on-premises reporting with Power BI Report Server • Embedded analytics

  • Understanding Writeback Target Allocation

    May 19, 2017 / No Comments » / 1098 Views

    I'm working on architecting a financial planning solution powered by Analysis Services Multidimensional. One thing that might not be obvious is how Multidimensional selects the target of writeback allocation. In this case, planning will be done at Customer and Product level. With the default equal allocation when writing at the customer level, it might appear that writeback doesn't work correctly. You'd expect that only the cells that contribute to the aggregated value (10 in the screenshot below) will be affected by writeback. However, if the Customer and Product entities are in different dimensions, writeback will affect all products! The reason behind this becomes obvious if you right-click the pivot table, and from its options enable "Shows rows with no data". Then, you'll see all products appearing with each customer (customers are crossjoned with products). Recall that by default, the pivot table uses NON EMPTY in MDX query to exclude combinations...

  • Atlanta MS BI Group Meeting on May 22th

    May 19, 2017 / No Comments » / 8463 Views

    MS BI fans, join me for the next must-attend Atlanta MS BI and Power BI Group meeting on May 22th at 6:30 PM. My esteemed friend, Stacey Jones from Microsoft will explain the predictive analytics capability of Cortana Analytics Suite. He'll demo a predictive analytics solution that was developed in a single day! It leverages Azure Machine Learning, Azure Data Factory, HDInsight, Spark, Power BI, and Intelligent Apps to deliver a solution that predicts the likelihood of flight delays for a customer. I will do a quick demo of the latest Power BI cool features. Our premium sponsor, TEKSystems, will sponsor the meeting. Presentation: Cortana Intelligence Suite End-to-End Level: Intermediate Date: May 22nd, 2017 Time 6:30 – 8:30 PM ET Place: South Terraces Building (Auditorium Room) 115 Perimeter Center Place Atlanta, GA 30346 Overview:   The Cortana Intelligence Suite provides tools to cover all types of business intelligence needs, from compelling dashboards...

  • What Does Power BI Premium Mean for You?

    May 3, 2017 / No Comments » / 2032 Views

    I'm sure we've heard the announcements today about Power BI Premium. In fact, Power BI Premium is so important that Microsoft has positioned it as a new product under the Power BI marketing umbrella name instead of a new licensing model. Microsoft and industry experts covered the announcements well so I won't reiterate the obvious. You may wonder what these changes mean for you. Let's summarize. Power BI Portfolio In a nutshell, Power BI Premium targets larger organizations which have faced two issues with the current Power BI licensing model: No "reader" license. If a report has a Power BI Pro features, all users accessing reports would need Power BI Pro license. So, if you a report that used Power BI Pro features, such as gateways or live connections and you won't this report to be available to 1,000 users, you had to foot $10,000/month bill because everyone required Power...

  • Power BI Report Measures Over Tabular Models

    May 2, 2017 / 2 Comments » / 12351 Views

    The May release of Power BI Desktop adds the ability to define DAX calculated measures when Power BI Desktop is connected live to a Tabular model or Power BI datasets. This is conceptually similar to defining MDX calculated members in Excel connected to a cube. The measure definitions are local to the Power BI Desktop model (the Tabular model is not modified). You can do all measure-related tasks as when you define measures in the data model, such as changing the data type and formatting the measure or changing the home table. In the screenshot below, I've defined a YTD report measure over the Adventure Works Tabular model. Behind the scenes, the DAX query generated by Power BI Desktop adds the measures as query-scoped measures in the /* USER DAX BEGIN/END */ section: DEFINE MEASURE 'Reseller Sales'[Reseller Sales YTD] = (/* USER DAX BEGIN */ TOTALYTD(SUM('Reseller Sales'[Sales Amount]), 'Date'[Date]) /*...

  • Important Power BI Announcements on May 3rd

    April 29, 2017 / No Comments » / 2526 Views

    Please make sure you register free and join Microsoft Business Forward online event (https://www.microsoft.com/en-us/dynamics365/business-forward) on May 3rd at 10 AM ET. Join Satya Nadella (CEO Microsoft), James Phillips (Corporate Vice President, Business Applications, Platform and Intelligence), Judson Althoff (Executive Vice President, Worldwide Commercial Business) for major announcements and details on the "new generation" of Power BI, Dynamics 365 applications, LinkedIn, and the Microsoft Cloud. This should be an important event not to miss!

  • Atlanta MS BI Group Meeting on April 24th

    April 22, 2017 / No Comments » / 27860 Views

    MS BI fans, join me for the next must-attend Atlanta MS BI and Power BI Group meeting on April 24th at 6:30 PM. Mike Bruce and Alex Higgins from Acuity Brands will share how they use Power BI to improve their development process. Acuity Brands will sponsor the event. And I'll demo the new Quick Measures Power BI measure. Presentation: Using Power BI to Track Software Development Performance Level: Intermediate Date: April 24, 2017 Time 6:30 – 8:30 PM ET Place: South Terraces Building (Auditorium Room) 115 Perimeter Center Place Atlanta, GA 30346 Overview: By using Power BI, Acuity Brands can monitor development teams' progress with rich, interactive dashboards. Data from Visual Studio Team Services ODATA feeds and APIs as well as pulling data from DocumentDB, teams can drill into their development performance and see where they may be having development performance. Data is accessed via embedded Power BI reports running on...


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!


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.