Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Ronald Monbleau]

We are in a situation that is causing us to re-think our use of PowerApps.
Microsoft initially made certain connectors standard and then moved them to premium.  The SQL Azure connectors were some of these.
We had built several highly used apps on standard connectors that are now premium and our EES agreement is coming up.

The licensing model has changes a few times in the last year and we are looking at a $1.5M increase to our EES agreement which essentially doubles it.

We are working with our reseller and Microsoft to improve the situation, but at this point I may have to abandon the platform for 90% of our app needs.

One work around we have found is to replicate SQL data to SharePoint lists and use SharePoint as the data source for PowerApps.
Has anyone else come up with ways to not use premium connectors and still have apps that work with line of business applications?

Also, has anyone else had conversations with Microsoft about the idiocy of their licensing model?
$40 retail per user per month for premium connectors and it doesn’t matter if you are a creator or consumer.  I know MS is looking to hear from the community on licensing and pricing.  Or at least our rep said they do.  $40/user/month should be only for creators.  Consumers should be either free or $1 or less.

Thanks
Ron

——————————
Ronald Monbleau
IT Solutions Manager
West Palm Beach
——————————

[Will Page]

There was massive pushback on this when they originally made the changes last year. The best they’ve come up in response the idea of a cut-down version of CDS we can use in Teams (Dataflex) which they’re calling a relational database but I somewhat doubt we’ll be able to create views from queries to even a fraction of the degree you can with SQL server. It’s not been released yet so we’ll see.

You should be grandfathered in for 5 years for any apps you developed before the licensing changes though.

——————————
Will Page
Technical consultant
Christchurch, NZ
——————————
——————————————-

[Ronald Monbleau]

Thank you.
We are going to look at DataFlex too.  But my hopes are not too high.
This is the first I’ve heard on the 5 year granddfathering.  That takes some pressure off.

——————————
Ronald Monbleau
IT Solutions Manager
West Palm Beach
——————————
——————————————-

[Mark Pearson]

I use SP and standard connectors for everything.  I have no issues and deal in teh thousands of records per month.  

I think you also need to add SQL servers often come with a per user charge as well

IF MS are looking to hear from teh community they are not advertising it very well

——————————
Mark Pearson
National Operations Manager
61409321547
——————————
——————————————-

[Warren Belz]

Hi Ronald,
You can trigger a lot of things with one E5 licence on When an Item is created or modified in SharePoint.
I do not have SQL, but you could certainly write records to it on this trigger with a timed flow coming back the other way to sync. Looking at your volume however, I do not know how practical this would be.

——————————
Warren Belz
Queensland Australia
——————————
——————————————-

[Ronald Monbleau]

Warren,
We have a 3rd party tool that performs a 2 way sync between SharePoint and SQL.  We use it all the time to keep Line of Business app data in SharePoint for use with Flow and other functions like lookup lists.

The tool we use is called Layer2

——————————
Ronald Monbleau
IT Solutions Manager
West Palm Beach
——————————
——————————————-

[Larry Burgess]

We make extensive use of Layer2 as well for exactly the same reasons – replicating the essential data from SQL Server to SPO to get around the licensing requirements. I have feeling MS is going to have some seriously ticked off customers if they don’t get their licensing ducks in a row.  I think the “grace period” / grandfathering in expires in 2024.

——————————
Larry Burgess
Developer
Interlinc Mortgage Services
Houston TX
281-210-5669
——————————
——————————————-

[Ronald Monbleau]

Mark,
Thanks.  I’m working with a SP list of 12K records and am having trouble.  Delegation sucks.
do you have a way of importing over 2K records into a collection from SharePoint?  I have a really good way of doing it from SQL but the concept doesn’t translate to SharePoint lists.

——————————
Ronald Monbleau
IT Solutions Manager
West Palm Beach
——————————
——————————————-

[Mark Pearson]

