May 4, 2023 at 8:11 am
My company has a number of SSAS tabular cubes which are deployed to a server and Power BI reports are built on top of that.
We have lots of bespoke reporting requirements from external customers and are thinking it would better for them to do there own analysis and reporting as long as we can provide a secure cube with their data only.
So the question is what options are available for a cube be made available for external customers to access and connect (via Excel, SSRS or Power BI) to do their own reporting?
I only know of cubes being deployed on a local server, so I can think of only direct access to the analysis server.
Could the cube be published to the customers analysis server?
Any other methods?
Thank you.
May 4, 2023 at 2:05 pm
To answer your first question...yes it would not be impossible to do that, but it would be tricky because your data source(s) would need to be accessible by each on of your customer's environments. Assuming your data sources are on-premises, making it accessible to multiple external clients would be a security risk.
Other than that you have a few options, each with its own advantages and nuances:
If you're going to use one of the first two options, keep in mind that SSAS works with Windows authentication only. This means that your customers would need a windows account in your domain (or the "local" domain if the server is hosted in the DMZ) to access the service.
The Power BI option is interesting, and how you use it depends on who is going to pay for the licensing. If you host the PBI data models in your own environment, then your customers would either need an O365 account in your domain (with a PBI license), or be added as a guest (or B2B) account in which case they need to bring their own PBI license.
If you were to host the PBI data models in your customer's Power BI environment, you would need to set up a gateway for each customer in order to refresh the data...unless your data source(s) is hosted in the cloud and accessible that way. With all the necessary "plumbing" in place, you could automate data model deployment via DevOps or by using PowerShell.
As you can see, lots of things to consider. I'd start with the question of who is going to pay for the licensing and/or cost to host the service(s). Also consider whether you could use row-level security as a means to ensure that every customer can only see their own data, as opposed to a separate cube/data model for each.
Hope this helps.
May 10, 2023 at 6:11 am
One problem you will have to deal with is authentication. My old place wanted to do something similar and authentication was the biggest problem.
Microsoft allow a greater range of identity types to connect to PowerBI in Azure than is possible with on-prem. If you think you will ever need to allow users to access current data in their reports then you will need to build the solution in Azure.
If users will be limited to only use data already curated into cubes then you can consider doing this on-prem. However you must be aware that a 'simple' upgrade to allow pass-through queries to get current data may not be possible with on-prem.
As a minimum using latest data with on-prem will require all users to authenticate with a domain account on to your domain. If you use any form of impersonation in on-prem, the problem is that PowerBI, SSAS and SQLServer each use different mechanisms for impersonation and they are not compatible with each other. MS have more or less fixed this in Azure with an identity server but this is not available for on-prem use.
Therefore, if you do impersonation with on-prem SSAS, this does not create a kerberos token so the impersonation cannot be carried forward into SQL Server or any other type of data source.
The moral of all this is: make sure you scope out all your requirements. These will dictate which option (Azure or on-prem) you should aim for.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply