What is Dataverse?

Microsoft Dataverse is a cloud-based, low-code data service and app platform, which allows you to leverage the security and connectivity of Microsoft services.

  • Dataverse comes with standard tables and columns, with relationships defined between them.
  • Users can also create custom tables and columns, and also define relationships between them.
  • Dataverse is available globally but its deployment is based on Region/Geography for compliance reasons.
  • This is not stand-alone and will need an internet connection.
  • Dataverse can be used as data source for Dynamics 365, Power Apps, Power Automate, AI Builder, Portals etc.
  • It used Azure Active Directory (Azure AD) to handle authentication including multi-factor authentication. Authorization is supported till row and column level.
  • We can easily apply business logic on data like duplicate detection, business rules, workflows etc.
  • Dataverse stores data in Azure Cloud.
  • A Dataverse database stores data in a set of standard and custom tables. A table is a logical set of rows that is used to store data.
  • Tables can hold millions of items. Storage in each instance of Dataverse database can be extended to 4 TB per instance.
  • The structure of Dataverse database is based upon the definitions and schema in Common Data Model.
  • As per Microsoft Document, Common Data Model is a logical design that includes a set of open-sourced, standardized, extensible data tables and relationships that Microsoft and its partners have published in an industry-wide initiative called the Open Data Initiative. This collection of predefined tables, columns, semantic metadata and relationships for the basis of Common Data Model.
  • Types of Tables:
    • Standard: Out-of-box, customizable, imported as part of managed solution
    • Managed: non-customization and imported as part of managed solution
    • Custom: new tables or imported from unmanaged solution
  • Types of table relationships in Dataverse
    • One-to-Many
    • Many-to-Many
  • Environments
    • Each environment allows only one Dataverse database for use within that environment
    • Each environment is created under Azure AD tenant and can only be accessed by users of that tenant
    • Al environments are bound to their respective geographic locations. Hence, the Dataverse database is created in the datacenter of that geographic location.
    • We can create multiple environments for one geographic location to manage our solutions like Development Environment for development purpose, Testing Environment for testing the developed solutions and Production Environment for solutions that will be used by End Users.
  • Business Rules
    • Business rules apply logic at data layer and not at app layer.
    • Examples of Business Rules: check empty value, show error message, validate data etc.

Create Date and DateTime using values in SQL Server

Year, Month, Day are columns in MyTable table

Create Date from numbers

To create Date: DATEFROMPARTS ( year, month, day )

Select
datetimefromparts([Year], [Month], [Day]) as Submission_Date
From MyTable

Output: 2019-01-02

Create Date and Time from numbers

To create Date and Time: DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds )

Select
datetimefromparts([Year], [Month], [Day], 00,00,00,0) as Submission_Date
From MyTable

Output: 2022-06-20 00:00:00.000

Search for multiple fields in Combo Box

By default, we can display only one field in Combo Box and Search for only one field in Combo Box.

There is a requirement to display multiple fields and also search for multiple fields in Combo Box. For example, we have EmployeeDetails as the Data should display Employee Name and Employee Email ID and can also search the record using same.

Modify below parameters of ComboBox:

Items: EmployeeDetails
DisplayFields: ["EmployeeName","EmployeeEmailID"]
SearchFields: ["EmployeeName","EmployeeEmailID"] 

We can use this for ID and Definition Column types as well.

What is DLP?

Data Loss Prevention (DLP) policies help users to prevent accidently exposing organizational data.

  • Scope: Environment Level OR Tenant Level.
  • Tenant level policies: Scope can be all environments, selected environments, all environments except ones you have specifically excluded
  • Classification of Connectors: Business, Non-Business, Blocked.
  • Business Connectors host data that is used by the business/organization.
  • Non-Business Connectors host data that is used for personal purpose
  • Connectors that are restricted and should not be used across one or more environments are classified as Blocked.
  • Default classification for a connector for new policy is Non-Business. Admins can move connectors from this category to other categories based on business preference.
  • DLP policies can be modified from Power Platform Admin Center
  • DLP policies affect Canvas Power App and Power Automate flows.
  • You should be a Tenant Admin OR Environment Admin to create DLP Policy.

People Picker in Canvas Apps

Insert a People Picker in Canvas Power App using Combo Box. Below are the steps: Add Office 365 Users Data Source. Add a Combo Box. Click on Edit Fields on Properties Bar. Select Person as the Layout. To pre-populate the Combo Box with the existing value, write below code in DefaultSelectedItems property. RequestSubmittedByEmail and RequestSubmittedByName […]

Check if a Table in Data Source is empty

To check whether a table in data source is empty or not, use IsEmpty

IsEmpty(tableName)

This is equivalent to CountRows(tableName)=0

We cannot use CountRows() directly on data source as it generally gives Delegation warning (Depends on the Connector)

If IsEmpty(tableName) returns true, this indicates that your Data Source table has no rows.

Restore a Database in SQL Server

  • Open SQL Server Management Studio (SSMS).
  • Right click on Database. Click on Restore Database…
  • Click on Device. Click on Add and select the file to restore. Click Ok. Again click OK.
  • You will get a message that your database is successfully restored.
  • You will be able to the new database under the Database category.

Install SQL Server 2019

Below are the steps to setup SQL Server 2019

  • Scroll Down and install Express Edition
  • Select Basic installation type
  • Accept the License Terms
  • Verify the SQL Server installation location. Click on Install. It will take few minutes. When the installation is done, you will get below screen. You can install SSMS from here by clicking on Install SSSM button.
  • When you click on Install SSMS button, it will open a Web Page in a new tab. Scroll down to download it. It will start downloading .exe file of SSMS Setup.
  • Click on the file to open the installation wizard. Click on Install to start the installation.
  • This will install SQL Server Management Studio (SSMS).