Shabnam Watson's Blog

Analysis Services, MDX, DAX, BI

Calculated measures in Power BI Desktop reports connected live to SSAS Tabular.

May release of Power BI  Desktop adds the capability of adding new calculated measures to a report when you are connected live to SSAS tabular. This is great because before this release, once you connected live to SSAS you could not make any changes to the model. Official announcement

CalculatedMeasurePBIDLiveConnectTabularSSAS

Ideally the SSAS database has all the measures you need but now you have the capability to add new ones if you need to.

You can control the folder (table/measure group) under which the new measure shows up by using the “Home Table” option from the Modeling tab. I really like this feature as you can create copies of the same calculation and send them to different folders for ease of use.

Other SSAS related features

There is a request open for the same feature to be added for SSAS Multidimensional. Vote here:

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/19140997-calculated-measures-when-live-connected-to-mdx-cub

There is another request for adding calculated columns to a report connect to SSAS Tabular live. I recently ran into a situation where I wanted to create a custom column for sorting since it was not provided in the SSAS database and I did not have permission to modify the SSAS database.

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/19140907-create-calculated-columns-when-using-ssas-live-con

 

 

 

 

 

Clear Cache XMLA for SSAS Multidimensional

I often use the following XMLA code to clear database/cube/measure group cache in SSAS Multidimensional when I do performance testing between different MDX queries or versions of the same database. I am posting it here so that I can find it easily in future. You can use it to clear the cache at the database, cube or measure group levels.

<ClearCache xmlns=”http://schemas.microsoft.com/analysisservices/2003/engine”&gt;

YourDBName

YourCubeName 

YourMeasureGroupName

</ClearCache>

If you need to clear the cache at Database level you don’t need the lines for Cube and Measure Group. Similarly if you are clearing the cache for the whole cube you don’t need the line for the measure group.

 

 

 

 

PASS Summit Speaker Idol 2016

This year I got to participate in the Speaker Idol competition at PASS Summit. It was an honor to be chosen as a contestant. The whole experience was amazing.  I got to compete with 11 very strong contestants. I was the winner of the second day round and made it to the final round!  I learned so much and made so many new friends. Even though I did not win the final round,  I would do it all over again and recommend it to anyone who wants to improve their speaking skills.

Here is a picture of the final round as we wait for the verdict! From left to right:   Robert Verrelme, Eric Peterson, and Peter Krall.

_MG_0610.jpg

Speaker Idol final round. Picture by photos.wicktech.net

Congratulations Peter Krall for winning!

_MG_0619.jpg

 

What is Speaker Idol?

This was the third year of Speaker Idol at PASS Summit run by Denny Cherry. If you have not heard of it before, Speaker Idol is a speaking competition where each contestant gets to present a topic in 5 minutes. It starts with 12 contestants split into three initial rounds on first, second, and last day of PASS Summit. Each day the judges choose one winner and a runner up. The winner of each round makes it to the final competition. After the last initial round, the judges will pick one runner up from the three runner ups they chose previously to be the last finalist. The winner of the final round, gets a session at the next year’s PASS Summit without having to go through the abstract submission process. Congratulations to Peter Krall, winner of this year.

Why/How to sign up?

If you are planning to participate in next year’s Speaker Idol competition:

  • If you are not sure if you should sign up or not, you should. You have nothing to lose, except maybe a few good friends if they compete with you and lose 🙂 That’s a joke but seriously, there is nothing to lose. You will only gain. If you think you will be feeling uncomfortable presenting in a room full of speakers and judges, remember that is a good thing as it is a sign that this experience will make you a stronger presenter.
  • Make sure you don’t miss the announcement that comes out on Denny Cherry’s blog for when to sign up. It usually comes out shortly after the PASS Summit speakers are announced in summer so follow Denny either on his blog or twitter.
  • You must have some previous speaking experience to be eligible. If you are not already a speaker at your local user groups and SQL Saturdays, that’s a great place to start.
  •  For participation requirements and rules read Denny’s blog post here: https://www.dcac.co/syndication/pass-summit-speaker-idol-2016-is-a-go

