“Applied DAX with Power BI” Book Available

/0 Comments/in , /by

I’m excited to announce my latest book: “Applied DAX with Power BI: From zero to hero with 15-minute lessons“. This book was born out of necessity. In my consulting practice, I had been teaching and implementing Power BI and Analysis Services Tabular, and people were constantly asking for DAX book recommendations. Indeed, DAX is not an easy topic and has its ways to humble even experienced practitioners. There are a few good reference books out there, but they could be somewhat overwhelming for novice users. So, I turned my classroom and consulting experience into this book and designed it as a self-paced guide to help you learn DAX one lesson at a time.

This one was a different book to write. Unlike my other books, which tend to be heavy, this book is smaller (220 pages). Regardless, it took me a while to write it. The book also adopts a lesson-based approach,  so the reader can learn and practice a specific DAX concept quickly. There are usually three sections in each lesson: Practice (with step-by-step guidance), Outcome (a visual that shows the result), and Analysis (explaining why it works this way). Most lessons are five to six pages long, and it should take no more than 15 minutes to complete the lesson’s exercises (you can download and view a sample lesson from the book page).

And if you do one lesson per day, you’ll be a DAX expert in a month!

Currently, the book is available on Amazon (paperback and Kindle eBook) and it’s making its way to other outlets. As always, I’m awaiting feedback on the discussion forum at the bottom of the book page.

051119_2019_AppliedDAXw1.png

Presenting at SQL Saturday Atlanta on May 18

/0 Comments/in /by

SQL Saturday Atlanta next Saturday might break the attendance record with over 900+ people registered! Kudos to the organizers. It’s not easy to organize an event of this size with a modest budget. I’ll present “What’s New for BI in SQL Server 2019” at 1 PM.

“SQL Server 2019 delivers important data analytics features and enhancements. Join this session to discover what BI features are coming up in Database Engine, SSIS, SSAS, SSRS (Power BI Report Server), and MDS, and to see some of these preview features in action.”

Fixing Random Problems with DAX

/0 Comments/in /by

Scenario: You have a dimension table and a fact table, and you want to generate some test data, such by randomly looking up a value from the dimension table for each row in the fact table.

The following DAX formula should get the job done but RANDBETWEEN doesn’t work (although it works as a standalone function outside LOOKUPVALUE). Specifically, you get either the same value or an empty value.

Rand_Value_From_Other_Table =
LOOKUPVALUE (
Dim_Table[Fruit],
Dim_Table[Fruit_ID], ( RANDBETWEEN ( 1, MAX ( Dim_Table[Fruit_ID] ) ) )
)

Solution: I think the issue is that like Power Query, DAX assumes that functions are idempotent. Given the same arguments, they should produce the same result, which isn’t true for RANDBETWEEN and Power Query Number.Random functions. The simple solution is to change the first argument to perform a dummy operation that doesn’t use a constant value, such as:

Rand_Value_From_Other_Table =
LOOKUPVALUE (
Dim_Table[Fruit],
Dim_Table[Fruit_ID], ( RANDBETWEEN ( 1 + Fact_table[Fact_table_id] – Fact_table[Fact_table_id], MAX ( Dim_Table[Fruit_ID] ) ) )
)

Thanks to Stacey Jones who came up with this interesting puzzle.

APPLIED DAX WITH POWER BI
 (From zero to hero with 15-minute lessons)

/0 Comments/in /by

Applied DAX with Power BIStart your self-paced DAX study today and get from zero to hero in a month!

    • Publication date: May 10, 2019
    • Size: 220 pages, 7.5″ x 9.25″
    • Price: $34.99
    • ISBN 10: 1-7330461-0-0
    • ISBN 13: 978-1-7330461-0-7

new

 

 

 

Introduces data analysts and developers to Data Analysis Expressions (DAX) — the programming language Microsoft Power BI, Power Pivot, and Analysis Services Tabular.

You won’t get far with Microsoft BI without DAX. However, DAX has been perceived as difficult to learn. Not anymore! This guide will help you master the major DAX concepts and get you from zero to hero in no time!

Synopsis

As its name suggests, the main objective of this book is to teach you the practical skills to take the most out of DAX from whatever angle you’d like to approach it. It’s written for data analysts using Power BI or Power Pivot, BI developers targeting Analysis Services Tabular, and in general, anyone who’s interested in DAX. You’ll learn DAX methodically, with self-paced lessons that build upon your previous knowledge and introduce more challenging concepts progressively. Each lesson starts with providing the necessary theoretical background, followed by step-by-step exercises. Most lessons are five to six pages long, and it should take you no more than 15 minutes to complete the practices. Do one lesson per day and become a DAX expert in a month!

This page provides sample chapters, source code, and a discussion forum where the author welcomes your feedback and questions.

What’s inside

  • Extend your models with custom columns
  • Implement important business metrics and key performance indicators
  • Create custom queries to explore data and test measures
  • Apply advanced relationships and data security
    …and much more!

Resources

Front matterSample chapterForum
IndexSource code
Back coverErrata

Reviews

