Advanced T-SQL Webinar / Free Training

Thank You!

First of all thank you to everyone who attended my T-SQL presentation on 5/10/2016. We had over 1400 people registered for the training and 871 people joined and listened in, that’s a lot of people! I also received a lot of good and encouraging feedback so thank you for that as well.

Resources

The number one question I received is will the T-SQL scripts be available for download. Of course they will. If you want to download the T-SQL scripts that I used for my presentation you can find those here:

Free Recording:

The full one hour webinar that I did for Pragmatic Works on 5/11/2016 can be found here:

Questions and Answers:

I just received the list of questions from the webinar so I will get this section updated in the next couple days. I wanted to go ahead and post this blog so you could have access to the SQL Scripts.

Question:

Les Said: BTW, best presentation EVER!!!! Very clear and straight to the point in each case. Congrats!!!

Answer:

Thank you!, Best question ever!

Question:Recursive CTEs in SQL

Ken Asked: Should anchor be unique? What happens if there are two rows returned in the Anchor member?

Answer:

Hey Ken, the anchor member does not need to be unique here. In our example we were specifying that the CEO is at the first level or Level 0. If there are multiple members in the Anchor then multiple members would show up at Level 0.

Question: Pivot in SQL

Travis asked: Can you do multiple columns, such as minutes and cost?

Answer:

Travis I believe you are asking about the pivot example that I showed in my webinar. The answer is yes, you can definitely do a double pivot or pivot on multiple columns. I will write a blog on how to do this in the next week, so please check back!

Question: Merge in SQL vs. SSIS Update

Vineet asked: How does the merge statement compare to updates done in SSIS.

Answer:

The only native built in update capability that we have in SSIS is the OLE DB Command so I assume that this question is in regards to comparing the merge with the OLE DB Command. The merge pattern will perform light years better than doing updates in SSIS using the OLE DB Command. The merge pattern I showed in the webinar is a very popular design pattern used for loading data warehouses.

Question: SubTotals with CTEs in SQL Server

Vineet asked: Can you share CTE Examples on your blog.

Answer:

Hi again, you were not the only one to ask this question as I have also received a few emails requesting this example. I will write a blog post with an example on this in the next week, so please check back!

Question: What SQL Books would you recommend?

Answer:

There are a lot of great SQL Books out there. I will stick with one author here. I have three books by Itzik Ben-Gan.

  • T-SQL Fundamentals
  • T-SQL Querying
  • T-SQL Programming
Advertisements

MDX CALCULATION WIZARD – 12 Awesome MDX Calculations

12 MDX Calculations for your SSAS Cube:

If you have suddenly found yourself in a situation of having to improve the functionality of your cube with MDX Calculations but you don’t know MDX then you have come to right place!  Learning MDX can be a difficult task and one that can take some time, and time may not be something you have!

MDX Calculation Builder Wizard

Well I have great news for you! BIxPress by PragmaticWorks has an MDX Calculation Builder that will help you improve your cube functionality without having to learn MDX! The Wizard will walk you through a series of simple steps and then generate the MDX code for you and add it to your SSAS Solution and voila!

FREE TRIAL HERE: BIxPress Free Trial

Posted below you can find the different calculations and sets included in the wizard:

image

Thanks for looking!

MDX ISEMPTY function to return products with no sales.

Have you ever wondered how can I return all my non selling products in MDX? Well, if you found your way to this post hopefully you have! Fortunately this is actually quite easy to do.

ISEMPTY FUNCTION

The ISEMPTY function returns a BOOLEAN value. If the cell evaluated is empty the value of TRUE is returned and if it is not empty then the value of FALSE is returned. This is a great function to nest inside other functions. Let me show you how we can use this function along with the filter function in MDX.

MDX FILTER FUNCTION

Here is a simple MDX query that returns [Internet Sales] on columns and [Products] on rows.

image

In this query we can quickly see we definitely have products that have never had internet sales. Now how can we return only the products that have never had sales? Well there are many ways to do this of course but I want to show how to use the Filter Function along with the ISEMPTY function for this example.

image

Here I use the set of members returned from [Product].[Product].Children as the first parameter in the filter function. For the second parameter in the filter function I use the ISEMPTY function on [Measure].[Internet Sales Amount]. This means that only products from parameter 1 that have no sales will be returned in the final result set. In the result set above we can see that only products without sales are being returned in the final result set. Easy right? Smile 

