Dan English's BI Blog

Welcome to my random thoughts in regards to Business Intelligence, databases, and other technologies

Posts Tagged ‘dax’

Book Review: Microsoft Tabular Modeling Cookbook

Posted by denglishbi on February 21, 2014

There is a new Analysis Services (SSAS) Tabular book that is available to own and add to your library.  The book is written by Paul te Braak, who is a lead business intelligence consultant in Australia and is one of the developers on the DAX Studio project, and he has put together an outstanding cookbook.  When the book was released I was surprised and excited.  I was surprised because I did not know that Paul was working on this (he is the sole author, big kudos to Paul), and excited because I knew it was going to be a good one.  I had this one on my radar list of books to add to my collection; I am definitely a big fan of the Packt Publishing Cookbook series style of books. Microsoft Tabular Modeling CookbookWhat I like about the books is that they introduce a topic or situation and then go over the solution in a very simple and easy to understand format – Getting Ready, How to do it, How it Works, There’s more.  Paul adds a lot of great insights in this book in explaining how the solutions work as well as including a bunch of ‘Tips’ along the way as well.

Here is when I first became aware that the book was released last month and posted the O’Reilly media deal:

image

So when I was asked by the publisher this past month to provide a review of the book I was more than happy to say ‘yes’.

Paul does a great job on slowly working you into the Tabular modeling concepts and the only tool you need to get going is Excel and the Power Pivot add-in.  Paul’s examples use Excel workbooks and flat files for the most part, so that makes it really easy to get started and get your learn on.

What is amazing is that this book is just over 300 pages and it is loaded with great content that covers items such as how to use Power Pivot, hierarchies, drilldown, parent-child hierarchies (including how to hidememberif in DAX), smart measures, smart keys, programmatic access in Excel – cube functions and VBA, querying with DAX, Power View, and more!  Simply amazing, Paul does a fabulous job and this is a great intro book that progresses into advanced topics and has great examples, tips, and insights that are a big time value add.

Here is a outline of the chapters:

Chapter 1: Getting Started with Excel
Chapter 2: Importing Data
Chapter 3: Advanced Browsing Features
Chapter 4: Time Calculations and Date Functions
Chapter 5: Applied Modeling
Chapter 6: Programmatic Access via Excel
Chapter 7: Enterprise Design and Features
Chapter 8: Enterprise Management
Chapter 9: Querying the Tabular Model with DAX
Chapter 10: Visualizing Data with Power View

Book link – Microsoft Tabular Modeling Cookbook.

I would definitely rate this as a must have for anyone doing tabular SSAS development and give it 5 out of 5 stars – image

Awesome job Paul and thanks for writing the book and sharing!

Posted in Analysis Services | Tagged: , , | 1 Comment »

Book Review: DAX FORMULAS for PowerPivot: The Excel Pro’s Guide to Mastering DAX

Posted by denglishbi on November 15, 2012

This week I got the opportunity to read a new book out on the market by Rob Collie (PowerPivotPro) – “DAX FORMULAS for PowerPivot: The Excel Pro’s Guide to Mastering DAX”.  I have to admit that once I received the book I read through it in a couple of days, couldn’t put it down and wanted to finish it.  I really enjoyed the style of the book, just like Rob’s blog posts, and there was just a really good natural transition of the content that it kept me hooked.  While reading the book I really felt that I was in a classroom and Rob was presenting the material, something about the writing style, really enjoyed it (hat tip to Rob).

The book is loaded with great tips and advice from Rob, reference links to his blog posts and other content, and real world examples.  The solutions and examples provided in the book can be used right away if you are already using PowerPivot or Tabular SSAS.  There is material in here not just for the Excel pros, but also for IT/BI pros as well.

