Using Cosmos DB for reporting now is proof of concept. It is not implemented yet.

Reporting scenarios

There are multiple ways to set up reporting dashboards on data stored in Azure Cosmos DB. Depending on the staleness requirements and the size of the data, the following table describes the reporting setup for each scenario

Scenario

Setup

1. Generating real time reports on large data sets with aggregates

Option 1: Power BI and Azure Synapse Link with DirectQuery
Option 2: Power BI and Spark connector with DirectQuery + Azure Databricks + Azure Cosmos DB Spark connector.
Option 3: Power BI and Azure Analysis Services connector with DirectQuery + Azure Analysis Services + Azure Databricks + Cosmos DB Spark connector.

2. Generating real time reports on large data sets (>= 10 GB)

Option 1: Power BI and Azure Synapse Link with DirectQuery
Option 2: Power BI and Azure Analysis Services connector with DirectQuery + Azure Analysis Services

3. Generating ad-hoc reports on large data sets (< 10 GB)

Power BI Azure Cosmos DB connector with import mode and incremental refresh

4. Generating ad-hoc reports with periodic refresh

Power BI Azure Cosmos DB connector with import mode (Scheduled periodic refresh)

5. Generating ad-hoc reports (no refresh)

Power BI Azure Cosmos DB connector with import mode

Deploying azure cosmos db using azure resource manager

Prerequisites

  1. Install Azure CLI https://docs.microsoft.com/en-us/cli/azure/install-azure-cli-windows?tabs=azure-cli
  2. Upgrade or install the Bicep tooling: "az bicep upgrade" or "az bicep install"
  3. Have Azure subscription you going to deploy
  4. Have a Resource group you are going use for resource deployment


Deploying bicep templates using Azure CLI

1. Run Login command to your azure account

az login
CODE

2. Set the default subscription for all of the Azure CLI commands that you run in this session.

az account set --subscription 'Luware Development Subscription'
CODE

3. Set the default resource group

az configure --defaults group=BI_Team
CODE

4. Deploy the template to Azure

az deployment group create --template-file main.bicep
CODE


ARM Deploy.zip






Choosing Partition key in Cosmos DB container

Throughput provisioned for a container is divided evenly among physical partitions. A partition key design that doesn't distribute requests evenly might result in too many requests directed to a small subset of partitions that become "hot." Hot partitions lead to inefficient use of provisioned throughput, which might result in rate-limiting and higher costs.

For each Azure Cosmos DB container, you should specify a partition key that satisfies the following core properties:

  • Have a high cardinality. This option allows data to distribute evenly across all physical partitions.
  • Evenly distribute requests. Remember the total number of RU/s is evenly divided across all physical partitions.
  • Evenly distribute storage. Each partition can grow up to 20 GB in size.


Service Session
{
    "Id": "0ce27612-d138-4112-949d-3ed7ff0a8e3c", <-key
    "ServiceId": "4344bc27-afec-483c-b1f2-4bc4d88fae6e", <-partition key 
    "InitialCallerId": "a4b6b91c-45cb-41b1-b5f9-5fcd788e768b",
    "OutcomeId": 5,
    "StartedAt": "2021-01-08 08:05:23.351661",
    "TotalQueueTime": 33.0568099,
    "TotalIvrTime": 21.0245331,
    "TotalConnectedTime": 42.0486938,
    "IsUnderSla": 1
}
Service
{
    "ServiceId":"4344bc27-afec-483c-b1f2-4bc4d88fae6e",
    "Name": "Nastya Stage",
    "id": "dae5d1c2-9f97-4834-92da-c12761f979dd",
    “OrganizationUnitId": "dae5d1c2-9f97-4834-92da-c12761f979dd", ", <-partition key (missing)
 }

User Session
{
   "Id": "48ac4bbe-9fe7-4c24-8b46-4b725cd981ae", <-key
    "ServiceSessionId": "3b365a9e-874e-4969-b3f4-157d4e8a7b30",
    "UserId": "be1634f7-9bf7-43fa-8634-1bcd0ce0c798", <-partition key
    "OutcomeId": 1,
    "DistributionTypeId": 3,
    "UserSelectedAt": "2021-04-13 12:31:14.585402",
    "RingTime": 40.0136646,
    "ConnectedTime": 14.0140499
}

User
{
    "Id": "caa84e08-9baa-405c-b7b9-5bbc9fc913e9",
    "Name": "ADM Tatsiana Kamarova Luclouddev",
    "id": "1812f01b-75ec-4890-8405-949e02db8300",
    “OrganizationUnitId": "dae5d1c2-9f97-4834-92da-c12761f979dd", ", <-partition key (missing)
}

CODE