It Just Works: Excel 2010 to SSAS

It’s been a while since I posted a geeky article .. so, it’s well over time.

I have the pleasure of working with a talented data warehouse architect on my current project, and the need to connect Excel 2010 to SSAS became a reality this past week. The instructions to do so are easy enough:

  • Navigate to the Excel 2010 Data tab.
  • Click the ‘From Other Sources’ from the ‘Get External Data’ section of the ribbon.
  • Select ‘From Analysis Services’; you’re presented with a ‘server name’ and ‘credentials’ dialog. We are using a Windows Azure Virtual Machine for this project, so you may have to create an endpoint that maps to a obfuscated port number (write me for details). Fill out these fields, adding “: port number” after the server name and your local login information (ensure your local login information represents a local account with an SSAS role on the system).
  • Click “Next”, and here’s where it gets dodgy:

  • You should be presented with a “Select Database and Table” dialog box. Select the cube you want to use and click “Next”.
  • In the “Save Data Connection File” dialog, click “Authentication Settings”, and then “None”. Trust me on this: you won’t see all the screens you need unless you have the system force you to enter login information in another step.
  • Click “Finish”; you may be prompted to save the .odc file, replacing the old one (I did this many, many times).
  • You will then see the “Import Data” dialog, which lets you place the PivotTable in your current worksheet. Select the location and click “OK”.
  • Now the fun starts:

  • You may get an error dialog: “An error occurred in the transport layer”. Click “OK”. Because you selected “None” in the previous step, you’ll be presented with a new dialog, the “Multidimensional Connection”.
  • In the “Multidimensional Connection” dialog, select “Analysis Server”; you will see the server you identified earlier.
  • Your User ID should come over too .. enter your system password and click “Next”.
  • Select your database in the next screen and click “Finish”.
  • Your .odc file should now be set up properly; save your Excel sheet and re-open, you may see the “transport layer” error again, but after this, you’ll be prompted to re-enter your system password.

    Ideally, you should be able to cache this login information .. I’m looking into that and will update the post.

It was an Asteroid after all ..

.. confirmed (or, at least firmly postulated) by a herd of American and European researchers.

This bunch have tested (and re-tested) debris from the Chicxulub impact crater off the Yucatán Peninsula in Mexico. Their findings include a timeline that spans 11,000 years for the impact, almost simultaneous with the Cretaceous-Paleogene extinction. From the article:

When dealing with geological timescales, a range of 11,000 years is about as accurate as you can get. As the research paper puts it, though, “the Chicxulub impact likely triggered a state shift of ecosystems already under near-critical stress.”

Not a bad timeline after all. Please see: Finally confirmed: An asteroid wiped out the dinosaurs | ExtremeTech

Now. If we can only solve the chicken or the egg question.

Warming up to SkyDrive

Just a little. Someone mentioned SugarSync to me the other day .. may have to take a peek at it.

With the loss of Live Mesh (see Live Mesh.. We Barely Knew Ye), I’ve had to do a few things a little differently. Besides reliable sync, local storage and pinpoint sharing accuracy, two of the nice features of Live Mesh included were Email Signature and Favorites sync. with Mesh, you could simply click a property box, and those items get synced across any of the systems in the Mesh. From the looks of it, these are not support as easily In SkyDrive.

I haven’t solved the Email signature sync, but here’s how I resolved the Favorites sync:

Your IE Favorites are stored in a personal directory called Favorites in the \Users folder of your system. The simple way to ensure you can access your favorites would be to sync this folder. Under Mesh, the folder was left in place and connected into the Mesh. With SkyDrive, it’s a bit different .. instead of moving to the mountain, we move the mountain to us, In Windows Explorer (not IE):

  • Navigate to C:\Users\yourname\.
  • Right click the Favorites folder.
  • Click on the ‘Location’ tab.
  • Browse to a folder underneath the root of your SkyDrive folder.

In a few moments, SkyDrive will obligingly copy your Favorites folder, making it available to other systems. The process is the same on the other system .. navigate to the folder as above and point to the Favorites folder beneath the SkyDrive root. You might see some duplicates of system- or  OEM-created shortcuts, so replace with care.

I’ll keep looking for a way to sync Email signatures and advise. For the moment, I keep them in a SkyDrive-synced folder in a Word document, which works well enough.

Live Mesh .. we barely knew ye ..

That’s actually not true .. but it was so catchy.

I’ve been using Windows Live Mesh since it first came out in Technology Preview in 2008. Originally the brainchild of Ray Ozzie (some would say the master of sync), Microsoft has made the decision to discontinue the service in favor of SkyDrive, another product with similar synchronization functionality.

Live Mesh offered folder synchronization with Mesh Synced Storage (under several other names, but essentially an early version of SkyDrive), and the capability to connect multiple machines into your provide synch network (a “device mesh” in 2008 terminology). Mesh or SkyDrive, synchronization has some great features:

If you’re a user of multiple PCs or other devices, synchronization gives you the ability to drop files into synched folders:

  • Very handy if you leave a desktop machine at your work and want to manage those files when you get home. Your work is waiting for you when you arrive.
  • Also handy for working on the go .. Mesh had a Windows Phone component.
  • World-wide accessibility, either by PC, device or by downloading files from the web.
    The coolest of the cool: synchronization with cloud storage effectively eliminated the need to do PC backups .. all you need to do is sync the folders you want to protect and your backup worries are over.
      • Buy a new PC? No problem .. install the sync software first, and your files (including driver and device drivers) will follow you to the new machine.
      • Need to rebuild your old machine? See the above.
      • Lose your machine entirely? Your work is safely stored in the cloud.

