Latest Power Query Connects to Analysis Services

Power Query for Microsoft Excel now supports connection to SQL Server Analysis Services cubes.

You can download the latest release here:

Upon loading Microsoft Excel, it will prompt you to close programs while it installs the new features.

Once loaded, you can see the Power Query tab across the top.  I'm using Excel 2013, so it was already there, just updated to a newer version.

We'd like to get some external data from our SSAS Multidimensional cube, so we click on the button "From Database Get External Data":

Then we select "From SQL Server Analysis Services".

We locate the name of our Analysis Services Server.

Next we're prompted to Use Your Windows credentials to access Analysis Services, click SAVE:

On the right hand side of Excel screen, you'll the Navigation pane containing our SSAS Multidimensional Cube:

We selected Measures and a Dimension and clicked the Load button, it starts chugging away, to pull the data into our Excel Workbook solution:

After a brief moment, whallah, we have data from our cube:

Now you can edit the query by clicking the Edit Query button, on the right hand side, you'll the list of steps used to pull in the data:

We then clicked the Settings button to the right of the Added Items and a window pops up:

Added another field called Orders Count and the field got added to the list of data:

By clicking the down arrow on any of the fields, a window pops up to sort the data, or remove specific entries:

Click the Close and Load button:

Excel re-loads the data containing the new field:

And the field now displays in our Workbook:

No MDX required!

We select the "Load to Data Model"... Click Load... we get a warning box:

Click continue, it loads data into a Model:

Then hovered over the Workbook Queries, right clicked, and said "Send to Data Catalog"

After supplying credentials, a screen pops up:

And the Sharing screen:

Notice the box in Yellow, '"This query uses a local data source and may not be available to others".

I publish and log into PowerBI.  Navigate to My Queries and sure enough, there it is...

I click the ellipses...

See the Analytics...

I don't have a Data Management Gateway established at this time, so that's as far as I'm going to take it at this point.

Hope you enjoyed and learned a bit.

Happy coding~!



Passed the 5th test today... Microsoft® Certified Solutions Expert: Business Intelligence

How About Cross Platform SQL Server?

In the world of Microsoft big new recently:

Opening up Visual Studio and .NET to Every Developer, Any Application: .NET Server Core open source and cross platform, Visual Studio Community 2013 and preview of Visual Studio 2015 and .NET 2015

For those programmers working with .Net, this is a huge new way of doing things.  Open Source.  Cross Platform.  This should really open avenues to bring in more developers from some of the 'free' languages.  Write once, deploy anywhere.

That got me to thinking, why not do the same with data.  How about SQL Server running Cross Platform?  On Linux and Unix operating Systems.

Only makes sense, MySQL has Cross Platform.

What do you think?  Possibility?

No Magic Wands

Many organizations are so behind the times when it comes to being data driven.  Tons of spreadsheets.  Data everywhere.  No organization.  Manual entry.  Silo data sources.

That's why it's somewhat of a dichotomy to read about all these new analytics capabilities with Hadoop, Slow Data, Machine Learning, Self Service, etc.

All you read about is how orgs are running triathlons with data.  Truth is, many companies are still learning to crawl.

Data has been the red headed stepchild for decades. 

Because most orgs were concerned about selling something, raising profits, front end systems, staying in business.  The data just sat there.  Perhaps a report developer to get basic reports.

Then the data explosion.

Now these same companies are trying to get up to speed and it's not like you can wave a magic wand and presto, everything's fixed.

They want to integrate their data.  They want to reduce manual entry.  They want an integrated data solutions.  They want self service.  They want accurate data.  They want to streamline processes.  Reduce costs and overhead.

This provides great opportunity for those in the data professional space.  But keep in mind, it can be slow going. 

Because when data professionals get in there, assess the ecosystem, and quote a price on how to get things started, it can be sticker shock.  In addition to having someone take responsibility for allowing weeds to grow in the data to information.

So like I've stated in the past, I'm all for the latest and greatest technologies to find insight and convert actionable events to increase sales, reduce costs and maximize efficiencies.  Just keep in mind, that's the idea situation and not every company is in the position to change everything overnight.


Next Project in the Queue

Service Broker for SQL Server.

I saw a demo recently at the SQL BI User group.

Now I get to work with the technology on my job.  Got a new project assigned this evening.  It involves taking a proof of concept and getting it to work in Production.  Also add a new endpoint.

It's basically a queue / messaging system built into all version of SQL Server, including Azure.

You send a message to one or more endpoints, by establishing a conversation, the message conforms to specific formats, after the conversation ends, it shuts down. 

And you can send multiple messages, in any order, and the receiving machine treats the entire batch as a transaction, which can roll back.

If the receiving machine goes away, the messages stack up in the sending machine.

That's the basics anyway.  I'm excited to have a project to bill hours against and we need to do a good job on this because we now own the process.  Which means I'll have work to do while in Germany the next two weeks.

And I get to work with people from other countries, like Germany and Australia.

Should be fun~!


I Didn't Order Tax

So we went out to eat the other day.  When the check arrived, I asked the waitress what this fee was at the bottom, something called "Tax".  I said I never ordered tax, please remove off the bill.

She said it was mandatory part of the transaction, no way to remove it.  I demanded to see the manager.

I said I didn't order this tax and I don't think I should have to pay for it.  I already get taxes taken out from my paycheck, and then pay more taxes around April 15.

And then when I earn profit from my investments, they tax that as well.  They put tax on our gas, food and now you want to tax me on dining out at a local restaurant.

This can't be right.

If I was to sit here, with a spreadsheet and calculate just how much taxes I'm really paying, it's got to be close to $0.50 on the dollar.

I thought our founding fathers of this great nation fought for their independence due to taxation without representation. And now this.

This has got to be some kind of joke.

So in the end, I paid the tax on our meals.  And I have to find a new restaurant to eat at.  Jeepers.  Some people are very sensitive to asking some basic questions.

Invisible Rays Flowing Through Our Bodies

Our house sits across from some electric wires, about 100 fee up in the air.  Sometimes when its real quiet I can hear a frequency buzzing in the background.  I wonder if those things are throwing off low voltage?

Of course we have a wireless network, as does every other person on our block.  Lots of invisible frequency there.

And wireless phones, about 5 different brands, each have satellites sending frequency every minute of every day.

And of course we have XM satellite, 3 radios in fact, so there's more invisible rays flowing threw our bodies.

When I was a child, they'd say get back from the television and microwave, they're emitting stuff that isn't good for you.  Now we are absolutely bombarded by invisible rays everywhere we go.

I wonder if this has harmful effects?  Surely they would warn us of any possible risks.  Just like they wouldn't sell cigarettes without warning us.  Only makes sense.

Perhaps in 50 years we'll look back and say we cooked our brains with the bombardment of invisible transmissions flowing through our bodies unprotected.

Let's check back in a few decades to see the truth.

Quite a few Computers

How many computers does a person really need?

I had a desktop computer for a while.  Bought a laptop for work (side jobs) and gave the desktop to the wife.  Then I have my work laptop.  In fact, I'll be getting a second laptop shortly for a specific client, so that's two work laptops plus my laptop.  Then I have a tablet device, although it's actually a very powerful machine, on a tablet, which I won at a Microsoft Big Data conference in Orlando.

So my wife wanted a new laptop, I purchased on last week, so I inherited back my desktop.

And then there's the Windows Phone which is basically another computer.

That's quite a few computers for one person.  I don't know what the correct number is or the average per household.

I wonder how much money is spent keeping these things powered and charged with monitors and such.

As a kid, we had one computer for the entire family, now we have close to 10.  These time's they are a changing.