How to prepare for the competition?

  • Once you are selected as a contestant, you have to pick a topic. Come up with a couple of topics and run them by some of your coworkers/friends. Pick something that you know well and are passionate about. Your knowledge depth and passion carry themselves as energy into your presentation and make it richer.
  • This is a speaking skills competitions. The speech topic itself does not matter much. Having said that since the ultimate goal is to become a speaker at PASS Summit, it is beneficial to pick a technical or community topic to showcase your capabilities and style as a future PASS Summit speaker.I think the judges did a great job of staying topic-neutral this year. I was the only person this year who had a BI topic, Code Reuse in MDX for Analysis Service.
  • Once you have your topic, practice as much as you can. Work is busy and life happens, but ideally you want to have your presentation/slides finalized and rehearsed many times before you head to the Summit. You don’t want to be in your hotel room practicing during the Summit days. During the Summit you should be either in the technical sessions learning or hanging out in the community zone, SQL clinic, vendor area, with friends, … to socialize. The human connections you make at the Summit are worth as much as the technical skills you learn if not more.
  • Read notes/blogs from last years’ contestants or chat with them if you see them at community events.
  • Watch videos of last years’ Speaker Idol on PASS TV and listen to the judges feedback. Don’t make the same mistake someone else did.
  • Watch the recap/orientation videos of last year competition:
  • Look at the twitter feed with #SpeakerIdol, there are lots of pictures there where you can get an idea of the room setup, the screens location, etc., assuming it will be in the same room next year.
  • Once you get to the conference, check out the actual room. For the last couple of years it has been in TCC Yakima 1.

Yakima.jpg

Everybody wants to win the ultimate prize!

The competition starts with 12 contestants and 1 person wins the ultimate prize of being the first speaker of the next year PASS Summit. So what about the other 11?  Well, there is no tangible prize. The prize is being part of the competition and what you learn! Having said this, I think every person should enter this competition with the goal of winning the prize so here is a collection of notes from the judges feedback and previous year contestants I talked to. These are not exact quotes. Keep in mind that many of the great speakers at events such as PASS don’t follow all of the following advice but they are not trying to win a speaking contest either. You are!

Slides /Demo

  • The title should be very clear. It is very important that it says what a presentation is about since many people choose sessions just based on the title.
  • Always say who the intended audience is and declare the level of the presentation before you start.
  • Don’t use bullet points in your slides. 🙂 (I know I am doing that here but this is not a slide.) No one had bullet point this year that I can remember, some participants had bullet points in 2015 and got negative feedback. Use an alternative way of showing talking points such as using SmartArt.
  • Use high contrast for the parts of the slide that you want to get people’s attention to. One way to do this is by highlighting or drawing arrows/circles.
  • Take people with color blindness into consideration. Don’t try to use green vs. red comparison.
  • You will be given a template to use, you don’t have to follow it.
  • Make font as big as possible (18 and up) and fill out all the white space.
  • Try to make your demos readable without having to use a zooming utility. Zoomit is great free zooming utility.
  • Make sure the results of a query (for example in SSMS) are large enough for the audience to be able to read.  (You can modify the font size in SSMS for the results grid.)
  • Whatever zoom method you use, be consistent with it.
  • If you use a zooming utility, hold the zoom a few seconds to let people see the contents. Don’t zoom in and out right away.
  • In SSMS, change the text highlight color (not the background color) from blue to yellow. This makes it easier to read the parts you highlight.
  • If you have screenshots in your slides, make sure they are readable.
  • Test the readability of your slides by practicing in the actual conference room during the breaks. Walk to the back of the room and see if you can read your slides.
  • You can also use this trick from Karen Lopez (one of the judges): Put up the slides on your laptop/monitor and walk back about 10Ft /3M back and see if you can still read your slides.
  • Transitions between slides and your demo are not your friend. If you do have to have them practice the transition using a real projector or second monitor so you can do this smoothly.
  • After a transition always make sure that the audience is actually seeing what you want them to see and that the transition has worked.
  • If your screen goes black or something goes wrong during your presentation (this happened to a couple of people), don’t stop talking. When you stop talking, you will lose the audience.
  • Provide attribution for photos/media in your slide
  • Don’t have typos!