The second night I was reading the book my son took a peek at what I was doing on my Kindle Fire HD (great reading experience by the way – color, access to the embedded pictures to enlarge, ability to access the links directly, easily take notes and add bookmarks).  My son is in first grade and really starting to get comfortable reading.  He started to read the page I was on:)  It was really cute.  The book was not only good for me, but helped my son learn new words and work on his reading (not sure if this book can qualify for an AR test though)!

When it comes to the editing of the book I did come across a some minor typos, nothing too major, and one figure was missing (Figure 154).  There are references to the Measure Grid (actually Calculation Area in PowerPivot, Measure Grid in Tabular SSAS – this is one thing that I wish Microsoft kept the same or consistent for referencing).  I got a laugh when Rob states that implicit measures are ‘dead to me’ and that he never, ever, EVER creates implicit measures.

The book is really invaluable and the price is a steal (particularly if you buy it on Mr. Excel’s store here)  The length was exactly what I like, short and to the point, I don’t want to read a 800 page door stopper, anything in the 300 or less page range is perfect (that is why I liked the Rational Guide books by Rational Press) and supplemented with links for additional references and more content if desired.  The material and insights that Rob covers is fantastic.  The disconnected table concepts as well as performance tips are PRICELESS.  I would highly recommend this book and without a doubt give it a five star rating.

image

Posted in PowerPivot | Tagged: , , , | Leave a Comment »

Video: Leveraging PowerPivot Demonstration

Posted by denglishbi on April 4, 2011

I finally got around to recording the presentation.  I only did the demo to create the screenshot application I included in the presentation, I didn’t include the portions where I demo exploring the xlsx file by renaming it to zip file extension or browse the VertiPaq folders that get created in your %temp% directory in Windows (watch out for these because they can take up a lot of space over time – I currently have 16 VertiPaq folders consuming 1.31GB of space).image

For the follow up on this presentation you can check out my previous posting – MN Microsoft BI User Group 2011 Q1 Follow up.  You can download the PDF presentation material by clicking on the PowerPoint screenshot on the right-hand side which I have stored on my SkyDrive account.

This video file is approximately 27 minutes long.  I didn’t realize my demonstration was that long, next time I will have to time this out so I can make sure I have plenty of time to show the complete demo and have time to resolve any technical difficulties.  The file is approximately 43MB if you download it and I did make this in HD mp4 quality, so it should be good to watch.  This was my first time using Camtasia to create a screencast, so hopefully everything worked out okay (really sweet product).

Leveraging PowerPivot Demonstration from Dan English on Vimeo.

Hope you enjoy the video, please feel free to post comments and questions.  Later.

Update (4/5/2011):  I have the PowerPoint presentation on SlideShare if you don’t want to download it and just want to view the slides.

Update (4/6/2011):   In regards to the CSV file I simply used the Export wizard in SSMS to create the file.  In order to do that I create the following view in the ContosoRetailDW database to reference:

CREATE VIEW [dbo].[vw_SalesBudgetData]

AS

SELECT DateKey, ChannelKey, StoreKey, ProductKey, SalesQuantityQuota, SalesAmountQuota,

FROM dbo.FactSalesQuota WHERE (ScenarioKey = 2)

When I exported the file I also used the Vertical Bar for the delimiter and included the column headers.

Posted in PowerPivot | Tagged: , , , | Leave a Comment »

Round 2: Calculating Previous Year Marketing Calendar Values with DAX

Posted by denglishbi on October 9, 2010

So a couple of weeks back I did the posting Calculating Previous Year Marketing Calendar Values with DAX where I talked about a solution for getting at previous (last) year values in DAX with Marketing (Retail) Calendars.  For the most part the solution worked, but as I looked at the results a bit closer I noticed that it eventually broke down and failed.  Why is this?

Well the answer should have been obvious and I have ran into this before, but eventually these 445, 454, 544, or 13 period calendars run into a small issue that they need to deal with.  The issue is that these calendars only deal with 364 days (13 periods x 4 weeks x 7 days = 364 days).  So there is an extra day that we eventually need to deal with and then we run into the leap year and all of this eventually creates the mysterious week 53!