Warren Bellz gave me a great solution once that worked well.  for a list of 8000 records that is growing.  If yu are using SP it required yu to have a numeric column (call it) ShadowID.  It gets populated with teh record ID everytime a new one is created  (there are ways to do this as it might pose a some thought.  This is needed as yu need to use the > which is not delegable on ID column but is on numeric.  I belive it is delegable on SP as they handle unique ID differently.

You need to the do something like:

ClearCollect(MDTemp,MdataV2);
If(CountRows(MDTemp)=2000,Set(VarID,Max(MDTemp,ID1));Collect(MDTemp,Filter(MdataV2,ID1>VarID)));
If(CountRows(MDTemp)=4000,Set(VarID,Max(MDTemp,ID1));Collect(MDTemp,Filter(MdataV2,ID1>VarID)));
If(CountRows(MDTemp)=6000,Set(VarID,Max(MDTemp,ID1));Collect(MDTemp,Filter(MdataV2,ID1>VarID)));
If(CountRows(MDTemp)=8000,Set(VarID,Max(MDTemp,ID1));Collect(MDTemp,Filter(MdataV2,ID1>VarID)));
If(CountRows(MDTemp)=10000,Set(VarID,Max(MDTemp,ID1));Collect(MDTemp,Filter(MdataV2,ID1>VarID)));
If(CountRows(MDTemp)=12000,Set(VarID,Max(MDTemp,ID1));Collect(MDTemp,Filter(MdataV2,ID1>VarID)));
If(CountRows(MDTemp)=14000,Set(VarID,Max(MDTemp,ID1));Collect(MDTemp,Filter(MdataV2,ID1>VarID)));
If(CountRows(MDTemp)=16000,Set(VarID,Max(MDTemp,ID1));Collect(MDTemp,Filter(MdataV2,ID1>VarID)));
Sort(MDTemp,ID,Descending);
If(CountRows(MDTemp)>=16000,Office365Outlook.SendEmailV2(“insert an email”, “Collection limit exceeded”, “The Startup has been exceeded”))

Basically I create a collection.

the above gets the records based on the 2000 limit and adds to a collection each time.  Note I only go to 16000 records but then it will send me an email warning to add more lines.  I use ID1 as the shadowID

Works like a Charm

Another tip Warren Gave me (kudos to him) was to create another numeric column to store the date based Value(Text(date,”yyyymmdd”)) each time a new record is created.  This allowed me to do search on dates which is non delegable but in this manner the number is.  Brilliant solution

——————————
Mark Pearson
National Operations Manager
61409321547
——————————
——————————————-

[Ronald Monbleau]

Mark / ,
That is a great solution.  There might even be a way to make it scalable.  I’ll play with it.
After much “thinking” on my issue with bulk loading from SharePoint, I realized that I had a numeric value I could use to batch the records in batches guaranteed to be less than 2000.  So I just created a loop through the makeshift “Batch IDs” and pulled in the 12k records.
?

——————————
Ronald Monbleau
IT Solutions Manager
West Palm Beach
——————————
——————————————-

[Warren Belz]

Hi Ron,
I did a blog on Delegation that may be useful to you. Also look at the related With() statement blog as this can be used at times depend on your other filters.

——————————
Warren Belz
Queensland Australia
——————————
——————————————-

[Ted Beerman]

I concur completely. I understand a “premium” license for writers, but where does MS get $40/user to be a user of an app? Where is the market-based pricing? How does $40 for PA work when an E5 license with all of Office AND Windows AND SharePoint AND more is about the same price. MS Project is more of the model I would expect…pretty expensive for the user that has to work in it everyday and use advanced features, but there are alternatives for users that just need to view or use the information.

——————————
Ted
IT Manager
——————————
——————————————-

[Clifford Steinberg]

Completely Broken licensing model.  We are in the same boat and have talked directly with MS about this.  They have no good answers after a year of discussion and the sales reps pushing back with customer feedback.  Its insane.  We license Microsoft 365 at the E5 level and SQL Enterprise with Software assurance.  The SQL connector should “come with the meal.”  However, if not, you should be able to purchase just the connector you need with an appropriate discount.  This all-or-nothing proposition does not work.  We should not be forced to look for alternatives while we sit on this platform that should be integrated completely based on our existing level of licensing.

——————————
Clifford Steinberg
CIO
Garden City NY
——————————
——————————————-