Body language/Speed/Vocal variety/Story

  • Don’t wear your conference badge / jewelry during your speech. They can make a sound when they hit the microphone.
  • Place the microphone at the center of your collar/shirt, not to the sides. You can clip the microphone above your chest at the center if your shirt does not have a collar.
  • Put the microphone receiver in your pocket securely so it does not fall out during your speech. (Wear pants with pockets!)
  • Remember to turn on the microphone before you start. Check to see that the AV guy is actually paying attention to you, start by saying testing, testing, 1,2,3. He sometimes has to adjust your volume. The test time does not count towards your 5 minutes.
  • Turn with your entire body not from your neck. The audio does not work if you just turn your neck because your head will get away from the microphone.
  • When you show results of a query or program, slow down and let the audience see the results.
  • Use a clicker to move your slides. Bring your own clicker and practice with it.
  • Start with a happy intonation and keep it throughout. (Extra bonus if you have dimples and keep using them to your benefit :), you will get this one if you watch the 2016 videos when they come out. Enough said that one participant changed his twitter handle after this feedback. )
  • You have to grab the audience’s attention within the first 10 seconds of you starting to talk with something engaging. Practice your opening.
  • Tell a story with a logical flow so the audience can follow and tell them at the beginning what you are going to show them and what problem you are solving. One style of doing this is to tell the end of the story first and then come back and tell how you got there.
  • Place your laptop on the podium. Once you do that, don’t lean on the podium, don’t put your hands on it, and don’t hide behind it the entire time. Take a step away from the podium and plant yourself there if you don’t feel comfortable moving around. This brings us to the next very important bullet point.
  • The floor where you present can be squeaky! If possible find the squeaky parts beforehand and don’t step on them! The judges in the front row can hear the squeaks and it can be distracting. Most of the squeaky parts are located at the metal joints in the raised wooden floor where you get to present.
  • The larger the room the bigger hand gestures you should have.
  • Spread your gaze across the room. For the purpose of this competition, even if one side of the room is empty, look at it occasionally.
  • Don’t take your eyes off the audience except if you are doing a demo on your laptop and even then it should not be more than a few seconds.
  • Don’t turn your back to the audience to look at your slides. The room for the last two years, has had a podium in the middle and two screens on the sides so you won’t be able to see your slides without turning.
  • Don’t read your slides.
  • Have audience interaction if you can. Ask questions if you can.
  • Humor is great if you can add it in. This year Eric Peterson had a great StrecthDB topic and a very funny story that everyone enjoyed listening to.

Time Management

  • Don’t go overtime!
  • Most participants used an app on their cell phones. Some had a clicker that had a time display. Whatever you choose, practice with it.
  • Practice! Practice! Practice!
  • Don’t try to take a one hour session from a SQL Saturday or some other conference and condense it down to 5 minutes. Write this presentation from scratch.
  • Focus on 1 or 2 points to make.
  • Consider the pro/cons of a demo. You don’t have to have a live demo, you can put your demo into the slide as screenshots.

Being judged! Why did I do this to myself?

_MG_0282.jpg

Speaker Idol Day 2. Picture by photos.wicktech.net

Nobody likes being judged, publicly, in front of friends, strangers, and a camera! Right? Maybe that’s true if you look at it that way. On the flip side, you get better at speaking by practicing and learning from more seasoned speakers. All contestants had worked really hard and everyone I talked to was passionate about sharing their topic with the audience and everyone could use feedback to get better.  If you consider the feedback as free training, it does not hurt much! Sometimes we can’t easily see the areas we need to improve and  it takes another pair of eyes looking from the outside. I think everyone had done a great job so if you watch the videos you will see that the judges say they had to be nitpicking to find suggestions for improvements.

Thanks! Many of them!

Thank you Denny Cherry for putting this session together. Thank you judges for your time and feedback.

Thank you everyone who came to watch the sessions. It was great to see familiar happy smiley faces 🙂 in the audience! Thank you all friends and #SQLfamily for your support. Thank you Ginger Grant and Donald Wert for giving me feedback when I was choosing a topic.

_MG_0601.jpg

Picture by photos.wicktech.net

Special thanks to Rob Volk for helping with my live practice and enduring learning how to mimic functions in MDX over and over again!

Thank you  Tom Norman  and Todd Kleinhans for being new good friends. It was great to hang out with you between the rounds and talk about the competition.

 

 

How to change the font size of the formula bar and measures grid in SSDT for SSAS Tabular

When you create a new measures in SQL Server Data Tools (SSDT) for SSAS Tabular, you edit the formula in the top section of the user interface in a section called “formula bar”. The default font size for this formula bar is 8.

formula_bar_font_size_8_default

The small font size may be difficult to read for some people. The easiest and fastest way to change the font size is to click somewhere in the formula bar and use your mouse middle wheel while holding down the Control button on your keyboard. However, the font size will go back to the default size next time you open SSDT.

If you wish to change the font size permanently, you can use the Tools menu. Click on Tools –> Options –> Fonts and Colors –> Environment and choose “Business Intelligence Designers” from the drop down menu. Here you can change the font size. Your changes will not take effect until you restart SSDT.

tools_options_environment_fonts_and_colors

The following pictures show my environment after I changed the formula bar’s font size to 10 and 14.

Font Size 10formula_bar_font_size_10

Font Size 14

formula_bar_font_size_14

Notice how the font size change affects the table results grid and the measures grid below as well. Also if you right click on any table name tab, the font size for the pop up menu has changed.

formula_bar_font_size_10_tables_right_click_menu

Font size 10 was the easiest to work with for me. It made the formula bar easier to read while it kept the rest of the environment at a good manageable size.

MDX script keyword colors are back in SQL Server Data Tools (SSDT) release version for Visual Studio 2015

The MDX keywords in a cube script were not properly changing colors in the SQL Serve Data Tools (SSDT) Preview version for Visual Studio 2015. (14.0.60316.0). (See my previous post on this problem here.)

