SaaS app integration

[kylereed]

I'm developing a single page app on ASP.NET Core 2.0/Vue JS that includes tight integration with Office 365: auth, OneDrive, Email, SharePoint, etc. 

I would like to have an optional Power BI integration in the app for my clients most of whom have an E5 subscription and thus a license for PowerBI Pro. I'm not interested in Power BI Embedded because of its expense and because, well, most of my clients already have PowerBI Pro.

Users login with their Office 365 creds to access the app. Each tenant get their own SQL Azure database; the api determines the correct database.  

I want this to be a good experience for users; easy to setup, a headstart with some out of the box reporting that works well. I also don't want to have to manually re-create this for each client. I'd prefer a DirectQuery kind of experience. The app is very transactional and delays are not helpful.

Options I'm considering: 

1. Implement API Key as an additional auth in the API for certain controllers; PowerBI would consume those endpoints.

2. Creating a custom data connector/auth for the app. Not seeing many amazing samples of this. Not sure whether to target the api or go direct to the database in Azure. 

3. Pushing the data into PowerBI via the API. 

I'm hoping a guru can jump in and give me some great advice. I've read much of the documentation but things seem to be moving quickly and I'm not sure which direction to go. Also, I'd love to hear how others have done similar things. 

[v-ljerr-msft]

Hi @kylereed,

---

I would like to have an optional Power BI integration in the app for my clients most of whom have an E5 subscription and thus a license for PowerBI Pro. I'm not interested in Power BI Embedded because of its expense and because, well, most of my clients already have PowerBI Pro.

---

So you want to embed Power BI content in your own app without the capacity of Power BI Embedded, right?

As far as I know, we must purchase a capacity for embedding in a production environment now. I haven't face such a scenario before. So hope a guru can jump in and give some great advice on it. 

Regards

[kylereed]

Actually, no, not embedded in my app. Like I mentioned it's optional. The app comes with several canned reports. The Power BI stuff would allow for custom reports/ad hoc reports. 

[cmncp]

Hi @kylereed.  Did you find a solution for this?  I have a similar need.

[kylereed]

Well, it's not what I was hoping for but I solved it this way:

1. I added to the front end an api key generator. It allows an App Admin to generate an api key and assign it to a given user. I stick the key in the control database (non tenant) with an expiration date (default 30 days).

2. I then added an authentication scheme to my asp.net core 2.1 backend in addition to the Azure AD JWT Auth. It searches the database for a matching, unexpired key and completes the authentication setting the standard tenant id claim (same as what Azure AD does). My controller base class uses this to ensure it's talking to the correct database (the app is multi-tenant with a database per tenant).

3. I thought I would be able to access my query controllers (OData) from Excel/PowerBI with this api key auth, but it kept erroring out [I could find no good examples of anyone actually authenticating to an OData service from Power BI]. So, I extended the api key auth slightly to do Basic Auth as well. That works. 

4. Now, Excel and Power BI can connect to an OData service successfully with Basic Auth using name: ApiKey, pwd: [generated ApiKey]

I hope this helps. If anyone wants to jump in with better ideas I'm all ears. 

Kyle Reed