Creating a Parameter Table

Note: ‘Technical Tidbits’ is an addition to this blog comprised of mini-blogs on different technical topics. Not all of these topics will be on the introduction level; in fact, most will deal with new things I’m learning as I continue on my own technical journey. These little blogs will expose you to advanced topics. You will want to read them and tuck them away for later review. For example, you may not have a clue about parameter tables now; but when you begin your study of the DAX language, you will remember the ‘tidbit’ blog and have it to review. Think of ‘Technical Tidbits’ as little markers down the road; they’re showing you what lies ahead.

DAX Master Wanna-Be

I’m on a quest to be a DAX Master.  As Yoda of Star Wars fame wielded the ‘force’ as a Jedi warrior, I’d like to wield DAX functions to make Tabular Services and Excel Power BI slice through complex evaluation contexts!  OK, I want to be taller and cuter than Yoda as well; I’m a stickler for detail.

So what is DAX and what is so great about DAX?  Data Analysis Expressions is a formula language, used primarily with Tabular Services and Excel Power Pivot data models.  Using DAX, you can create calculated columns and calculated fields on your data models; but our focus in ‘Technical Tidbit’ today is on creating a Parameter Table.

OK, what do DAX and Parameter Tables have to do with each other?  What is a Parameter Table and what is the value of creating one?  Well, you might not get as excited about Parameter Tables as I did, but I thought they were amazing when I first learned about them.  As I read Ferrari and Russo’s book Microsoft Excel 2013: Building Data Models with PowerPivot, Chapter 7 dealt with ‘Understanding Evaluation Contexts’.  Then, in order to bring Evaluation Context into the practical realm, they set up a real-world scenario utilizing the theory of evaluation context and introduced Parameter Tables.

I won’t go into the concept of ‘evaluation context’, but remember that it is VERY important to understanding DAX; using it wisely with data models and filtering is a must.  With that said, let’s get to what a parameter table is and when they might be useful.  A parameter table is when you create a little Excel table BUT it is unrelated to the rest of the data model.  Well that doesn’t do anybody any good; but wait!  This unrelated parameter table is used internally by DAX functions to modify the behavior of the other tables.

Say, for instance, you have a database with millions, billions or trillions of rows of Sales Amounts; trying to build a report on the sales column could drive you nuts.  Now say your manager’s boss wants to see a report on all these sales.  This amount of data would take up lots of space in a report and would not lend itself to concise reporting or easy analysis.  We need a plan; a parameter table!

How to Create a Parameter Table

Your parameter table (let’s call it ‘ParameterSlicer) will allow the end user to decide the scale they want to view the sales amounts; for example: ‘Real Value’, ‘Thousands’, ‘Millions’, ‘Billions’, etc.  The use of the parameter table does not filter the data; but allows you to change the scale used in viewing the numbers.  Now that’s slick!

First, I used the ContosoRetailDW to create my data model, especially because there are millions of rows with lots of sales amount that will produce large sums.  Then I created a little Excel table, named ‘ParameterSlicer’;  giving it three columns ‘ShowValueAs’, ‘DivideBy’, and ‘Order’ along with four rows: ‘RealValue’, ‘Thousands’, ‘Millions’, ‘Billions’.Excel Parameter Table Sample

After you create the new table, you used the Table Import Wizard to load the Excel table to your data model.  Remember, the new table is not related to anything inside your model; but once the table is in the model, you can use it as the source for a report slicer. This little Excel table will be the source for the slicer in that report your boss wants to see; but nothing will happen without the DAX Magic!

DAX

Without DAX, your little Excel table will sit in the data model and just look cute; lonely and unrelated, but cute. Data Model with Unrelated Table

But that’s not good enough; we must use DAX code to make everything work.  Your DAX formula must ‘see’ what the end user has selected in ‘ShowValueAs’ and modify the content of the report to display the values selected.  I modified the DAX calculated field as shown in Ferrari and Russo’s book, and will try to explain it as simply as I can.