The different keyword colors make it easier to write, organize and read an MDX script inside a cube.

The following picture show Adventure Works script inside the latest SSDT.

SSDT_2015_GA

Here is the version information for the latest version of SSDT available at the time of writing this post.

SSDT_2015_GA_VersionInformation

 

 

World Wide Importers Sample Database

AdventureWorks sample database has been replaced by the new World Wide Importers sample database. You can download it from here:

https://github.com/Microsoft/sql-server-samples/releases/tag/wide-world-importers-v0.1

 

 

Looking forward to SQL Saturday Atlanta #521

This Saturday I will be teaching MDX basics. This one hour session is geared towards BI developers who are comfortable with SQL queries and have an good understanding of data warehouse concepts.

I will post the slide deck soon.

sqlsat521_web

PASS SUMMIT 2016

PASS_2016_240x400

SQL Saturday #522 Jacksonville

Here is the slide deck for my session “A SQL Developer’s Guide to MDX Basics” from SQL Saturday #522 in Jacksonville.

A SQL Developer’s Guide to MDX Basics

This session is targeted for those who have a good understanding of SQL language and are interested to learn how to write MDX queries against SQL Server Analysis Services (SSAS).

 

SQLSaturday2016

MDX keyword colors are missing in SQL Server Data Tools for Visual Studio 2015 Preview

MDX keywords are not changing color in the calculation script in SQL Server Data Tools – preview version for Visual Studio 2015. Hopefully this is a temporary issue and will be fixed in the release version. Having different keyword colors than the rest of the code makes it much easier to read, write and maintain the calculation script especially in larger implementations.

SSDT Preview for VS 2015

The following picture shows how the Adventure Works calculation script looks like when using SSDT Preview for VS 2015 at the time of writing this post. Everything is in black with a grey background.

SSDT_2015_No_MDX_Keyword_Highlighting

I am using SQL Server 2016 Release Candidate (RC) 1 and the latest version of  the SSDT which can be found here: https://msdn.microsoft.com/en-us/library/mt204009.aspx. The latest version of SSDT tested for this post is 14.0.60316.0. Please note that unlike previous versions there seems to be only one single download/install available for SSDT. Previous versions made a distinction between SSDT and SSDT for Business Intelligence (SSDT-BI).  Previously SSDT only supported SQL Server Relational database projects and SSDT – BI supported all business intelligence project types. This does not seem to be the case at least with the preview version.

SSDT – BI for VS 2013

Compare the keyword colors between the above picture and the following picture which shows the calculation script in SSDT – BI for VS 2013:

SSDT_2013_With_MDX_Keyword_Highlighting

This script is much easier to read! Fortunately previous versions of SSDT are still available for download from here: https://msdn.microsoft.com/en-us/library/mt674919.aspx. Since SSDT – BI for VS 2013 seems to work fine with SQL Server 2016 (RC) 1, that’s what I will be using for SSAS development until the keyword colors are fixed in SSDT for VS 2015.

SSDT – BI for VS 2012

A similar problem existed with SSDT – BI in Visual Studio 2012. With SSDT – BI 2012, the script changed the color of comments and all the MDX keywords to green! (See next picture).  As a result most developers opted to use Visual Studio 2008 even against SQL Server 2012 to avoid using the black and grey script. (SSDT – BI was called Business Intelligence Development Studio or BIDS in VS 2008.)

This issue was reported to Microsoft Connect and is currently marked as closed: https://connect.microsoft.com/SQLServer/feedback/details/841790/ssdt-2012-analysis-services-cube-project-calculation-page-color-formatting

I installed SSDT -BI for VS 2012 to see if the problem is fixed as reported by Microsoft Connect. The following shows the Adventure Works calculation script in SSDT -BI  for VS 2012:

SSDT_2012_With_Partial_MDX_Keyword_Highlighting

It seems like the green MDX keyword colors still exist at least in the version I was able to download.

Versions of SSDT for VS tested for this post

The following pictures show the version information for the different SSDT that I tested for this post. As mentioned previously, there seems to be only one download of SSDT available for VS 2015 that has combined the database project and BI project support.

SSDT for VS 2015

Here you can see the BI version under SQL Server Analysis Services and SQL Server Reporting Services and the relational database project version under SQL Server Data Tools.

SSDT_Feb_2016_Preview_For_VS2015_Version_Info

The following pictures show the types of project that become available with the download of the latest version of SSDT for VS 2015:

 SSDT – BI for Visual Studio 2013

I have not installed the non-BI SSDT.

SSDT_VS2013_Version_Info

 SSDT – BI for Visual Studio 2012

Here I have not installed the non-BI SSDT.

SSDT_VS2012_Version_Info