This situation is documented here on the National Retail Federation web site “4-5-4 Calendar”:

An Explanation of the 53-Week Year

Dividing the retail calendar into 52 weeks of seven days each, or 364 days, leaves an extra day each year to be accounted for. As a result every five to six years a week is added to the fiscal calendar.

If I go back to my original solution and scroll a little further down in my PivotTable I would have seen this since in Feb 2008 we run into a leap year.  This eventually causes the solution to bomb out when we get into the 2009 and 2010 comparisons and so on.

image

So, now what do we do?  Time to regroup and look for a solution to work around this.  After some brainstorming I realized that we just needed to expand on the original solution.  This means that we need to tell each time frame that we are dealing with how it relates to the previous period.  So not only at the day level like I originally setup, but also for the week, month (period), quarter, and year.  So we go back into our table and setup some additional columns:   

image

And then populate these dates with values:

image

Make a few tweaks to the calculation to evaluate the different time frames and reference the date ranges that we just defined (I don’t show it, but we also updated the table in the PowerPivot Window to include these new columns into our model):

image

PY Sales Amount =

IF(COUNTROWS(VALUES(DimRetailDate[Reporting_Year])) = 1,
CALCULATE([Sum of SalesAmount], DATESBETWEEN(DimRetailDate[PK_Date],
IF(COUNTROWS(VALUES(DimRetailDate[Reporting_Quarter_Of_Year])) = 1,
IF(COUNTROWS(VALUES(DimRetailDate[Reporting_Month_Of_Year])) = 1,
IF(COUNTROWS(VALUES(DimRetailDate[Reporting_Week_Of_Year]))=1,
IF(COUNTROWS(VALUES(DimRetailDate[Reporting_Day_Of_Year]))=1,
FIRSTDATE(DimRetailDate[PY_Date]),
FIRSTDATE(DimRetailDate[PY_Reporting_Week_Start_Date])),
FIRSTDATE(DimRetailDate[PY_Reporting_Month_Start_Date])),
FIRSTDATE(DimRetailDate[PY_Reporting_Quarter_Start_Date])),
FIRSTDATE(DimRetailDate[PY_Reporting_Year_Start_Date])),
IF(COUNTROWS(VALUES(DimRetailDate[Reporting_Quarter_Of_Year])) = 1,
IF(COUNTROWS(VALUES(DimRetailDate[Reporting_Month_Of_Year])) = 1,
IF(COUNTROWS(VALUES(DimRetailDate[Reporting_Week_Of_Year]))=1,
IF(COUNTROWS(VALUES(DimRetailDate[Reporting_Day_Of_Year]))=1,
FIRSTDATE(DimRetailDate[PY_Date]),
FIRSTDATE(DimRetailDate[PY_Reporting_Week_End_Date])),
FIRSTDATE(DimRetailDate[PY_Reporting_Month_End_Date])),
FIRSTDATE(DimRetailDate[PY_Reporting_Quarter_End_Date])),
FIRSTDATE(DimRetailDate[PY_Reporting_Year_End_Date]))))
,BLANK())

And now we go back and take a look at our results:

image

Tada! We are back in business. And this will work at the other levels as well (here we use the Reporting Year and Quarter):

image

So now we are back in business. Just needed to expand out the original solution into each of the different time frames that we wanted to analyze and make sure that we have the previous year mappings setup according to our business rules in the table we are referencing.

Posted in PowerPivot | Tagged: , , | 2 Comments »

Calculating Previous Year Marketing Calendar Values with DAX

Posted by denglishbi on September 24, 2010

UPDATE (9/27/2010): watch out for the mystery 53 week that occurs every 5 years or so.  formula does not account for this at all and will fail eventually.

UPDATE (10/9/2010): I have an updated post that has a resolution for handling the 53 week Marketing (Retail) Calendar setup – Round 2: Calculating Previous Year Marketing Calendar Values with DAX.

