Excel/SSAS Tip: Viewing the MDX behind PivotTables on SSAS Cubes

I was connecting to a Analysis Services cube in Excel 2010 and was curious to see the MDX behind the PivotTable. This is just a quick way within Excel if you want to see the MDX behind your PivotTables as well as some adding some more features to your PivotTable.

First, you will need to install “OLAP PivotTable Extensions,” which can be found here:

http://olappivottableextend.codeplex.com/downloads/get/691712

After you download the setup file, follow the steps to complete the installation.

Image

Once you’ve installed the extensions, connect to your Analysis Services cube and right-click anywhere inside your PivotTable. You will notice a new menu option down at the bottom named “OLAP PivotTable Extensions…”

Image

Note: If you have various versions of Excel (2003, 2007, 2010, etc.) installed on your machine, this option will be available in all versions

Once you click on this, you will be able to see all the extensions provided from the add-in’s. For purposes of this blog, the PivotTable’s MDX will be able to be seen in the third tab titled “MDX.”

As you can see, you can also view all the calculations that you define in the PivotTable in the first Calculations tab.

This add-in seems to include a lot of nice features that I haven’t had a chance to completely explore outside of the MDX capabilities, which is what I wanted to focus on in this tip.

I may post an additional post once I explore the extensions further.

WORKBOOK AUTOMATION IN TABLEAU’S TABCMD

Recently, I was assigned to our company’s Employee Engagement Survey process. I was responsible for finding a more efficient way of producing and automating hundreds of reports and ensuring that managers and supervisors across the company received their respective reports accurately and in a timely fashion.

It took me weeks and weeks of digging around the Internet, speaking to various people, and trial and error to finally get it working. Scouring through posts and forums, I quickly realized that I was not alone and this was a question that was greatly sought after by many Tableau users. So in this post I will consolidate everything I’ve learned along the way.

Software needed:

  • Tableau Server 8.0
  • Tableau Server’s command line tool, tabcmd — this comes with Tableau Server 8.0, but needs to be installed separately
  • Febooti Command line e-mail — third-party application that is used for e-mail distribution of the reports

Installing Tabcmd

The location of the Tabcmd Installer will vary depending on your operating system.

Image

*[version] refers to the version of Tableau Server you have installed on your machine. I used the latest version 8.0, which is shown throughout my screenshots

Image

Double-click TabcmdInstaller to begin the installation

Image

According to Tableau’s website, the command line utility does not need to be installed on the same machine as Tableau Server. Also, Tableau recommends that tabcmd be installed in your C:\ (i.e. C:\tabcmd)

The installation is fairly straight-forward, but if you have any additional questions please feel free to post them in the comments section.

Using the Command Prompt:

Once tabcmd is installed open your Command Prompt (as Administrator) from:

Start Menu -> Programs -> Accessories -> Command Prompt (Right-click and select “Run as administrator”)

Image

Once the Command Prompt is open, you will need to locate tabcmd. This will vary depending on the location of tabcmd.

Image

If you are running the tabcmd utility on a separate machine from Server:

Image

In order to log into Tableau Server, the first thing you will need to do is login the server with a user account with the appropriate security access in order to make changes to the dashboards and/or reports.Image

Normally, the command line to login is:

Image

Where http://tableau.company.com would be your Tableau Server URL, “admin” would be your username and “password123” would be your password. However, I believe it Is best practice and for security purposes, to have tabcmd read your password from a separate text file that will need to be created.

In a text editor (i.e. Notepad or Notepad++), type your password and save it as pwd.txt, in the same folder where your batch file is.

Image

Now, instead of exposing your password, our new command line will be:

Image

Using tabcmd server commands, –s is the abbreviation for –server (long option) before our Tableau server URL (both are acceptable). We also use –u or –user (long option) before our username and –passworld-file to access the text file with our password we created earlier.

You can find a more comprehensive guide on tabcmd Commands at http://onlinehelp.tableausoftware.com/v7.0/server/en-us/tabcmd_cmd.htm#id7eedf1d3-9ae7-4119-9d68-efc5632ca75b__id4062796f-aa16-44b8-b6fe-734af91ddd0d.

If you logged in successfully, you will see something similar to this:

===== Creating new session
===== Server: http://tableau.company.com
===== Username: admin
===== Connecting to server…
===== Logging in…
===== Login Succeeded.

Creating the Reports

For my company our Tableau dashboards contained an Employee_ID parameter, multiple pages per report, and were delivered in PDF format.

Note: For the sake of our example, our parameter will be EMPLOYEE_ID and the ID we are generating the report for is: “123456”

The command line to export our report with ID “123456” for our EMPLOYEE_ID parameter is:

Image

Here, “export” exports a view or workbook from your Tableau Server and saves it as a file. In our example, our report is saved as PDF file “Report123456” in the “C:\My Reports” directory.

It is very important to note that the string after the “export” command is the string that is after your workbook URL in Tableau Server.

For instance,

https://tableau.company.com/views/My2013Dashboard/FirstPage

The bold face section of the URL is what would go after the export command 

