Have you ever wanted to monitor the performance of a SQL Server Analysis Services (SSAS) server but did not want to create a tool from scratch and your company did not allow you to purchase one of the few commercial tools available?
Join me in this session as I show you how to use Windows Perfmon, SQL Server Extended Events, SSAS Tabular and Power BI Desktop to build a performance monitoring tool for a SSAS server.
We will start by reviewing some of the SSAS performance metrics you can collect. Then we will learn how to use Windows Perfmon and SQL Server Extended Events to collect performance counters and query execution information from a server and store it in a SQL Server database. Next we will use SSAS to build a Tabular model from the information collected. Finally, we will use Power BI Desktop to present this information.
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
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:
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.
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.
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.
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 Verrel, me, Eric Peterson, and Peter Krall.
Congratulations Peter Krall for winning!
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.
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 listento the judges feedback. Don’t make the same mistake someone else did.
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.
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!
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.
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?
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.
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.
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.
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.
The following pictures show my environment after I changed the formula bar’s font size to 10 and 14.
Font Size 10
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.
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.