I’ll follow the masses to SkyDrive and give it a good shakeout .. will be interesting to see if it will satisfy my use cases:

  • A root folder called ‘sync’ under which I have a number of folders, each shared with different people or machines. I use this for various virtual connections .. pulling files onto cloud servers, and so on.
  • A desktop shortcut into which I can simply drop a current file to ensure it will find it’s way to my next machine.
  • The ability to share individual folders with specific people or teams .. all while keeping other folders secure for myself or other teams.
  • Synchronization of my Email signatures and web favorites.

So, join the crowd and grab SkyDrive. Navigate to http://skydrive.live.com, log in with your LiveID and set up some folders to sync or share. While you’re there, investigate the SkyDrive apps for various devices and applications (including a SkyDrive-synchronized OneNote). There is also a SkyDrive Pro version (call it SkyDrive’s bigger brother) with more capacity (and a price tag).

A Funny Thing at LAX ..

.. certainly a leading statement: all you need to do is look about and you’ll see LOTS of funny things at LAX ..

.. at SEA as well .. remember Moonies?

.. but I digress.

There was this trip .. I had just finished a few days with my favorite customer in Santa Monica and had made my way back to the airport along Route 9. On time, had even negotiated security .. even received the TSA-mandated ‘massage’ (yes: even I get patted down now and again).

Sitting in the waiting area, 40 minutes before the flight, and the overhead says:

Welcome to Alaska Airlines flight 485. We are ready for pre-boarding. As we have no small children and no identified individuals who require additional time to board, would Michael Coates please approach the podium?

I almost didn’t get up. However, curiosity absolutely overwhelmed me. I got up.

In reality, the flight was severely under-booked .. only a few seats from cancellation (there are several LAX-SEA flights a day, the next departing a mere 40 minutes later) .. but close enough to fly (this is all per the agent).

I was the only Gold (the Alaska Airlines Mileage Plan) on the entire flight .. the gate agents thought they should give me a special thrill .. which they did.

Just wait until I get to the Million Miler. Not long now. Open-mouthed smile

Hunter as Highlander of the Month

Some exciting news for Hunter this past week: he was chosen by the Shoreline Directed Studies Department as the “Highlander of the Month” for February.

The honor comes with a certificate, school recognition and a ceremony held last Friday.

We received a letter from his principal; the snaps below are from the ceremony.

 

Way to go Hunter!

The Journey to ‘Operational’

There is a point at which we all want to ‘do our jobs’.

  • If your job is solely execution, rejoice! Being operational means your prayers are answered. Your goals are clear; you can rock the house, crush your goals and enjoy your Friday nights.
  • For those who fall in the realm of ‘continuous improvement’ .. while noble, it impacts the group above .. but, more so: your life. How much is too much (or too little)? Please think, and plan before you strike. Ask the question: are you operational? Can your team function and succeed?
  • Then, there is those who fall under the definition of ‘visionary’ .. as long as you recognize that you are not trying to manage chaos (in the form of introducing changes for changes’ sake), there may be value. As above: can your team function and succeed?

Note that a visionary without execution is .. well .. not as valuable. Our visionaries need to get their hands dirty now and then; by themselves, or by proxy. Engage a Tiger Team .. a group who can close the loop.

The business goes on, though .. it is critical that we avoid being the bottleneck to others’ success.

Another day in Paradise ..

.. or I guess you could call it that. It was a day in Redmond with the sun shining (after two weeks of fog). Note that  it’s not sunny now, in fact, it’s raining.

Oh: and it’s night.

Two meetings on-campus between which, I prowled the familiar halls of Building 40 .. not that I ever worked in that building .. it’s just they’re all so the same. I drank the coffee (not the Kool-Aid) and caught up with various denizens and nemeses. I showed a lot of slides, bantered, beckoned, baited and bought (time). Then, I had sushi.

All in all, a good day.

A Life Essay

Some thoughts about growing up:

I’m very proud that you were offered a bigger role at work. I also understand that you have fears / concerns about taking it: this is part of growing into the world and becoming an adult. You may cite a fear of failure, but psychology experts will instead describe that as a ‘fear of success’ .. in either case, it is holding you back from becoming an adult and from being responsible for yourself.

Everyone grows up and into the world. Everyone gets their own place, creates their own lives; some bounce back home (called ‘boomerangs’) if they don’t get quite the start they need. I was a boomerang a few years back; my brokerage career came to an end and I ran nightclubs and bars for a few years. When I decided to get out of the nightlife, I started working in technology, first as a word processor, then in computer security and then into software development. During the word processor year(s), I found myself back at home with my folks; something that I never thought would happen.

You are just getting started in your life. Right now, you have a familiar and safe place to live. You contribute to the household and for that contribution have extended the illusion and the comfort of your childhood .. and this is just fine. You will always have a safe place here to rest your head, a refer / freezer to raid and speedy WiFi.

Time marches on, with our without us. You must align and extend yourself to what lies ahead. Whether you choose a career or a return to school, you have my support .. but you must do something beyond the status quo to continue your growth into the world. This something we’ll discuss in the next few days, so please think through the trail you want to blaze for yourself and we’ll talk about it.

Let’s see how it works out.

Hunter at Shorecrest

My Hunter is attending Shorecrest High School, in Seattle .. and we couldn’t be more proud of him. While transitioning to the mainstream can be a problem (for all .. the teachers are certainly working to do their part), he is really thriving.

I’m a bit late on this .. the snaps were taken in December. The short story is Boys versus Girls .. find a hidden picture .. get it to the gym floor.

Hunter found it first and made the delivery:

.. and then, he danced:

Follow

Get every new post delivered to your Inbox.

Join 727 other followers

%d bloggers like this: