MS SQL – Query & Reporting Tools

November 11, 2017

Getting data out of a Database hasn’t been easier these days. Giving all the tools you have at your disposal…

Giving the fact that all the fuss about BI and Cloud Storage, Big Data etc. We seem to loose feeling with all the o

image

Here are a few examples we can use for simple and complex query and reporting purposes.

1. Query using the MS SQL SSMS

It’s obvious that SQL SSMS offers all the functionality you need to get the data out of the database.

This example shows a combination of Common Table Expression (CTE) Query combined with the PIVOT function, to generate you dataset.

image

2. Using PowerShell – Query

Re-using this quite complex Query using Scripting language is quite Powerful.

image

image

3. Excel – Query

Using Excel combined with MS Query we can re-use the same Query

image

image

image

image

4. Access – Pass-Through Query

Re-using the complex query using MS Access in a Pass-Through Query Statement.

image

image

image

5. MS SQL Reporting Services

Re-using the complex query using MS SQL Reporting Services & Report Builder

image

image

6. MS PowerPivot – Excel Addin

Re-using the complex query using MS Power Pivot – Addin

image

image

image

image

image

7. MS Power BI

Re-using the complex query using MS Power BI

image

image

This is not a limit list of tools you have a hand. There many more which you might overlook …

QlikView Client

MS Power Query

– …

For getting data out of a database you need to the proper tools, but this is not a constraint at the moment.

What is, is being able to manage all these different applications and technologies.

Bottom-line is that one you spend efforts in getting your queries right you can re-use them most any tool that comes around Smile

Advertisements

PowerBI – Number and Currency Formatting

October 31, 2017

After looking for a solution why my Number Formatting did not want to work out…

After creating a new Measure it returned the classing US based formatting.

image

While I wanted to have the EU formatting in my table.

Power BI Desktop Client Regional Setting :

Even after changing the Power BI Desktop Client settings, it did not take effect Sad smile

image

Solutions :

Go to Control Panel –> Region –> FORMAT

image

If this format is not in line with your region it will overrule the Power BI Regional settings ?

image

So after applying the correct region format settings on OS level, everything appeared as expected Smile

PS : These figures are just demo based Winking smile

Enjoy!


PowerBI – Access SharePoint list data

February 5, 2017

As I showed in a previous post SharePoint is different Interfaces you can use to access as a data source.

https://audministrator.wordpress.com/2015/01/26/sharepoint-interfaces/

So I we know it can expose it’s List data using the  REST or oData protocol.

Open PowerBI and go to GET DATA menu.

Choose OData Feed to connect to your SharePoint data source

 

image

Once logged you can choose from your lists

 

image

And play around to make your data visible using all the features from PowerBI Smile

image

Once Finished you can publish your result

 

image

Once published you can reach out on the mobile apps.

 

image


PowerBI – Get Google Analytics Data

February 5, 2017

Using PowerBI there are several out of the box  Web Services available to connect to. One of them is Google Analytics.

This is how it goes :

Open PowerBI Online version.

image

Next in the left menu bar click Get Data.

image

Next select Services

image

Select Google Analytics  and Get it NOW.

image

Select the authentication and fill in your credentials.

image

On the left menu bar you can see the Datasets, Reports and Dashboard, and reports that are available out of the box

 

image

image

image

 

Next you can Schedule a reload time … the Free edition you can only choose between daily or weekly.

image

 

This shows which are the nice capabilities of MS PowerBI platform  Smile

As you could see I also connected out of the box to SalesForce.com

image