Recently, I experienced an issue trying to get a new installation of Microsoft Excel 2016 to connect to SQL Server 2014 Analysis Services Multidimensional. Whether I would try to connect directly from Excel, via the “From Other Sources” menu or via the Cube Browser in SQL Server Management Studio, I would receive an error that Excel was unable to connect to the database.
I verified that the service was running, firewall not blocking it, and credentials good. Read on for the solution to this issue.
Most introductions start with an explanation of cubes and MDX and then work their way toward the syntax and structure of a query and then start to cover the various syntax options in increasing difficulty. While that is a great approach, sometimes you just need to get going right away. Fortunately, MDX actually has a few good visual methods of querying an existing cube to access data without necessarily needing to know the syntax and theory (however, that doesn’t hurt!). This post is going to cover a few of the visual methods of querying a multidimensional analysis services cube.
All that being said, it does still help to understand the structure of your data and the various relationships before trying to query it. I’m using the Adventure Works 2014 Data Warehouse and Analysis Services data sets (previously discussed here). The Adventure Works 2014 Data Warehouse contains a number of different fact tables and related dimensions. I primarily use FactInternetSales (and its related dimensions) which is data for the Internet Sales of Adventure Works. The diagram for the relational data warehouse database can be seen below.
Adventure Works 2014 Data Warehouse Internet Sales diagram
This post serves as a full reference of all of the resources I used while on my journey to learn MDX / SSAS Multidimensional. I’ll try to keep this post updated with an index of resources as I consume them. Some will be free, some will not, I’ll note which are which.
Recently I had the need to start learning MDX to query against an existing Microsoft SQL Server Analysis Services (SSAS) cube. As this can be a long and difficult journey, I thought it’d be useful to make notes about specific things I learn as well as to list and review resources I find along away. I will be paying special attention to using MDX with SQL Server Reporting Services (SSRS) as this is a very common usage scenario, but one in which there are sparse learning resources.
I’ll try to keep this post updated with an index of resources as I consume them. Some will be free, some will not, I’ll note which are which. But first, some housekeeping:
I’ve been known, from time to time, to execute a fun and harmless over-the-top creative office prank. With my upcoming departure from my current organization, I thought it might be fun to launch one last bit of fun, timed to be found at some point after i’d left.
I’d always liked the idea of hiding something (non-evil) in my desk to be found at some point by a future inhabitant of the desk. I’d also heard of someone else calling the job documentation they were leaving behind a “treasure map.” So, that’s where the idea was born — why not leave behind an actual treasure map to an actual treasure? And if a treasure map is going to be created, then of course we’re going to need a pirate theme!
I had grand plans and ideas on how to make a fairly involved hunt — but, due to time restrictions and actually wanting this to be found, I decided on a simpler approach to a basic map and instructions which led directly to buried treasure. I had one other speed bump encountered at the last minute — my office and desk were to be reworked the day I was leaving, so leaving clues strategically placed there wasn’t going to work.
Never fear! I managed to leave a small clue in a common area of the office (which managed to go unnoticed for a week) which then led treasure hunters to the map, which ultimately led to the prize hidden outside in the woods. The hunt was great fun and was a great success! Hopefully it was enjoyed by all.
The unfolded treasure map with its bottle.
SQL Saturday Roundup: #477 – Atlanta, GA (BI Edition)
This past Saturday marked a great landmark for Atlanta — the first “BI Edition” SQL Saturday, and, for many of us, our first SQL Saturday of 2016! Atlanta has been hosting a regular SQL Saturday event for many years now, always with tremendous attendance. Based on the amount of interest in the regular SQL Saturday (usually around May of each year), it was great to see a BI focused edition launched.
With the regular event in the Spring, having this event in January was a great way to space out the two Atlanta events probably about as equally as they could be. And, for a first event, it seemed to be a tremendous success with a registration wait list and around 300 in attendance.
The event was held at the local Microsoft facility in Alpharetta, GA, where the monthly Atlanta MDF user group meetings are held. All told, the facility was a pretty good choice of venue (and definitely a convenient location), but suffered from some overcrowding. The facility had about half the sessions in roughly classroom sized rooms and half the sessions in much smaller conference room sized rooms. While these smaller rooms made for an interesting and more intimate setting, they ultimately filled up very quickly.
Unlike many SQL Saturdays, the event kicked off with an opening keynote and presentation in the large room (multiple rooms joined together technically). I enjoy it when a SQL Saturday begins with some sort of all-attendee opening remarks, it provides nice symmetry to the event (which always has a final closing remarks session), so hopefully more events will adopt this.
Dandy Weyn presenting the opening remarks at SQL Saturday Atlanta 2016, BI Edition.
2016 is here at last, and it’s already shaping up to be an exciting year. Most notably, after 12 years, I decided to put the Professional Development skills that I’d been honing over the past few years into practice and start the year off with a bang — by interviewing for and accepting a Business Intelligence position with another organization.
I’m not going to pretend it was easy, leaving a stable job with an amazing organization after so many years, but it was a necessary one. And, I won’t say that more than once, I didn’t ask myself if I was being crazy. Personality types such as my own value and prioritize security and stability, sometimes to our detriment, to where we find ourselves in situations which are so stable and comfortable that it becomes difficult to challenge ourselves and grow and easy to fall into a routine. But challenge is crucial to continue growing, learning, and advancing. I’ll dearly miss the people at my previous organization, but I look forward to the adventures ahead of me.
So, here’s to a great, and definitely interesting, 2016. My challenge to you for 2016 — if you find yourself too comfortable and stable, find a way to challenge yourself in some new way, whatever it is. Make a plan, get out there, and do it! And don’t forget to document it in your Professional Development Plan!
In October of 2015, the office tape dispenser, Tapey, decided that he’d had enough and headed out for a rule-breaking vacation out west.
I’d originally found this idea on Reddit, however, as it turns out, it was actually just a marketing stunt. Tapey’s adventure, was completely legitimate.
After enjoying his time abroad, he made it safely back to his home on the supply shelf of “Room 9” at Rocket IT, with nobody the wiser — until they saw his album.
Check out the full album of Tapey’s adventures here:
[ View the Full Album ]
Now in my third year, I recently attended PASS Summit in Seattle, Washington. This was my second visit to Seattle for PASS Summit, with my first Summit experience in Charlotte, NC. Having experience my “first time in Seattle for Summit” last year, I had a pretty good idea of where things were and what things would be going on and when and for the most part, things followed the previous year’s pattern very closely.
I arrived in Seattle a few days early so I could work some sightseeing in. While it’s generally not a good idea to try to have a rental car during the conference, in the days leading up to the conference when you want to see a lot of places, it’s the way to go. I rented a car from the airport and made my way down to Portland for a couple of nights.
I spent my first two days of vacation, Saturday and Sunday doing some geocaching and exploring the Portland area. The weather was overcast but nice the first day and lots of rain the second day. I’d really wanted to see Mt. Hood, but unfortunately the weather didn’t cooperate at all.
Mt. Rainier while driving down to Portland after arriving in Seattle
We’re a little over a month away from the start of PASS Summit 2015 on October 27th, it’s time for planning to begin. PASS Summit is an annual conference (usually held in Seattle, WA) of over 5,000 data professionals focusing on all things SQL Server. Unlike many conferences, this is very much a “for the people, by the people” type of conference where practical knowledge is the focus.
As with any large conference, there is a lot of planning to be done to get the most out of attending. In additional to all the usual travel and accommodation logistics, there is whether or not to attend any pre-conference sessions, which of the over 200 sessions to plan to attend over the 3 days of the event, and which after hours events to attend.