“This book fills a gap for the new users of Power BI (or Power Pivot) who’ve taken a stab at writing DAX (either via Power Pivot or Power BI) and are now ready to fill in some beginner-blanks and learn the correct mindset for how and when to use DAX along with some common business calculation patterns.”

Bill Anton
SSAS / Power BI Consultant

How to purchase

Buy the paper copy from Amazon
Buy the Kindle eBook from Amazon

 

Another Successful BPM Solution

/0 Comments/in /by

Business Performance Management (BPM) is a methodology to help the company predict its performance. An integral part of a BPM strategy is a process for Budgeting, Planning, and Forecasting which is typically performed by the Finance department. When it comes to Finance, nothing is simple, and BPM is no exception. This diagram illustrates what typical data movement might look like to consolidate BPM areas into a consolidated Financials view.

050619_0037_AnotherSucc1.png

In this case, a client wanted to automate budgeting for the Sales, General, and Administrative accounts (SG&A). To clarify the jargon, SG&A accounts capture overhead cost, such as salaries and bonus, and this client used Workday to record these expenses for each employee.

I typically see companies take two paths when it comes to BPM:

  • Home-grown Excel-based solutions – You can do anything in Excel, but this path typically leads to fragile solutions that collapse under their weight.
  • High-end planning solutions – To get out of the Excel “spreadmarts”, the temptation is to buy a high-end commercial software, such as Hyperion. This path leads to a very high initial investment and then even higher “customization”.

A better option that we pursued was to implement a customized solution based on the Microsoft BI platform. This approach reduces cost and allows users to make changes using the tool they like most: Excel.

For example, the screenshot below shows how the user can filter the report at any level and make changes to the forecast values (in blue color).

050619_0037_AnotherSucc2.png

The main benefits of this solution are:

  • Automated retrieval of actuals from the company’s data warehouse (also designed by Prologika)
  • A highly-customized solution that meets complex business needs
  • Management has immediate access to actuals, budget, and forecast
  • Elimination of manual entry and data errors.
  • Ability to analyze company’s performance by various dimensions.

How does your company do business performance management?

Atlanta MS BI and Power BI Group Meeting on May 6th

/0 Comments/in , /by

