Migrating from Excel to CDM

[mr-dang]

The write speed to CDM is looking very attractive. Currently, Patch takes 3s in an Excel spreadsheet while CDM is real-time as writing should be.

That said, the two seem to operate very differently. When I remove an Excel datasource and attempt to add a CDM datasource of the same name, PowerApps can't handle delegation of all my formulas at once--understandable. RAM usage spikes to a whopping 10GB and CPU usage to 100%.

It seems that Excel datasources are a snapshot of the data and is not updated until you perform a write or refresh function. Because of this, it is not constantly reading from the datasource (you don't see the dots roll across the top of the screen). CDM on the other hand is reading from the datasource real time, so it's always swirling at the top. You really shouldn't use too many Filter() on the RAW source. It seems to work better to Collect it to a temporary collection and recollect whenever you make changes.

I am not a programmer by trade, so I am wondering: do most users work in temporary collections of the original datasources?

This whole time I kept thinking I wanted to make all of my formulas to operate on the original source--but it seems I am finding faster performance by working locally. If I could read from more than 500 records, I think I won't need complete delegation after all since I would just work in collections.

[Steelman70 · Accepted solution]

Hi Mr Dang, I work with both temporary collections and original sources.  I use temporary collections mostly for menu items in drop-downs that do not change during a work session.  It speeds things up with relatively few side effects.

I think it is better to work with original sources for many reasons*, but currently there is a performance issue as I mentioned in another one of your posts.  I suspect that as PowerApps and CDM develop, the programme team should introduce optimisations in PowerApps itself such as accessing the data source only when really needed.  Then we will not need to use temporary collections at all.

Anyway in one app I used the ID filter to load >500 records (actually 1600) to my temporary collection and this works fine although a bit clumsy.  Something like this:

ClearCollect(MyTemporaryCollection, Filter(MyDataSource, ID<500));
Collect(MyTemporaryCollection, Filter(MyDataSource, ID>=500 && ID<1000));
Collect(MyTemporaryCollection, Filter(MyDataSource, ID>=1000 && ID<1500));
Collect(MyTemporaryCollection, Filter(MyDataSource, ID>=1500))

 In any case this is only a work-around until the optimisations are introduced in PowerApps itself.

* Simpler apps, fewer issues with data synchronisation, immediate feed-back in case of input errors etc. etc.

[mr-dang]

Using ID looks like a good interim solution. 

I took your suggestion and tweaked it in a way that I can transition when the time is right.

• My temporary collection for pulling in 500 records at a time has the same name as what my final Connected datasource will have. Until PA can read more than 500 records, I'm using a dummy entity which I will duplicate to have the name of the temporary collection. 
  
  I think this makes sense since my formulas would operate on the temporary collection anyway. Then I would only need to connect the final datasource and tweak any formulas that write back to the source to transition.
• PA will not operate more than one condition in each Filter--at least in CDM. 
  
  I added a column to my entity which divides Title by 500 to segment each block of 500 for collecting. I first tried it with AddColumns, but it did not work, so I put it in my formulas for writing back to source:
  

  n: RoundDown(Value(Right(Title,10)),0)+1

• I can now use your formula, but with a combination of a toggle, an OnVisible property, a timer, and a slider to make it loop. The OnVisible property resets the Toggle. The Toggle reloads the datasource. It simulates Refresh(MyDataSource) for my temporary collection. 
  

  Screen.OnVisible: 
  UpdateContext({resetdata: !resetdata});
  UpdateContext({resetdata: !resetdata})

• Toggle1.Reset:
  resetdata
  
  Toggle1.Default: 
  resetdata
  
  Toggle1.OnCheck:
  Refresh(MyDataSource);
  Clear(MyTemporaryCollection);
  UpdateContext({pulldata: true, iter: 0})

• Timer1.OnTimerEnd:
  If(pulldata,
  
  If(iter<Slider1.Value,
  UpdateContext({iter: iter+1});
  Collect(MyTemporaryCollection, Filter(MyDataSource, n=iter)),
  
  UpdateContext({pulldata: false})
  )

  This is an absurd workaround for looping. CountRows(MyDataSource), Max(MyDataSource,n), Last(MyDataSource).Title only operate in the first 500 records which is like a black box. There's no way to find out how many iterations you could do so I just stuck in a slider for now, which I can edit later.

I think this will minimize the number of things I need to edit in order to transition to a datasource that can read more than 500 records.

[mr-dang]

Revision--my last post did not correctly address writing back to the source. It assumed that an n number that identified which group of 500 a record belongs to was already known.

In solving this problem, I have found it gets less and less practical a solution. Here's what I had to do:

1. Patch normally, except for the n value. RecordId is written automatically and Title will be too if you change it to "Number Sequence" when you first form the entity. However, those two columns are not known at the time of Patch, so you need to repatch.
2. Set a variable equal to what you just patched.
3. Repatch the variable, but this time, RecordId and Title are known, so you can divide either by 500 to find out which group they belong to for Collection.

This is what it looks like in a formula:

UpdateContext({temp:
	Patch(MyDataSource,Defaults(MyDataSource),
		{[your data here]
		}
	)
});

	Patch(MyDataSource,temp,
		{n: RoundDown(Value(temp.Title)/500,0)+1
		}
	)