Connection providers that Excel uses for Power Pivot and Power Query

For Power Query Excel (2013) uses Microsoft.Mashup.Oledb.1
If you don't have Power Query installed you'll get the following error message:

Microsoft.Mashup.OleDb.1 provider is not registered on the local machine

(Had anyone ever tried marking this message as "unhelpful"?)

In the Data ribbon, 
If you go to Existing Connections -> Select a Connection or Table -> Mark the connection and right click it -> Edit Connection Properties -> Definition Tab

you will see that the connection string starts with:
Provider=Microsoft.Mashup.OleDb.1


If you use only Power Pivot and you try to link to a connection, then the connection string looks like this:
Provider=SQLOLEDB.1;Integrated Security=SSPI;

Why is it mixed? Why are there two types of connection strings?

It depends on who started the Power Pivot query and when. There is also a difference if you have Power Query Installed on your machine or not. Unfortunately, if you installed Power Query and then upgraded office you might need to install Power Query again (or, at least, Repair it). 

As far as I'm concerned this is the main problem with Excel as a Reporting tool:
Excel is amazing. It could do many things, use many languages (DAX & MDX) but if you can't share it easily between people since it is flavor-dependent (are you using 2013 or 2010 or 2016 or Office 365? do you have Power Query installed or not?) then it causes huge problems. Ribbons disappearing, Queries have to be rewritten, connection strings have to be rewritten... Version control shouldn't be such a huge issue, IMHO.

Updating source for Power Query In Excel after changing the table name

If you work with Excel 2013 and you create Power (either Power Pivot or Power Query) queries, and you had changed the name of the underlying SQL server database table,

you need to do the following in order to re connect :


First, go to the Power Query tab and then choose either Show Pane or Launch Editor

The Workbook Queries will appear on the right.
If you don't have Power Query tab in your ribbon (it appears after ADD-INS), you need to install it
(correctly).

You will see all the queries marked with yellow exclamation mark. You click on them and change them.

Once you double click on the Query in question the Query Editor will come up.

If you changed the database Choose in Applied Steps the Option of Source, and choose the name of the new database.

Go to Query Editor - View (last tab)

Choose Advance Editor:

And now you can change the table name to the desired name.

If you want to change it to a Stored Proc in Excel, type in the query details in the query name, like this:

And change the Source in the "Advanced Editor" as follows (

It is called M Programming language for Power Query)

So you don't need to name the type here, you just say it is the source, and that works!

No, I couldn't find any documentation for this thing, I just played around. 

I hope it helps :)

Microsoft and R?

I've read here that Microsoft had acquired evolution Analytics, the company who provide open source distributions of R, alongside commercial “Enterprise” extensions for big data infrastructures.
Since I've decided to broaden my skills set lately by learning R, I believe these are exciting news for the following reasons. The writer of the post, Tony Davis, mentions that it must be to replace/extend SSRS; learning it for a short while, I believe that on top of SSRS, you can use R functionality for acquiring data, modeling, data mining and reporting. Essentially: everything BI, or SSIS, SSAS and SSRS.. Since R reads all the data into RAM, it can also replace/extend the in memory OLAP which exists nowadays in SQL Server 2012 (or the Tabular Model).
Another thing which is important to point out for the SQL developer who's keen to learn R, is the "thinking" of the language. As an SQL developer (especially MS, not necessarily Oracle), we're used to think in "Sets". We don't go row-by-row when we write code, but instead it's table-by-table. It was a type of thinking which is hard to get used to (it was difficult to learn normalization). And then when you need to learn BI, you had to "unlearn" the normalization part, but you're still going by rows. Writing code for regular programming languages (like C, Java, whatever) requires you to go row-by-row, one data (structure) after the other. 
In R you have to go row-by-row, while you could still query after a set of data. It requires a completely different type of thinking; if you tell me "data set", I immediately think "SELECT FROM WHERE". It doesn't work this way in R (though you could implement this type of syntax in R). So not only you can have many uses of R, but if might even use R instead of writing a cursor. 

So essentially I believe that Microsoft had purchased R in order to extend SQL in many good ways. Either way it makes sense to take the time and learn it!

How to create a good professional website? 10 tips to improve your SEO

SEO, for those who don't know, are Search Engine Optimization. When Google looks up something, it ranks the entire search results by a certain algorithm. Here is a very simple list which describes how can a simple and humble non-IT professional improve their chances of being found on Google when somebody tries to look them up.
In my previous post I've said that not everybody need a website. If you're a lawyer (let's say) and your name is listed in all the leading listings possible, then you don't need a website. How do you check it?
Simple: Google your name + profession. If it is a very common name then add the location as well. If you can easily find yourself and you phone number in the list that's great. No need for a website, your job here is done.
If you don't find yourself easily, then you have a problem, even if you claim that no publicity is needed, that the word of mouth works very well; if  your friend wants to recommend you and since they last used your services they lost your card/mobile number/email address then you lost a potential connection, and that's a pity.
Maybe all you need is a good Social Media Professional Page: It could be either on LinkedIn, Facebook, Google+ or other. Depends on your preferences. If you need to choose only one (since you don't have the energy for more), then I think that for artistic people Facebook is preferred, while for the rest of us professionals LinkedIn is preferred. A Google Plus page is very useful as well.

But, assuming you have a website, and you do wish for people to find you quickly.

1. The company has a name? Excellent. Make sure that it includes the main line of business in the title. Don't call yourself "surfboard" if your a lawyer. You're a lawyer that likes surfing? find something that composite of both words. "No-Shark Solicitor", for example.
2. Nevertheless, don't make the name too generic. "Sydney Lawyers" won't help anyone. It really won't. Find a name that sticks and that you like. A bit of creativity is a good thing. 
3. If you have more than one branch: Put the address of the main branch first. When your company is searched online via Google , Google puts the address of the first address that it finds first. It's not always the desired result.
4. Yes, you do need a "main branch". Or at least "main location" or where this company had originated from.
5. Good practice: add "opening hours" next to each branch name. 
6. Good practice: have short profile listing of the people that work in your company. When you list the main professionals, it also makes your company easier to find. Let's say that I'm not sure that I spelled your company name correctly. I might Google one of the professionals in your company. If one of the first 5 listings is of your company, then your company would get more "hits" via Google and I can find the contact that I'm looking for.
7. Good practice for listing your name or your contacts: list your name as well as the nickname. Robert (Bob) Smith, for example.
8. Every now and then look at the website and make sure it is readable and that you can navigate in it easily. Read it through and make sure there are no typos, or any weird "computer marks" there. And make sure that the "Opening hours" (clause 5 above) and the "Our Team" (clause 6 above) are still correct and up-to-date.
9. Make sure that the look is modern and up to date: look at your website on different devices, and make sure that even if you change the size of the browser (or even just tilt the smartphone) it is still readable.
10. Have you changed your company name recently? Make sure that in your website it is stated clearly: "New Name", formerly known as "old name", and add the "old name" to the meta data of the website (your web developer knows what I'm talking about). "Recently", by the way, could mean even 10 years. I had to look up a company by the name it was listed in my company's database and the name had changed 7 years ago. 
11. If you just purchased the domain, put a business card as your website while it is "under contraction". This way people can contact you even if your website isn't bright and flashy!
12. The last tip here is the most important one:
    Since Google claims that more searches are done on the Mobile device than on the PC, you must make sure that your  website is mobile friendly!

And one last tip which is very important:

The point of this tips column is to make sure people can find you when they look you up online. 

I hope that this information had helped you. Any other very basic tips? Let me know about them in your comments!