The Power BI Analysis Services Connector (Tabular) is a feature that I’ve been working with throughout the preview of Power BI ever since it was introduced in early March of 2015. Coming from the SQL Developer side of things where using Visual Studio and Team Foundation Server for reporting purposes was the norm. Building tabular models in the VS made more sense to me than building power pivot models. It offers project access security, change control, a means for repeatable setup and deployment, and the external safety of information provided by role and row based security. Not every company has these tools at their disposal, but when they do, it is a no brainer for analysis and reporting needs.
Prior to the recent Power BI GA release, the implementation of tabular model usage still needed some work within the Power BI tools. The security offered in role and rows was not being applied to the end user, the lack of this support made trying to find some implementation solutions a little difficult. There was a feature request to implement row level security, but the requested feature hadn’t really gained a lot of traction amidst all the other feature requests. I was a little concerned about how, or if, Microsoft was going to be implementing the missing piece. However, I was optimistic, because the speed at which new features and fixes were coming out of the Microsoft BI team was impressive, to say the least. Thankfully, as of the GA release, the tabular model role/row level security features have been implemented and works against the end user.
Let me explain what sets this Connector apart from the rest.
- It validates permissions against the user building the report and dashboard. And then, when the dashboard is shared with end users, the connector validates their permissions and can filter the data they see. Now, some will say this is how it “should” work, but after working with Power BI and seeing how it worked initially, I’m impressed they were able to implement the correction as quickly as they did.
- It offers a “live” connection. Near real time data. As often as you want to update your model, that’s how often the reports will update. So I can set up one process on the backend, schedule and monitor it, and I know that my front end will immediately reflect the changes to the data.
- I can connect to my tabular model from Excel, work on some visualizations and then deploy the Excel file as a dataset. The awesome part is when that happens, the Excel file becomes a live connection if I already have the AS Connector set up. That means I can start analyzing in Excel, and if I come upon something that I want to share, I have a means to automatically plug that into my live connection on the site!
- If that wasn’t enough, with the GA, the Power BI Desktop also offers the same direct connection to my model. I can choose to “Explore the Tabular model by using a live connection”, and all my relationships are automatically pulled in. Not only that, once I’m done, I can publish from the Power BI Desktop to my site!
- The Power BI desktop still supports “Select items and get data from multidimensional or Tabular model” without the relationships. I can still “Connect” to my model and pull in only the data, this allows me to use a portion of the model as a supplement for putting together a completely different model if I want to. The difference here is that when I publish the model, it’s independent of the live connection because I chose not to use it in that way. “Explore vs. Connect” in PBI desktop.
Power BI initially started off with a variety of different data sources to choose from and ways in which ad hoc visualizations could be created by business owners. That same functionality exists today, but with the introduction of the SQL Server Analysis Services connector the tool just stepped up the game. Now businesses can set up tabular models, secure them in Team Foundation Server, create repeatable and documented processes for change control and updating, and know that their front end tools that are using and disseminating that information throughout their company is accurate and up to date.
That is Powerful stuff.
If you haven’t yet checked out Power BI, visit here for more information.