• Understanding Writeback Target Allocation

    May 19, 2017 / No Comments » / 333 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 » / 5584 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 » / 1591 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 » / 11983 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 » / 2312 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 » / 2546 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...

  • Tabular DAX Editor

    April 22, 2017 / No Comments » / 4123 Views

    The Tabular toolset is getting better. One thing that I miss from Multidimensional is the cube script that lets you view all custom calculations in one place so that you can organize them any way you want, add comments, etc. This is why I contributed to the DAX Editor tool. Microsoft has taken notice and introduced a tool (also called DAX Editor) in the latest SSDT release. Read Kay Unkroth's announcement here. The Microsoft DAX Editor supports the old XML-based schema and the new JSON schema. On the upside, it gives you a break from the Measure Grid and the formula bar. On the downside, you can work only on one measure at the time. So, let's leave it to marinate it a few more months with the hope that we can finally have a Tabular script. As Kay commented at the end of his blog post there is a...

  • Automating Excel to Power BI Publishing

    April 18, 2017 / No Comments » / 1197 Views

    Excel 2016 added a Publish to Power BI menu to let you export or connect Excel workbooks to Power BI. You can read more about this Excel feature here. One area where Excel is still ahead of Power BI Desktop is that is has an object model that lets you automate tasks with VBA. Unfortunately, Power BI Desktop doesn't have an object model so you have to resort to unsupported ways (aka hacks) to automate tasks, such as refreshing and publishing to Power BI. I discussed some here. I've noticed that Microsoft added not yet documented PublishToPBI method to Excel 2016. With it, refreshing the Excel data model and publishing it to Power BI Service takes two lines of code (you'd need more code to open the Excel workbook from an external application). Sub Macro1() ActiveWorkbook.Model.Refresh ActiveWorkbook.PublishToPBI PublishType:=msoPBIExport, nameConflict:= msoPBIAbort, bstrGroupName:="<Some Workspace>" End Sub

  • Power BI Quick Measures

    April 3, 2017 / No Comments » / 4152 Views

    One of the most common complaints raised by Power BI customers is the DAX steep learning curve. The April release of Power BI Desktop introduces a feature called Quick Measures. Currently in preview (make sure to enable Quick Measures from File ð Options and settings ð Options, Preview features), Quick Measures are supposed to replace Quick Calcs. Besides supporting only a limited number of packaged calculations and not working on top of custom measures, the problem with Quick Calcs is that they don't show the DAX formulas so there isn't a way for you to learn from the work Microsoft did or to change the formulas to customize their behavior. This changes with Quick Measures. You can create a Quick Measure over implicit or explicit measures. To do so, once you add a field to the report, expand the measure drop-down in the Fields of the Visualizations pane, and the...

  • Atlanta MS BI Group Meeting on March 27th

    March 23, 2017 / No Comments » / 16262 Views

    MS BI fans, join me for the next Atlanta MS BI and Power BI Group meeting on Monday, March 27th at 6:30 PM. Dave Tangren and Nelson Davis from Slalom will compare Power BI to Tableau. Slalom will sponsor the event. I'll show the latest of the Power BI Matrix visual. It will be a great meeting! Presentation: Comparing Power BI to Tableau Level: Intermediate Date: March 27, 2017 Time 6:30 – 8:30 PM ET Place: South Terraces Building (Auditorium Room) 115 Perimeter Center Place Atlanta, GA 30346 Overview: Power BI is getting stronger all the time. So is its competition. Want to see how Power BI stacks up against Tableau? Come see our thought leaders from Slalom debate the points! Speaker: Dave Tangren is a Practice Area Leader for Slalom in Atlanta. Dave brings thought leadership in visual analytics and business discovery to the market. With his deep background in Microsoft...

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