Total Connectoins on an App and some general questions

[dungar]

I've been tasked with moving several physical process to "an app", so i'm going to attempt to use powerapps.  I've done this once before, and learned a lot since then, so i'm hoping it goes smoother this time, however there are some issues that make this harder than I'd hope-

1. Our data is stored/written to a Azure MS SQL database. This is a premium connector, which makes EVERYTHING vastly more complicated due to powerapps baffling decision to not have any bulk pricing option (that i can find, it did take me 6 months of back and forth with MS to even get them to understand what was going on and not just read the documentation back to me).  I've looked into using sharepoint as a "middleground", and then sync to the database using power automate, but I've never found a fast way to mimic table structure to a sharepoint list.  It would take way too long to recreate all the tables i need in sharepoint with the methods i'm currently aware of.

2. So assuming i can't get around the premium connector, i'm now looking at being charged $10 per user per app.  So if I have two positions in the company (1 and 2), and position 1 needs to do task A and B, and position 2 needs to do task C and D, i can't just make an app for A/B/C/D, as that's $20 per user per month.  I instead need to make a position 1 app, which handles Task A and B, and position 2 app, which handles Task C and D.  This isn't too bad as the line is "clean" for these tasks buuuut....

3. The MS documentation recommends not doing more than 30 connections.  Now from my pov, there's 1 connection.  I need to connect 1 database, and all the data will be there, however, you need 1 connection per table due to how powerapps is designed. If I start getting close to the 30 connection thing, even though it's "one connection" am I going to see issues?  Further i noticed that in memory collections also wind up in that list.  From my reading that shouldn't count towards the total because it's claiming the issues is that it logs in to each connection, and it shouldn't need to do that for those, but i'd like some sort of confirmation?

4. I'm open to other solutions or things i haven't thought of.  I've considered doing this in F#/C#, but I have some other issues there which I don't think i can get around, but if there's some other tech I should be looking into I'm all ears.

[Pstork1]

1) Although I don't like that SQL was made premium the licensing is very simple.  Each user of an App must have either a per user or per app license.  That's not really complicated.

2) Your logic is off.  The pricing is per app, not per task.  If you build an App for tasks A/B/C/D then the pricing is $10 per user per month.  BTW, that price is dropping to $5 per user per month for the per app license starting October 1.

3) The connector can also connect to SQL Views, so unless you need to access 30 non-related tables individually you should be using SQL Views.  Also the 30 connection limit is for performance, not a hard limit.  Performance will PROBABLY begin to degrade if you use more than that.  It depends on what you are doing and how much data you are transmitting.

[dungar]

1.) You say that, but Microsoft took 6 months to confirm this, and the methods the licenses are distributed is still an issue, but whatever.  It is what it is.

2.) I don't think i conveyed this clearly. I  would like to make an app per task (as these are fairly complicated tasks). I must make an app per position, as the costs will get way too high.

3.)  My issue with connections is i have tables I'd like to use as sources for drop downs and the like.  If i have a dropdown so you can select what type of entry you want to enter, that's a separate table in the system, and thus a separate connection. The view of the data will not always have every potential selection from the list.  Given there will be many such dropdowns, i'm stuck either A. had coding them into the app (hard to update ) or B. Making them tables (as i should) and then importing them in and risking connection issues.

[Pstork1 · Accepted solution]

1) What issue are you having distributing per app licenses?  They are assigned to an environment, allocated to an app and then automatically assigned when a user who the app is shared with runs the app the first time.  No, its not like the normal license assignment, but it is straightforward.  Not sure who you were talking to that it took 6 months to get an answer.  Its not that complex.

2) If you make one app per task then your original estimate is correct.  But that is a common decision of design complexity vs. licensing costs.

3) I would suggest a C) scenario.  For relatively static data like dropdowns it does make sense to sync those tables with Excel or SharePoint and pull the data from there.  Performance will be much better.  I would also recommend loading all those to collections at the start of the app.  This will also improve overall performance.  Since the connection limit isn't a static limit, but is based on performance, doing those things should alleviate any issues.

I'm sorry you are disappointed with the way Power Apps is designed, licensed and run.  But some of that is inherent in it being a low-code/no-code environment. I don't think you will find a better alternative.  You may want to look at standard ASP.NET development.  I don't think it will be cheaper due to the development and ongoing maintenance costs.  But that would be your alternative.

[dungar]

1.) I'm not the main person in charge of distributing apps, so this is probably more off topic than we need to go. The fact that they're just assigned when a user runs the app, with no easy way to track that, has been causing problems for the team that does handle this though.  They might be unaware of a better way, but that's a separate issue at this point.  The main thing i wanted to confirm is that there's no great way to get away from using SQL here, such as being able to quickly create sharepoint lists based on SQL table structures, as the big blocker right now is how long it takes to create a corresponding table in sharepoint and sync it up.