As always thanks for looking!

Create and Deploy Perspectives in SSAS

SSAS Perspectives

I’m trying something a little different with this blog. I have created a new channel over on YouTube, please feel free to search MitchellSQL and subscribe to my channel! 🙂 Instead of writing everything out in this blog post I created a quick 8 minute video. Please watch the video and let me know if you prefer videos or blog post! Thanks for looking!

Create and Deploy Perspectives in SSAS

MDX NON EMPTY KEYWORD VS NONEMPTY FUNCTION

Non Empty vs NonEmpty

Hey everyone, in this blog I want to address a very common MDX Question. What is the difference between the NON EMPTY keyword and NONEMPTY function? To take it a step further which one should you use?

Non Empty keyword VS NONEMPTY Function.

The big difference between the NON EMPTY keyword and the NONEMPTY function is when the evaluation occurs in the MDX. The NON EMPTY keyword is the last thing that is evaluated, in other words after all axes have been evaluated then the NON EMPTY keyword is executed to remove any empty space from the final result set. The NONEMPTY function is evaluated when the specific axis is evaluated.

Should I use NON EMPTY keyword or NONEMPTY function?

Ok Mitchell, so you told me when each of these are evaluated but really you haven’t told me anything up until this point. Can you tell me which one I should use already? Well, unfortunately, it depends. Let’s walk through an example of each using the BOTTOMCOUNT function.

BOTTOMCOUNT FUNCTION with NON EMPTY Keyword

In this example I’m returning the bottom ten selling products for internet sales. Notice that I have returned all products that have no internet sales, this is not necessarily a bad thing, maybe you want to return products that don’t have sales.

image

However if you don’t want to return these products then we can try using the NON EMPTY keyword. In the below example you can see the results when I add NON EMPTY to the ROWS axis.

image

WHOOOAAA, what happened?? A lot of people would have expected the results here to show the bottom ten products that DID have sales. However, that is not the case, remember that I said the NON EMPTY keyword is evaluated LAST after all axes have been evaluated. This means that first the bottom ten selling products which have $0 in sales are first returned and then the NON EMPTY keyword removes all that empty space from the final result.

BOTTOMCOUNT function with NONEMPTY function.

So let’s try this again, if you want to return the bottom ten products that had sales then we must first remove the empty space before using the BottomCount function. Take a look at the code below:

image

In this code we first remove the empty space before using the BOTTOMCOUNT function. The result is we return the bottom ten products that had internet sales. Once again neither one is right or wrong here it just depends on what you want in your final result.

NON EMPTY Keyword vs. NONEMPTY Function – Performance

There is a very common misconception that the NONEMPTY function always performs better than the NON EMPTY keyword, however this is not true. My good friend Dustin Ryan has a great blog discussing this point. Head on over to his blog here: SQLDusty

Thanks for taking a look at this blog and I hope you enjoyed it!

MDX 101–Free Training Video Q&A

pablo (1)

First of all I want to thank Pragmatic Works for once again allowing me to have the opportunity to present and for so graciously hosting me. Secondly thank you to everyone who attended the MDX presentation.

Great News!, If you were not among the 500 attendees who made it to the live training you can view the recorded version here: MDX 101.

Want to skip the reading and watch my Q&A Video? MitchellSQL

MDX 101 Question and Answer

This ended up being a slightly longer post than I expected. Below is a summary list of the questions answered in this post.

  1. What tool did you use for deploying MDX code?
  2. How can you handle divide by zero errors in MDX?
  3. How can we use MDX in SSRS?
  4. Why does the children function not return the [All] member?
  5. How long does it take to become an MDX Expert?
  6. What books would you recommend for MDX?

We had a lot of great questions, many of which I will quickly answer here and some of which will lead to a series of follow up blogs. So let’s get started!

Q: What tool did you use for deploying the MDX code and can you provide the link?

A: Absolutely. The tool I used is a free tool called BidsHelper, you can download BidsHelper from CodePlex. For your convenience here is the link: BidsHelper

Q: How can you handle divide by zero errors when doing division, like in your Profit Margin example.

