Atlanta MS BI and Power BI Group Meeting on June 3rd

/0 Comments/in , /by

MS BI fans, join us for the next Atlanta MS BI and Power BI Group meeting on June 3, Monday, at 6:30 PM at the Microsoft office in Alpharetta. I’ll show you how to bridge the analytical and developer worlds by integrating Power BI with PowerApps. Eric Flamm will demonstrate SQL Notebooks in Azure Data Studio. Prologika will sponsor the event. For more details, visit our group page and don’t forget to RSVP (fill in the RSVP survey if you’re planning to attend).

Presentation:Bridge Analytics and Developer Worlds with Power Platform
Date:June 3, 2017
Time6:30 – 8:30 PM ET
Place:Microsoft Office (Alpharetta)

8000 Avalon Boulevard Suite 900

Alpharetta, GA 30009

Overview:One of the Power BI most prominent strengths is that it’s a part of the much broader Microsoft Power Platform and Data Platform that spans various on-premises and cloud offerings for data storage, processing, and analysis. Join this session to learn how to integrate Power BI with PowerApps and discover exciting new possibilities that redefine the meaning of a report and let you do more with less. During this entirely hands-on, almost no-slides session I’ll walk you through the steps to implement a popular report requirement for writeback, that Power BI doesn’t natively support.
Speaker:Through his Atlanta-based company Prologika (https://prologika.com), a Microsoft Gold Partner in Data Analytics, Teo Lachev helps organizations make sense of their most valuable asset: their data.  Teo has authored and co-authored several bestselling books on organizational and self-service data analytics (his latest is “Applied DAX with Power BI”), and he has been leading the Atlanta Microsoft BI and Power BI group since he founded it in 2010. Teo has been a Microsoft Most Valued Professional (MVP Data Platform) since 2004.
Sponsor:Prologika is one of the most trusted names in Data Analytics. Our clients, from small businesses to Fortune 100 enterprises, derive tremendous value from our services. Our mission is to help organizations make sense of data by applying the latest technologies for descriptive and predictive analytics and get actionable insights. Your organization will spend less time mining for information and be better equipped to make sound business decisions.
Prototypes with Pizza“SQL Notebooks in Azure Data Studio” by Eric Flamm

092417_1708_AtlantaMSBI1.png

“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?