SumOfSalesAmount (this is just the name of your calculated field that was placed on the ‘Sales’ table) =

IF (

HASONEVALUE = ( ParameterSlicer[ShowValueAs]),    (this is the table name|table column of parameter table)

SUM (Sales[SalesAmount]) / VALUE (ParameterSlicer [DivideBy]),

SUM(Sale[SalesAmount])

)

DAX Explained

The IF function works with another function HASONEVALUE.  The IF function test a condition; if the end user does not select anything in the slicer, the HASONEVALUE will return FALSE and nothing happens.  When the end user selects a single value on the Slicer table, then the HASONEVALUE will return TRUE and the sales numbers will be seen by the scale selected by the end user.  It’s like saying “if the user has selected a single value in the slicer, then show the ‘sum of sales amount’ divided by the corresponding denominator; otherwise, show the total of the ‘sales amount”.

It’s important to remember that the ‘ParameterSlicer’ Excel table DOES NOT filter the data, it will simply allow the end user to change the scale of the numbers.  Now with the Excel table loaded into the data model and this DAX calculated measured placed on your ‘Sales’ table, you can bring everything together. Parameter RealParameter ThousandsParamter MillionsParamter Billions

This is a simple example but the most common use for a parameter table; however, they can be used for much more.  With a parameter table you can modify the way a number is computed, change parameters for an algorithm, or change values returned from a calculated field.  So don’t dismiss this little guy.

Final Thoughts

The table names and columns have been changed to protect the innocent, just kidding; you know you’ll need to plug in your actual database table names and columns, but you get the idea of using parameter tables.  Be sure to get Alberto Ferrari and Marco Russo’s excellent book: Microsoft 2013: Building Data Models with PowerPivot.  The book is a great way to begin your journey into DAX.  Until next time!

Susan Schneider lives in Jacksonville, Florida with her wonderful husband Steve. She enjoys sailing and is a ‘wanna be fisherman’, and loves all things BI. See more information under the ‘About Me’ section. Remember to sign up for new blog notifications: Go to Subscribe2 on the sidebar and sign up!

Pragmatic Works LoveFest

Deep Dive: Pragmatic Works Training

I’ve blogged about the great training at Pragmatic Works before, but today, we deep-dive into the various training available through Pragmatic Works; let the lovefest begin with Training on the T’s!

Before my journey on the technical side, I was a teacher; so it’s quite easy for me to recognize great teaching versus good teaching. So, if you have a limited number of hours to dedicate to training, but you want to get the best possible learning experience; then you need to watch Devin Knight’s training sessions. It doesn’t matter what Devin is teaching on, the subject is going to be excellent! Devin is a wonderful communicator and teacher, and he presents the subject matter in a logical progression; making it easy to learn.

Another favorite presenter is Brian Knight.   I remember sitting in a seminar at a SQL Saturday, many years ago, listening to Brian talk about Data Mining.  Brian’s knowledge of the subject, along with his boundless enthusiasm, made you want to learn Data Mining. He says data is sexy, and he’s correct!

I’ll also introduce you to a few more of my favorite webinar presenters: Kathi Kellenberger, Bradly Schacht, Mike Davis, and Angel Abundez. There are other great presentors like Adam Jorgensen, Shawn Harrison, and Jason Strate; but there’s not enough time in the blog post to cover everything!

Building a Strong Foundation

For the next few minutes, I want to list specific Training on the T’s webinar titles and presenters that will help you build a strong foundation in your technical knowledge. I’ve selected the following order of webinars to save you time and help you lay a solid foundation for your learning. So let’s get started!