A: Ahh, great question. We can use the IIF function along with the IsEmpty function in MDX to check the denominator first to make sure the value is not empty or equal to 0. Here is an example:

First, here is the original query.

[Measures].[Profit] / 
[Measures].[Internet Sales Amount]

Ok, so next we need to check if the denominator [Internet Sales Amount] to see if it is empty or equal to 0. If it is then we will simply return the value NULL, if it is not then we will perform the division.

IIF(
        ISEMPTY([Internet Sales Amount]) OR [Internet Sales AMount] = 0, 
        NULL,
            [Measures].[Profit] / 
            [Measures].[Internet Sales Amount]), FORMAT_STRING = "PERCENT"

Q: How can we use MDX in SSRS?

A: Another great question and I apologize for not being able to cover this in the webinar. I will do a follow up blog and provide some more details in the coming weeks. For now let’s discuss the basics.

  1. Create a data source in SSRS to your Analysis Services Cube.
  2. Create a data set that uses your Analysis Services Cube Connection.
  3. Finally use the Query Designer to build out your MDX. Alternative you can write your own MDX by selecting the expression button located to the right of the Query window, don’t forget to remove the = sign:

image

Q: Why does the children function not bring back the all member?

A: This is a very common question in MDX, the quick answer is that the children function is a navigation function. When we use the children function on any member in the cube only the children of the that member is returned in the result set. Let’s walk through an example.

The following MDX Query returns the [All Products] member from the attribute hierarchy “Category”.

image

Now we can take the children of the member [All Products]. In the attribute hierarchy Category we should expect the categories to be returned.

image

Q: How long does it take to become an MDX Expert?

A: Well, I’m not an MDX Expert yet so I would recommend asking my good friend SQLDusty who is a wizard with MDX and has a great blog with 30 – 40 MDX related posts! Click here to check out his website: SQLDusty

Q: Can you recommend a book for MDX.

A: There are many books out there and I have read through quite a few of them. Sometimes I need to have things explained from many different perspectives before I finally understand them Smile. Here are a couple books I recommend and why.

Microsoft SQL Server 2008 Analysis Services UNLEASHED. Although primarily SSAS focused this book has an awesome section on MDX, and in my opinion the author does a great job of explaining MDX. There are probably a hundred and fifty or so pages of MDX out of around 800 pages.

Analysis_Services_Unleashed

Expert Cube Development with SSAS multidimensional Models. Once again this is another SSAS focused book but has a good section on very commonly used design patterns. Chapter 6 is fully designated to calculations in the cube.

ExpertCube

Practical MDX Queries is also a great book for someone who is brand new to MDX. This book walks through all the basic MDX Functions.

Practical_MDX_Solutions

Once again thank you everyone for attending the webinar and for all the feedback received!

I’m Speaking! JSSUG 7/15/15. SSIS Performance Tuning

pablo (2)

Jacksonville SQL Server User Group

I am excited to announce that myself and Manuel Quintana (@SQLRican) will be presenting for our local SQL Server User Group on July 15, 2015. Our presentation topic is “SSIS Performance Tuning”  and we will covering some really cool stuff, so don’t miss it!

Thursday we will post additional blog references and resources as necessary. We will also answer any of the questions here on this post so please check back for valuable resources! Smile

You can check out Manuel’s blog here: https://sqlrican.wordpress.com/

Want to attend? RSVP here: http://www.meetup.com/Jacksonville-SQL-Server-User-Group/events/223331609/

Blog References and Additional Resources

SSIS Cache Transform

https://mitchellsql.wordpress.com/2014/06/20/using-cache-transforms-in-ssis-to-improve-performance-part-1/

https://mitchellsql.wordpress.com/2014/06/30/use-file-cache-and-the-cache-transform-to-help-mitigate-network-issues/

Multi Flat File Connection manager

https://mitchellsql.wordpress.com/2014/06/27/load-thousands-of-files-in-ssis-lighting-fast-multiflatfile-connection-manager/

Asynchronous Transforms in SSIS (Blocking)

http://tinyurl.com/oeb87fz

Retain Same Connection = True

https://mitchellsql.wordpress.com/2015/01/12/ssis-performance-tuning-the-for-each-loop-retain-same-connection/