The ?EMPLOYEE_ID=123456 after the URL sets the value for the parameter in the workbook. Each PDF will be unique according to the parameter value specified in the URL. In this case, the report returns back the results for the employee with ID 123456.

I used the –fullpdf command because I was exporting a workbook that was published with the sheets as tabs, so this captures and produces the PDF containing all the sheets in the report.

The –pagelayout command sets the pages orientation of the exported PDF.

The –timeout 300 (–timeout SECONDS) command tells the number of seconds the server should wait before processing the login command.

Note: You can point and send the report to any existing directory you specify in the command

E-mail distribution of the reports

Rather than posting each manager or supervisor’s individual report onto a shared collaboration site and having them download it, I decided to make it a bit easier by attaching the PDF file and e-mailing each manager/supervisor their report.

Unfortunately, at this time Tableau Server does not have a native function to do this, so I came across a fantastic command line e-mail utility that does the work in a flash, Febooti command line e-mail utility (http://www.febooti.com). They do offer a free 15-day trial on their website, but the tool is only $129.00 to purchase with a 30-day money back guarantee.

Febooti Command line e-mail can send e-mails with unlimited number of attachments, send e-mails using any text encoding, optional reply-to-email addresses, and much more.

Once you purchase Febooti Command line e-mail, they provide a product key and the install is straightforward.

Febooti Command line e-mail installs in,

C:\Program Files (x86)\Febooti Command line email\

Similar to using tabcmd, we open up the command prompt and navigate and open

C:\Program Files (x86)\Febooti Command line email\febootimail.exe

Image

The syntax I used to send out the e-mail was:

Image

-SMTP specifies the outgoing mail server address

-TO specifies the destination e-mail address

-FROM specifies the e-mail address where it is being sent from

-TEXT precedes the body of the e-mail and what you want as the body of the e-mail

I created a separate body.txt file in a text editor (similar to pwd.txt above). This text file is also saved in the same directory as the batch file.

Conclusion and Thoughts

For my purposes, I ended up creating 2 separate batch files, one to handle the creation of the reports and one to handle the e-mail distribution of the reports.

The reason for this was because of my security access roles. I ran the report creation batch on my local machine, but had to run the e-mail batch connecting remotely to the server where Tableau Server was installed.

I had over 700+ manager/supervisor IDs to filter on for the parameter value and over 400 to be e-mailed individually. With this automation process, what took weeks to complete with 4-5 people working around the clock with the potential of human error, was now reduced to 1 person to double-click the batch files and a total of 5 hours (this time can be drastically reduced depending on the speed of your machine) of run time for the creation of PDF reports.

For the batch’s retrieval of the parameter values and e-mail addresses, I created a text file, or “master list” containing all the manager IDs and their respective e-mail addresses.

I hope this post helps you and please feel free to post any questions or comments.

Thanks for stopping by.

Software, Tableau. Using Tabcmd. Retrieved June 22, 2013, from http://kb.tableausoftware.com/articles/knowledgebase/using-tabcmd

Software, Tableau. Tabcmd Commands. Retrieved June 22, 2013, from http://onlinehelp.tableausoftware.com/v7.0/server/en-us/tabcmd_cmd.htm#id7eedf1d3-9ae7-4119-9d68-efc5632ca75b__id7cb8d032-a4ff-43da-9990-15bdfe64bcd0

Treadwell, Kate. Automating Dashboard Delivery Using Tabcmd. Retrieved June 22, 2013, from http://www.interworks.com/blogs/ktreadwell/2012/09/07/automating-dashboard-delivery-using-tabcmd

Command line, Febooti. Retrieved June 22, 2013, from http://www.febooti.com/

POWER VIEW 2013 VS. TABLEAU 8.0 DESKTOP

In the wake of all the emerging business intelligence report tools, decisions on choosing the right report authoring and visualization tool for you can get pretty murky. Today I provide a brief breakdown, from my experiences, between two BI analytics tools I have had a chance to work with in the last few months: Tableau 8 Desktop and Microsoft Power View 2013. I will attempt to give you a high-level overview of both without making this post too long.

First, a little background of the two BI analytics tools — a Tale of the Tape, so to speak.

Tableau Software is quickly emerging as a heavy-hitter in business intelligence and has been announced as a “leader” in Gartner’s 2013 Magic Quadrant for Business Intelligence and Analytics Platforms report (http://www.tableausoftware.com/gartner-magic-quadrant).

I will be focusing on Tableau Software’s Desktop (version 8) product. Tableau 8 Desktop is a data visualization application that allows users to seamlessly create interactive reports, dashboards and visualizations all within minutes. Tableau 8 Desktop features an intuitive, clean and easy to use interface for users, the ability to bolt on to various data sources, and a variety of charts to choose from. I like to think of it as the Apple of BI tools when it comes to look and feel (Not so much in line with Apple’s locked down OS and rigidness).

On the flipside of things, we have Microsoft’s newest report authoring tool, Power View 2013. It is worth noting that Power View is only available in the Office Professional Plus and Office 365 Professional plus editions.

Power View is Microsoft’s crack at introducing users with a data analysis and reporting product that is simpler and more accessible than their predecessor: Microsoft Reporting Services.

A focal point of Power View is its accessibility and ease for the users. Power View allows users to author their reports and dashboards within their native web browsers. Additionally, Power View is a breath of fresh air for users who may struggle with the intricacies (i.e. T-SQL, SELECT statements, WHERE clauses, etc.) of Reporting Services and is a breeze for anyone with basic computer skills.

OK, that’s great and all, but which one is better?

Some noteworthy benefits of Tableau that I’ve come across are:

  • Visuals are quick and smooth and it allows you to choose among a myriad of charts that best suit your data
  • The charts and visuals default to best practices (i.e. line charts for trends over time), so even if you may not be a visual analytics expert, Tableau will find a good model for you
  • Tableau is easy to set up and can bolt on to many data sources, straying away from the expected complexities of traditional OLAP cubes (You can leave the predefined schemas and pre-aggregation of data at home!).

Don’t worry, I haven’t forgotten about Power View. Power View packs a punch itself.

With Power View, users have access to:

  • A familiar interface for users through the beloved interface of Microsoft Excel, with many of the same features and functions we all know and love
  • Allows less-than-power users to skip SELECT statements with JOINS and WHERE clauses and all the headaches from querying databases
  • This is all obviated with its use of the BI Semantic Model
    • Briefly, the BI Semantic Model (BISM) is a model of your data and serves to hide the nuts and bolts of the operation. It offers a place to store calculations and business logic, which can in turn be used by several reports. Also, various data sources can be feed a single BISM.
  • Microsoft is always looking to improve and listening to its users, which brings us frequent updates

Let’s get to it.

Both tools have their strong points and like all else, there are some spots that could use some improvement.

Round 1: Charts/Visuals

Power View is still pretty limited and basic on the choice of visuals and charts to choose from. Tableau, on the other hand, had a huge selection of charts in version 7 and has added more in its latest version 8.0. The edge here for Tableau Desktop is the fact that it defaults to the best fitted chart for the data you are looking at, while also providing countless chart customizations.

Image     Image

Power View (left); Tableau 8 (right)

Winner: Tableau 8 Desktop

Round 2: Ease of Use

As I mentioned previously, Tableau 8’s browser interface is clean, smooth and reminds me of the buttery flow of Apple products (In a good way). Although, I love the way everything is laid out, there is still a slight learning curve as to where everything is and what you can do with the copious amount of features available.

Power View on the other hand is perfect for the not-so-power user and allows users to pull meaningful context in a few clicks because of its familiar interface within Excel. (Not to say Tableau can’t, just saying that the learning curve is slightly steeper than Excel)

Image

Image

Power View (top); Tableau 8 Desktop (bottom)

Winner: Power View

Round 3: Practicality and application

Both Tableau 8 Desktop and Microsoft Power View are great ways to consume and present data and both have their short-comings and needed areas of improvement. However, having used the products quite regularly over the last 6 months or so, I have a clear cut choice as to which one I’d prefer to use (for now).

Microsoft’s vision for Power View was to give users an “interactive data exploration and visual presentation experience for ad hoc reporting” and I think they are on the right path. But unfortunately, it just isn’t there yet and lacks a lot of functionalities I’d like to see in the coming versions. Some of which include, the ability to customize my charts more and personalize labels and key data points I may want to bring attention to. Also, the fact that I cannot share my dashboards or visualizations and have other users interact with what I’ve built is a huge downside for me. The ability to customize is a huge selling point as well in Tableau, as I can edit things from colors, to labels orientation, what data to display in the information tool tips, the optimal sizing of charts in my dashboards, etc. Dashboards and visualizations built in Tableau Desktop can also easily be shared and interacted with through Tableau Reader, Tableau Server, blogs, and mobile devices.

Winner: Tableau 8 Desktop

Tableau 8 Desktop’s “knockout punch” is its strength and abilities for data exploration. Tableau provides end-users the freedom in an environment that leverages existing IT infrastructure and recognizes that not all data is in the enterprise data warehouse.

The goal of BI and visual analysis is to bring the data to life, create a logical story board and answer critical questions.

Our tool of interest must allow us to filter and focus on areas of interest, group and aggregate data, create on-the-fly calculations, and present new ways of interpreting the data that may not be seen at first glance. Tableau 8 Desktop has truly grasped rapid-fire business intelligence and fulfilled these objectives.

Each application has their place and has provided us with fresh and unique ways to consume data, obviates the user to understand the intricacies of SQL and simplifies report authoring.

So, from my experiences with the two applications, I would have to choose Tableau 8 Desktop as the winner in this duel.

I’m sure the two will meet again in the future as updates and new versions roll-out.

Thanks for reading.

Hello World!

Welcome.

In my blog, I will be posting interesting and useful (hopefully!) tips/tricks/tidbits that I come across on a day to day basis in my world of business intelligence development. 

Stay tuned! 

Image