MS BI fans, join us for the next Atlanta MS BI and Power BI Group meeting on May 6, Monday, at 6:30 PM at the Microsoft office in Alpharetta. Patrick LeBlanc, a Principal Program Manager at Microsoft, will show us how to integrate Power BI and Flow for geospatial analytics. I’ll showcase Power BI Report Builder.  TEKSystems will sponsor the event. For more details, visit our group page and don’t forget to RSVP (use the RSVP survey on http://atlantabi.pass.org if you’re planning to attend).

Presentation:

Using the Power Platform to Enhance Spatial Granularity

Date:

May 6, 2019, Monday

Time

6:30 – 8:30 PM ET

Place:

Microsoft Office (Alpharetta)

8000 Avalon Boulevard Suite 900
Alpharetta, GA 30009

Overview:

Join this session to see how you can use the Power Platform to solve a real world customer problem. With a growing mobile workforce, many organizations are trying to figure out how to properly arm them with the correct tools. In this session we will explain and demonstrate how you can leverage the Power BI Mobile app and Microsoft Flow to build a solution that enables them to quickly identify locations near and far. We will demonstrate how the solution was built step-by-step, detailing how and why certain technologies and solutions were selected or not.

Speaker:

Patrick LeBlanc is a currently a Data Platform Solutions Architect. Along with his 15+ years’ experience in IT he holds a Master of Science degree from Louisiana State University. He is the author and co-author of five SQL Server books and one of the only two guys in the cube. Prior to joining Microsoft, he was awarded Microsoft MVP award for his contributions to the community. Patrick is a regular speaker at many SQL Server Conferences and Community events.

Sponsor:

People are at the heart of every successful business initiative. At TEKsystems, we understand people. Every year we deploy over 80,000 IT professionals at 6,000 client sites across North America, Europe and Asia. Our deep insights into IT human capital management enable us to help our clients achieve their business goals – while optimizing their IT workforce strategies. We provide IT staffing solutions, IT talent management expertise and IT services to help our clients plan, build and run their critical business initiatives!

Prototypes
with pizza

“Power BI Report Builder” with Teo Lachev

092417_1708_AtlantaMSBI1.png

Power BI Report Builder

/0 Comments/in /by

SSRS is near and dear to my heart. My first book (“Microsoft Reporting Services in Action” published in 2004 by Manning) was on this subject and I became an MVP because of my contributions around SSRS back in those days. Because of this and because every good thing should come in two, I rejoiced when I heard Microsoft announcing the Power BI Report Builder to complement the work they do to bring paginated reports to Power BI. Although lacking in interactivity, there are a few good reasons to favor SSRS paginated reports as they continue to be the most extensible and feature-rich report type in the Microsoft BI ecosystem, as I explained in more detail in my “Choosing a Reporting Tool” blog.

Like Power BI Desktop, we now we have two Report Builder tools. The SSRS Report Builder continues to support to full feature set of SSRS and targets deployment to a report server. Power BI Report Builder is tethered to Power BI (Power BI Premium to be precise since you can deploy paginated reports to Power BI Premium only for now). Not all SSRS features are currently supported in Power BI, such as shared datasets and shared data sources, drillthrough reports and subreports, but Microsoft is working hard to close the gap. For more information about the current limitations, refer to this article (however, besides a subset of data sources, I didn’t see any other feature restrictions).

I ran into two issues with the recently released update to Power BI Report Builder. First, when running a report, the tool throws an error “This method explicitly uses CAS policy, which has been obsoleted by the .NET Framework”. The simple workaround is to uninstall Power BI Report Builder and install it again.

Second, the Power BI XMLA point is currently down so don’t attempt to connect the Report Builder (or any other tool) to Power BI datasets published to a premium workspace. You’ll get an Unauthorized error. According to the current advisory, April 28th is the rough estimate when the fix will be deployed worldwide.

Power BI XMLA Endpoint

/0 Comments/in /by

If “XMLA” doesn’t ring a bell especially in the context of Power BI, it stands for Extensible Markup Language for Analysis. Still puzzled? It’s the protocol of Analysis Services (Multidimensional and Tabular). So, when an Excel or Power BI sends a query to a cube, it’s encoded according the XMLA specification (an XML-based format). And, the XMLA endpoint is the web service endpoint that Analysis Services listens for upcoming requests.

Now that I cleared the terminology, Microsoft announced the public preview of the XMLA endpoint in Power BI Premium. Since Power BI uses Analysis Services Tabular to scale and hosts the Power BI Desktop models you deploy, this means you can now access that Analysis Services backend instance which wasn’t accessible before. Or, at least read-only for now, meaning that you can only query it and not deploy organizational Tabular models to it.

What it’s in there for you? Here are some scenarios that this management feature enables:

  • Besides Excel, you can connect any client that support Analysis Services, such as Tableau, to your published Power BI datasets if you’re looking for ways to diversify reporting. Chris Finlan mentions this as one scenario worth considering for the near-and-dear to my heart Report Builder (aka Power BI Paginated Report Builder).
  • You can profile your Power BI published datasets by connecting the SQL Server Profiler.
  • You can stress test your Power BI datasets, such as to ensure that they support the expected workload.
  • And my favorite, I can script any Power BI model to see how all these hidden features, such as grouping and binning, are coded.

040719_0037_PowerBIXMLA2.png

Microsoft is probably finding it increasingly difficult to maintain multiple deployments for Analysis Services: SSAS, AAS, and Power BI. Once the Power XMLA endpoint is writable and Power BI dataset sizes increase, I expect them to nudge customers to deploy organizational Tabular models to Power BI Premium, to be on the latest and greatest on a single platform. I hope that it won’t require Power BI Premium too when it goes GA.

Magic Quadrant for Dummies

/0 Comments/in /by

Confused by magic quadrants? Curious about their internals, such as how companies move from one quadrant to another? GeekAndPoke came up with this much needed resource that explains it all. No words necessary!

Happy April 1st!

Atlanta MS BI and Power BI Group Meeting on April 1st

/0 Comments/in , /by

MS BI fans, join us for the next Atlanta MS BI and Power BI Group meeting on April 1, Monday, at 6:30 PM at the Microsoft office in Alpharetta. Considering all the the momentum behind machine learning, the main presentation will be “Microsoft Citizen Data Science with Power BI” by Stacey Jones, a Principal Data Solution Architect at Microsoft. I’ll do a brief overview of the brand new Modeling tab in Power BI Desktop.  Vacation Express will sponsor the event. For more details, visit our group page and don’t forget to RSVP (use the RSVP survey on http://atlantabi.pass.org if you’re planning to attend).

Presentation:Microsoft Citizen Data Science with Power BI
Date:April 1 2019, Monday
Time6:30 – 8:30 PM ET
Place:Microsoft Office (Alpharetta)

8000 Avalon Boulevard Suite 900
Alpharetta, GA 30009

 

Overview:Are you curious about Data Science? Are you a Data Scientist? If you answered yes and no to these questions respectively, this session is for you. In this session I will discuss the emerging Citizen Data Scientist role and demonstrate the emerging tools targeting this role, especially the new features in Power BI, such as AutoML
Speaker:Stacey Jones specializes in mentoring and guiding firms in their efforts to build a modern Data, AI & BI governance programs that empower their business with Self-Service BI and Data Science capabilities. He currently serves as the Principal Data Solutions Architect at the Atlanta Microsoft Technology Center (MTC).
Sponsor:Based in Atlanta, Vacation Express is one of the largest tour operators in the southeast United States and has been in business for 30 years. Vacation Express is a member of family-owned and operated, Toronto-based Sunwing Travel Group Inc., Canada’s leading vertically integrated leisure travel company, and has sold well over 2 million vacation packages to its exotic destinations!
Prototypes
with pizza		“The Power BI Desktop Model tab” by Teo Lachev

092417_1708_AtlantaMSBI1.png