When it comes to reporting typically we will use the common calendar year that starts on January 1 and goes through December 31 with the standard months.  We might even be faced with a fiscal calendar that starts on July 1 and ends June 30 the following year.  In both of these cases we are sticking with the set days in the months and are not doing anything special.  But what happens when we start to use a custom calendar for marketing or retail purposes like a 445, 454, 544, or possibly 13 periods of 4 weeks each?  Will this have any impact on how we need to create our metrics to report on our data?  The answer – Yes.  Because in this case we are not simply evaluating September 1 of this year to September 1 of last year.  Nope.  We are actually evaluating it to September 2 of last year which is the same day of the week.

So now the challenge is how can we do this and how can we do it with PowerPivot using DAX?

Disclaimer: I cannot guarantee that this calculation and example will work the same as it did for me, so please always verify the results and add any additional checks that may be needed, this is strictly setup as a demonstration.

For this demonstration I am going to use a marketing calendar using a 454 week setup that starts with the first week in March.  To create this date dimension table I leveraged the functionality in BIDS to create a time table in my data source to generate this table for me – Creating a Time Dimension by Generating a Time Table.

image

Once this table was created for me in my SQL Server database I went into that table and created a new column in the table that I called PY_Date.  This column is going to contain the prior year equivalent date for the dates that are stored in this table in the PK_Date field that was generated by the wizard.  I then went ahead and populated this column with the prior year equivalent dates so we now have data that looks like this in the table (I am just displaying the two date columns here):

image 

This new column is what I call the ‘silver bullet’ that will be required to reference in our DAX formula.  I am kind of blowing it by introducing this first, but I will continue on and show a couple of DAX formulas for a typical calendar and then how it will be adjusted to work with the marketing calendar.

Once we have this table setup and have some facts that we want to relate to this new time (date) dimension we go ahead and load this into PowerPivot and setup our relationship between the two tables.

image

Now we are ready to setup a PowerPivot PivotTable to analyze our data and create additional measures.  In this first example I place the Year Name and Month on the Rows and Sales Amount in the Values.  After this I then create a new measure to calculate the Previous Year Sales Amount leveraging the CALCULATE, DATESBETWEEN, FIRSTDATE, DATEADD, and LASTDATE functions:

image

PY SalesAmount =CALCULATE([Sum of SalesAmount], DATESBETWEEN(DimRetailDate[PK_Date],FIRSTDATE(DATEADD(DimRetailDate[PK_Date],-12,MONTH)),LASTDATE(DATEADD(DimRetailDate[PK_Date],-12,MONTH))))

Here are the results we end up with:

image

Now this works create when you have standard reporting that uses the typical set month periods.  What does it look like when we swap out the members on the Rows with the Reporting Year Name and Reporting Month values that are referencing our marketing 454 week calendar setup:

image

As you can see this same formula will not work.  So now we take a look at using that additional column that we setup earlier for the equivalent prior year value for our dates and we modify the original calculation slightly:

image

PY Sales Amount =CALCULATE([Sum of SalesAmount], DATESBETWEEN(DimRetailDate[PK_Date],FIRSTDATE(DimRetailDate[PY_Date]),LASTDATE(DimRetailDate[PY_Date])))

Now let’s take a look at how this looks with the data:

image

There we go.  That is what we want.  Now we are looking at equivalent dates.  So we need one measure to use with standard calendar reporting periods and a different one if we are reporting against equivalent time periods like using a 454, 445, 544, or some other type like 13 equal 4 week periods.  The secret ‘silver bullet’ is the additional previous year equivalent date column in the time (date) dimension table that is utilized to make the results we need.

Warning: As stated above I cannot guarantee that this is going to work the same in your environment and you might need to include some additional IF logic to do some additional checks to return BLANK().

Enjoy!

Posted in PowerPivot | Tagged: , , | 3 Comments »

 
Follow

Get every new post delivered to your Inbox.

Join 1,758 other followers