First off, watch Devin Knight’s webinars, with my comments listed for a few of them:
-Understanding Microsoft Self-Service Business Intelligence
-Introduction to Power BI (**great overview of Power BI Excel 2013)
-Introduction to Power Pivot (**the cornerstone to the Power BI tool suite in Excel 2013)
-Introduction to Common DAX Expressions (**the language that make Power Pivot rock)
-Creating Real World Power Pivot Models (**you’re only as good a your database)
-Introduction to Power Query (**talk about an easy way to ETL: ‘extract, transform, and load’)
-Creating an End to End Power View Reporting Solution (**deliver the ‘wow’ in report presentation)
-Touring your Data with Power Map
-Choosing a Microsoft Reporting Technology

Now, there are many more topics that Devin has available, but I’m giving you a logical course of training to give you the building blocks you need for a solid foundation in the MS BI Stack. After you view these webinars, let’s move on to a few webinars from Brian Knight.

One of the best ‘soup to nuts’ overview on planning a Data Warehouse was recently presented by Brian (with PowerPoint by Devin). The webinar presents a clear cut look at the planning stages of a DW. With that said, it’s also beneficial for beginners as well, as you learn the terminology and concepts; you’ll not want to miss this one.

Brian Knight:
-Tips and Tricks for Planning a Data Warehouse (**great presentation ‘soup to nuts’ overview)
-Quick Start to Power Pivot
-How to use Power Query as your Self-Service BI ETL tool

Topics for a Future Day

When you have some more training under your belt, you’ll want to move on to webinars by Mike Davis, Kathi Kellenberger, Bradley Schacht, and Angel Abundez. When you feel comfortable with T-SQL, learn about Window Functions; Kathi gives two excellent webinars on this subject. Mike Davis gives the best presentation on how to prepare for a technical interview. His presentation on the subject at the 2014 SQL Saturday was excellent, but his webinar ‘Mastering the Technology Interview’ is even better! These folks are just a few of my favorites, but all the PW webinars are excellent. When you get a chance, check out some webinars by Adam Jorgensen, Shawn Harrison, and Jason Strate as well.

Kathi Kellenberger: T-SQL Window Functions
-T-SQL Window Function Performance
-Write Better Queries with Window Functions

Mike Davis
-Mastering the Technology Interview
-Complex DAX Expressions Power Pivot

Bradley Schacht
-Introduction to ETL Using SSIs
-SSIS ETL for Beginners
-Back to Basics: SSIS 2012 for Beginners

Angel Abundez
-Power BI Tips for Data Analyst
-Chart Anatomy 101

Virtual Training

Sometimes you just need to spend some money on training. So when you’re ready to make that move, PW offers Virtual Training courses in six areas: Analysis, Big Data, Business Analytics, Data Integration, Data Visualization, and Database Administration. The cost is $995.00 for 4 days (3 hours each day) of in-depth virtual training; the class is limited to around 24 participants. You’ll usually have labs to complete after each day’s training. For more information click on the Data Sheet (a one page outline of each day’s topics), Details (specifics for each day’s training), and Date/Time(when the class will be offered during the year). I’ve taken the SSRS course and I’m scheduled to take the Tabular and Power Pivot for Developers in October. I can hardly wait!

Workshops and Bootcamps

PW offers workshops around the country; they are in-person two day training courses on a variety of SQL Server topics. Then you have Bootcamp, a week-long intensive learning on either Business Intelligence or Performance Tuning.

Closing Thoughts

Pragmatic Works has lots of good stuff under their Resource section; things like Articles and Whitepapers, Cheat Sheets, and more. If you are limited on time, but want consistent, excellent training; spend your time on the Pragmatic Works website. Well, we’ve definitely had a Pragmatic Works lovefest today. Next time we’ll take a deep dive into Lynda.Com and the great technical training available there. Until then!

Susan Schneider lives in Jacksonville, Florida with her wonderful husband Steve. She enjoys sailing and is a ‘wanna be fisherman’, and loves all things BI. See more information under the ‘About Me’ section. Remember to sign up for new blog notifications: Go to Subscribe2 on the sidebar and sign up!