Which is the best secure data source to store and retrieve more than million record?

[muges01]

Hi,

I'm trying to create a power apps with the following requirements

• The app should retrieve the data from secure data storage
• The app should only display the data for the logged in user
• The logged in user shouldn't be able to access the other user's data
• The app should be able to retrieve data from more than millions data records
• The manager should be able to retrieve data for the team members

Which is the best data source to use for the above scenario?

• SharePoint list
• Common Data Source
• SQL Server

I was considering CDS but i'm not sure how reliable and how it will perform with the large number of records.

If any suggestion or guidance will be grateful.

 Thank you in advance.

[v-xida-msft]

Hi @muges01 ,

Based on the needs that you mentioned, I think the Common Data Service may be a better choice for you.

For your first question, Common Data Service has its own security storage mechanism. Please check and see if the following article would help in your scenario:

https://docs.microsoft.com/en-us/power-platform/admin/database-security

For your second question, if you want to display the data from your data source based on current login sign user, I think the Filter function could achieve your needs. Please consider take a try with the following formula:

Filter('CDS Entity', 'Created By'.'Full Name' = User().FullName)

For your third question, you could consider assign proper security role (e.g. System Customizer role, mentioned in response 1) to the user, then the user could only access to their own created records. You could also consider use above Filter formula to limit the user to see their own created records.

For your forth question, currently, PowerApps app has no limit on the amount of data source records. Actually, the data source records you could process is based on the formula applied to your data source.

If you apply Delegable formula to your delegable data source, then it would delegate the data process from your app to your data source itself. If you apply Non-delegable formula to your delegable data source, then you could only process data locally in your app, and you could only process 2000 records at most locally.

More details about Delegation in PowerApps, please check the following article:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-overview

For your fifth question, you could consider save the Team members into a collection in your app, then filter your data source records based on the Team Member collection.

Best regards,

[muges01]

Hi @v-xida-msft ,

Thank you for your quick response. 

I have checked the security storage mechanism, i guess i need to use the minimum privilege security role but with the minimum privilege security role can the user add and edit their own item?

I don't want to process the data locally, i want the data to be process or filtered on the data source. Example, only get the data belong to the selected user and for the selected date range. Can i achieve this?

Is it possible to access the CDS data using API so that i can make use some of the data in my SPFx webpart?

[v-xida-msft]

Hi @muges01 ,

Could you please share a bit more about the minimum privilege security role that you mentioned?

More details about the predefined security roles, please check the following article:

https://docs.microsoft.com/en-us/power-platform/admin/database-security#predefined-security-roles

You could also consider create a custom Security Role, and then assign specific Entity access permission to this role. Please refer to the following article:

https://docs.microsoft.com/en-us/power-platform/admin/database-security#create-or-configure-a-custom...

for your second question, if you want to retrieve data which is belong to current sign in user and in a specific date range, I think the Filter function could achieve your needs. Please consider take a try with the following formula:

Filter(
        'CDS Entity', 
        'Created By'.'Full Name' = User().FullName,
         Value(Text(ThisItem.'Created On', "yyyymmdd")) > Value(Text(StartDatePicker.SelectedDate, "yyyymmdd")) && Value(Text(ThisItem.'Created On', "yyyymmdd")) < Value(Text(EndDatePicker.SelectedDate, "yyyymmdd"))
)

Note: StartDatePicker and EndDatePicker represents the DatePicker controls added in your app.

For your third question, the answer is Yes. You could use Web API to access data in your CDS. Please refer to the following article:

https://docs.microsoft.com/en-us/powerapps/developer/common-data-service/webapi/overview

Best regards,

[PaulD1]

I have a fair bit of experience with PowerApps (canvas) and SQL but have only just started having to work on projects with CDS (so I'm no authority) - but so far my view is never use CDS unless you absolutely have to (e.g. there is an existing D365 / CDS which you must access in real-time).

>>The logged in user shouldn't be able to access the other user's data

Is recording the owning user in the record and setting a filter based on that sufficient security? If you put your app in an environment where the users do not have 'maker' rights then they will only be able to use your SQL Connection via your App. If your data is senstive though you could look at SQL's row based permissions for additional security.

>>The manager should be able to retrieve data for the team members

In SQL you could have a table of 'delegates' - e.g. for each manager, a list of who their team members are. You can then create a View joining the 'manager' and 'data' tables to achieve this.

>>The app should be able to retrieve data from more than millions data records

SQL will allow you to create indexes, monitor query plans, etc. to ensure your data retrieval is quick (and you can increase your plan for additional power). Also (afaik), CDS does not let you query across multiple entities (other than via fetchxml) - you cannot create SQL like queries with joins, so if you want to store your data in a relational structure I think CDS is a poor choice compared with SQL.

[muges01]

Hi @v-xida-msft ,

Sorry for the delay, due to the holiday seasons. Happy New year.

Yes, the minimum privilege security role that i mention is the link you have give.

https://docs.microsoft.com/en-us/power-platform/admin/database-security#create-or-configure-a-custom...

Create or configure a custom security role

If your app uses a custom entity, its privileges must be explicitly granted in a security role before your app can be used. You can either add these privileges in an existing security role or create a custom security role. There are a set of minimum privileges that are required in order for the new security role to be used - see Minimum privileges to run app.

Thank you for the reply.

[muges01]

Hi @Paul ,

Happy new year and thank you for your feedback. You are the third person mentioning about CDS is a bad choice.

The reasons that they said:

• Bad performance
• Limited API functionality
• Limited to single environment
• Couldn't copy to another environment (UAT to production)
• etc

What is blocker did you came across?

Are you using ALM and CI/CD? 

[muges01]

Hi @v-xida-msft ,

Is there any good article on ALM and CI/CD for PowerApps and CDS?

[PaulD1]

Biggest blockers for me:

• Bad performance
• Can't create relational queries (huge problem)
• Very hard/convoluted to work with - e.g. the way you have to work with lookups (foreign keys) and option sets are a pain and take quite some getting used to
• Delegation (you hit a lot more non-delegation problems than with SQL)

As an example, all IDs in CDS are GUIDS (so you can see that performance is not a high priority) but if you use the CDS connector in PowerApps, you can't perform a delegable filter/lookup against a GUID data type.

If you know some SQL you'll find CDS really limiting and frustrating to work with.