2.) Sure, but many other microsoft products allow for a capacity license, and powerapps used to (well something like it). This is the only thing we're using where i'm constantly stuck cutting corners because doing it the proper way is not just too expensive, but absurdly so.  We're likely going to wind up on a capacity power BI license, so it's not like we're unable/afraid to spend the money, but when I say "cap is unlimited" no one is happy.

3.)  Ok i'll look into this.  I don't love the write to excel/sharepoint because now i need to replicate data out of my database just for the purposes of connecting (as all of this data must wind up in the database, and it's where it will be edited should it change), but given i'm looking to do that anyways with the entire structure what's a few dropdowns.  Is there some documentation on making sure it loads at the start?  I may already be doing that but I want to be sure.

[Pstork1]

1) I agree that reporting on per apps licenses needs to improve.  MS is working on better reports.  But you are right there is no easy fix if you are using SQL.

2) I don't have any control over how MS does licensing, but I would disagree that there was ever a capacity based license for Power Apps.  I don't particularly like the licensing model they are using, but that doesn't make it hard to understand.

3) Let me be clear, That you would only use the sync to Excel or SharePoint for content that is relatively static, like content used to populate dropdowns. You would not do it for live data that changes frequently.  Loading data from those data sources will work the same way that loading it from SQL does.  It just uses standard connectors.

[dungar]

1.) i think there's no further point discussing.  It is what it is. 

2.) is the same, but all i'll say is it took microsoft members 6 months to give me a straight answer on if we did or didn't need to pay for connections or if they were included with our current licenses, and how to set that up.  They repeatedly told me things that were not true, because their own team didn't understand how using a premium connection changed the deal.  I have multiple emails where I explain that i'm using sql, clarify that's a premium connector, and they quote back the documentation to me as how it relates when you're not using a premium connector.  It was an immensely frustrating process, not even slightly professional, and has basically tanked any large adoption of powerapps at this company.

3) This is then worrying about the definition of "relatively static"  We're looking at some 10-20 very simple processes here that I need to replicate.  I can manually update sheets when someone decides that process A has a new type to deal with, but the whole point of a database in my view is exactly so i can coordinate my data.  Having to constantly work around it really does suck, but i'll do what i have to.  I just don't love the idea of having the type list updated in some powerapp but not reflected in the reporting database

I think at this point I have most of the answers I need.  The short of it is "yes you need to watch your connection limit and bundle your processes to avoid massive costs", unless you want to custom develop an app, which is off the table for other reasons.

[Pstork1]

1) Agreed, no point to discuss further

2) Again, I'm sorry that whoever you talked to from Microsoft was an idiot.  But clearly it didn't take me 6 months to give you are straight answer and point you to the documentation.  It wasn't that hard of a licensing question. For whatever reason you ended up talking to the wrong people.

3) The major use of Power Apps is to allow tech savvy business users to create apps easily without needing to understand the internal workings of databases.  If your database needs are as extensive as you describe then I doubt a Low-code/no-code approach is the right one for you.  Power Apps is not meant to replace professional development.  Its meant to handle 80% of the work that professional developers used to do because there was no easier way.  Its not the solution for everything.

[dungar]

I get that in theory but i find the limitations somewhat silly.

My literal use case is that I have endusers who currently manually write stuff down, and it's later manually typed into an excel sheet, which is then automatically loaded to a database for tracking.

Being able to cut out the middleman and just have the end user enter data directly to the database strikes me as something powerapps should be doing quite easily, and in theory does do quite easily, but due to the limitations of a single app and the infinite scaling of the cost of the product can very quickly get out of hand.  My 20 some processes could easily cost $200 a user, across thousands of users, and congrats you've got a 6 figure yearly bill to write some numbers to a table.

Given the whole point of this project is to STOP having data written in some excel sheet that no one knows about, and to make sure it's all consolidated, it's unfortunate that powerapps plays so poorly with it (the date offset thing has also been an ongoing headache, which while easy enough to deal with, is annoying that it requires so much boilerplate to do so).  I don't think "save your dropdowns to an excel sheet" is a reasonable suggestion at all when it 100% should be stored in a database, especially if i'm already dealing with database data. That just leaves one more spot for me to manage/maintain/sync, and one more spot for data to desync.

But whatever.  As said it is what it is.  I'll deal with it or pick some other way to do it.  Direct development in C#/F# is being avoided for other reasons, but my first thought when approaching this was "oh that's pretty basic even in full code, so i bet powerapps can knock it out quickly", and the truth is it does...but i'm sutck working around the edges rather than throwing up what should syncing 20 forms to 20 tables.

[Pstork1]

Let me clear up one thing.  The cost per user would never be $200.  Yes, per app licenses are $10 and only cover one app.  But per user licenses cover an unlimited number of apps at $40 per month.  So once users on average are using more than 4 apps its cheaper to just buy them all per user licenses.