M is now the new way to get data in SSAS Tabular 2017

If you work with SSAS Tabular model, you have option to import from your Power Pivot/Power BI model. In most cases when we build Power Pivot/Power BI model we use Power Query to get data. Prior to SSAS 2017 you will have problems after the import because your tabular model doesn’t speak M so you will need to find a way to re-import data back to your Tabular model.

Capture

Good news is now you don’t have to because SSAS Tabular 2017 introduces the modern Get Data experience for models at the 1400 compatibility level, read more about what’s new in SSAS 2017¬†here. But bear in mind that you will need SQL server 2017 and set your Tabular compatibility level to 1400 to be able to use this new feature. I have tested with SQL server 2016 and it allowed me to make a connection to my SQL database and I can see my table list. But the story ends here as so you can’t import anything.

Capture

People talked about this for long time and finally Microsoft delivered this. Well done to the Dev team. THANK YOU! It’s a natural thing to happen as more and more people prototype a model in Power BI/Power Pivot and at some point you will want to import it to a Tabular model for various reasons such as data size limit in Power BI/Power Pivot etc.. Now the dust settled, what happens next for me, I guess is to do a deep dive into M. You probably heard about custom data connector and you can build a connector to get data from ANY data source you might have. In order to do that, you will need to know M, see this great post here to get you started if you are curious about this just like me ūüôā

 

Advertisements

Power BI May Updates

Power BI has released may updates

Here’s the complete list of May updates:

Report view

Analytics

Data connectivity

Query editing

You can also watch this video for demo of these great new features.

On top of this, there are 2 other things I think you should know if you and your organization has already started or are planning to use Power BI.

1 Power BI free license will no longer enable you to share reports, you have to have either pro license or Power BI premium (see point 2).

2 MS is introducing Power BI Premium, a new way of sharing your report either on premise (Power BI report server) or cloud. read here for more info.

a whitepaper is also available for you to know more about this brand new offering.

Power BI Premium whitepaper

There is also a post from Matt for detailed reasoning why MS is doing this

Matt’s post

 

 

Tableau + Power BI

Tableau and Power BI are both great BI tools and you normally get to play with one of them as no company will spend money to get both. But for those of you who use Tableau and have Excel 2010, you should consider bringing some of family members of Power BI into your set of tools. Let me tell you why.

First of all, Power Query and Power Pivot are both free add-ins for Excel 2010 and they are the best gifts from Microsoft, these is no reason¬†not to install them. Secondly, If you use Tableau for reporting, you know Tableau likes flat file and you will try everything to create the flat file. Creating a Tableau extract or connecting live¬†to a database and use SQL joins to bring multiple tables together are the ways you can do¬†just that. But what happens if¬†the data you need reside¬†in different database? Ask your IT to link one as linked server so you can join tables from these 2 database to create a flat file or ask your IT to do it for you?¬†When you work for a big organisation,¬†that’s something will take long time to happen or not¬†at all.¬†So what do¬†you do then?¬†As data geek we never give¬†up, Power BI comes to the rescue.¬†As seen below graph, I successfully used Power Query ( one member of Power BI family which is made up of PowerPivot, Power Query, Power View/Power Map) to create one data extract using data from 2 different databases and 1 Excel file.

3

What happens next is you could suck the new data file into Tableau and done with it or you can load the data file into Power Pivot and¬†then use Tableau to connect to your Power Pivot to visualise the data. As we know Tableau’s visualisation capability is way better than Power BI at the moment. But It’s much easier to get the numbers using Power Pivot. Like I said in my first post, getting the right number and how easy to get them is more critical. Since you have both tools at your disposal, why not get the most out of them. I have take the second approach. PowerPivot’s DAX formula and time intelligence functions and doing fantastic¬†jobs to get the numbers¬†I need, then I use Tableau for visualisation. See below cool tree map generated by Tableau. (the bigger the¬†square, the higher the sales value).

Let’s applause for¬†the real power brought to you by the combination of both tools. Happy analysing!

1

My first post

Hello world, I have always wanted to start my blog as I would like to share what I know about Self service BI, more specifically, Power BI and Tableau. I do know a little bit about SQL server BI stack which comprise of SSIS, SSAS and SSRS, but  I do not know them well and enough to share.

Hence this is my first post, I think I should introduce myself. My name is Bob and I have been working as a BI Reporting Analyst in Financial sector in Sydney Australia.¬†My career as a BI¬†Reporting Analyst started with designing report in Power Pivot which¬†I started using back in 2010. I have to admit that part of the reason why I liked Power Pivot so much back then is because Rob Collie’s¬†blog http://www.powerpivotpro.com. I have been following his blog almost since he started blogging on his site. Since then his site leads to a couple of other blogs as well, such as Chris Webb’s¬†and SQLBI. I have learned a lot from these people and¬†I have bought every book written by these people on¬†Power Pivot and Power Query and I would highly recommend¬†those blogs and books if¬†you are serious about Power BI.¬†Nearly half year ago¬†I¬†started using Tableau, I have to say that it’s a fantastic tool for visualisation,¬†and Power BI lags a lot in this space. As Rob mentioned in one of his blog post that there are 2 kinds of people in the world: people love numbers and people¬†love visual.¬†Like Rob¬†I am in the first category and how easy it is to get¬†the numbers and get them¬†right is far more important than visualise them. Power Pivot does a freaky fantastic job in this, hence if you ask me to choose, I will go for Power Pivot without any hesitation.

I better keep my first post short and hopefully I will share something useful to you in my next post. Thanks for reading.