NB_Update_Semnatic_Model_Measures_Calculation_Groups.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "ac6e2a5b-996a-4a1a-b16f-357b95078ab1",
   "metadata": {
    "microsoft": {
     "language": "python",
     "language_group": "synapse_pyspark"
    },
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "source": [
    "## Import Libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "68dfea9c-f216-44ee-83b8-ad1dcb675765",
   "metadata": {
    "microsoft": {
     "language": "python",
     "language_group": "synapse_pyspark"
    }
   },
   "outputs": [],
   "source": [
    "import sempy_labs as labs\n",
    "from sempy_labs.tom import connect_semantic_model"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7f246e5d",
   "metadata": {},
   "source": [
    "## Set Workspace and Dataset Variables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cc46fe86-4574-40f1-8bea-ad14406498d6",
   "metadata": {
    "microsoft": {
     "language": "python",
     "language_group": "synapse_pyspark"
    }
   },
   "outputs": [],
   "source": [
    "workspace = 'Workspace ID'\n",
    "dataset = 'Dataset Name'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eef78a06",
   "metadata": {},
   "source": [
    "## Connect to Semnatic model"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7ecd1460-5b3e-437c-afbb-a4c4914ac07a",
   "metadata": {
    "microsoft": {
     "language": "python",
     "language_group": "synapse_pyspark"
    }
   },
   "outputs": [],
   "source": [
    "with connect_semantic_model(dataset=dataset, readonly=True, workspace=workspace) as tom:\n",
    "    for t in tom.model.Tables:\n",
    "        print(t.Name)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "179c064f",
   "metadata": {},
   "source": [
    "## Rename Objects in SM"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2c0e38b6-e255-436c-bb97-644a98f6b571",
   "metadata": {
    "microsoft": {
     "language": "python",
     "language_group": "synapse_pyspark"
    }
   },
   "outputs": [],
   "source": [
    "# Rename objects in the semantic model\n",
    "with connect_semantic_model(dataset=dataset, readonly=False, workspace=workspace) as tom:\n",
    "    for t in tom.model.Tables:\n",
    "        t.Name = t.Name.replace('_',' ')\n",
    "\n",
    "with connect_semantic_model(dataset=dataset, readonly=False, workspace=workspace) as tom:\n",
    "    for c in tom.all_columns():\n",
    "        c.Name = c.Name.replace('_',' ')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8c8c7d66-5600-47e8-80c6-c829255c4840",
   "metadata": {
    "microsoft": {
     "language": "python",
     "language_group": "synapse_pyspark"
    }
   },
   "outputs": [],
   "source": [
    "# Function to convert to Snake case\n",
    "def to_title_case(snake_str):\n",
    "    return snake_str.title()\n",
    "# Convert table names to title case\n",
    "with connect_semantic_model(dataset=dataset, readonly=False, workspace=workspace) as tom:\n",
    "    for t in tom.model.Tables:\n",
    "        t.Name = to_title_case(t.Name)\n",
    "\n",
    "# Convert column names to Snake case\n",
    "with connect_semantic_model(dataset=dataset, readonly=False, workspace=workspace) as tom:\n",
    "    for c in tom.all_columns():\n",
    "        c.Name = to_title_case(c.Name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3eef8484-487f-4830-a67a-7422a147272b",
   "metadata": {
    "microsoft": {
     "language": "python",
     "language_group": "synapse_pyspark"
    }
   },
   "outputs": [],
   "source": [
    "# with connect_semantic_model(dataset=dataset, readonly=True, workspace=workspace) as tom:\n",
    "#     for t in tom.model.Tables:\n",
    "#         print(t.Name)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e352ff83",
   "metadata": {},
   "source": [
    "## Create Measures"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f7dfc54e-fa54-4b0f-8e3f-21c0db6f2d52",
   "metadata": {
    "microsoft": {
     "language": "python",
     "language_group": "synapse_pyspark"
    }
   },
   "outputs": [],
   "source": [
    "# with connect_semantic_model(dataset=dataset, readonly=False, workspace=workspace) as tom:\n",
    "#     for t in tom.model.Tables:\n",
    "#         if t.Name == 'Europe Weather Data':\n",
    "#             # List of measures to add or update\n",
    "#             measures = [\n",
    "#                 {\n",
    "#                     'name': 'Measure Name',\n",
    "#                     'expression': \"Measure Expression\",\n",
    "#                     'format_string': \"#,0.00\",\n",
    "#                     'description': \"Measure Description\",\n",
    "#                     'display_folder': \"Measure Folder\"\n",
    "#                 }\n",
    "#                 ]\n",
    "\n",
    "#             # Add or update measures\n",
    "#             for measure in measures:\n",
    "#                 if measure['name'] in [m.Name for m in t.Measures]:\n",
    "#                     # Update existing measure\n",
    "#                     existing_measure = t.Measures[measure['name']]\n",
    "#                     existing_measure.Expression = measure['expression']\n",
    "#                     existing_measure.FormatString = measure['format_string']\n",
    "#                     existing_measure.Description = measure['description']\n",
    "#                     existing_measure.DisplayFolder = measure['display_folder']\n",
    "#                 else:\n",
    "#                     # Add new measure\n",
    "#                     tom.add_measure(\n",
    "#                         table_name=t.Name, \n",
    "#                         measure_name=measure['name'], \n",
    "#                         expression=measure['expression'], \n",
    "#                         format_string=measure['format_string'],\n",
    "#                         description=measure['description'],\n",
    "#                         display_folder=measure['display_folder']\n",
    "#                     )\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ad43bddf-bc96-4839-9e83-e74b509f496b",
   "metadata": {
    "jupyter": {
     "source_hidden": false
    },
    "microsoft": {
     "language": "python",
     "language_group": "synapse_pyspark"
    }
   },
   "outputs": [],
   "source": [
    "# Rename objects in the semantic model\n",
    "with connect_semantic_model(dataset=dataset, readonly=False, workspace=workspace) as tom:\n",
    "    for t in tom.model.Tables:\n",
    "        if t.Name == 'Europe Weather Data':\n",
    "            # List of measures to add or update\n",
    "            measures = [\n",
    "                            {\n",
    "                                'name': 'Total Rainfall',\n",
    "                                'expression': \"SUM('Europe Weather Data'[Rain])\",\n",
    "                                'format_string': \"#,0.0\",\n",
    "                                'description': \"Total amount of Rainfall\",\n",
    "                                'display_folder': \"Precipitation Metrics\"\n",
    "                            },\n",
    "                            {\n",
    "                                'name': 'Total Snowfall',\n",
    "                                'expression': \"SUM('Europe Weather Data'[Snowfall])\",\n",
    "                                'format_string': \"#,0.00\",\n",
    "                                'description': \"Total amount of Snowfall\",\n",
    "                                'display_folder': \"Precipitation Metrics\"\n",
    "                            }\n",
    "                        ]\n",
    "\n",
    "            \n",
    "            # Add or update measures\n",
    "            for measure in measures:\n",
    "                if measure['name'] in [m.Name for m in t.Measures]:\n",
    "                    # Update existing measure\n",
    "                    existing_measure = t.Measures[measure['name']]\n",
    "                    existing_measure.Expression = measure['expression']\n",
    "                    existing_measure.FormatString = measure['format_string']\n",
    "                    existing_measure.Description = measure['description']\n",
    "                    existing_measure.DisplayFolder = measure['display_folder']\n",
    "                else:\n",
    "                    # Add new measure\n",
    "                    tom.add_measure(\n",
    "                        table_name=t.Name, \n",
    "                        measure_name=measure['name'], \n",
    "                        expression=measure['expression'], \n",
    "                        format_string=measure['format_string'],\n",
    "                        description=measure['description'],\n",
    "                        display_folder=measure['display_folder']\n",
    "                    )"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d6d49ff4",
   "metadata": {},
   "source": [
    "### Delete Measures"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8bb381f6-f807-4b77-b40b-194bb60da0a5",
   "metadata": {
    "microsoft": {
     "language": "python",
     "language_group": "synapse_pyspark"
    }
   },
   "outputs": [],
   "source": [
    "# with connect_semantic_model(dataset=dataset, readonly=False, workspace=workspace) as tom:\n",
    "#     for t in tom.model.Tables:\n",
    "#         if t.Name == 'Europe Weather Data':\n",
    "#             # List of measures to delete\n",
    "#             measures_to_delete = [m for m in t.Measures]\n",
    "            \n",
    "#             # Delete each measure\n",
    "#             for measure in measures_to_delete:\n",
    "#                 tom.remove_object(measure)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e27621b8",
   "metadata": {},
   "source": [
    "## Create Calculation Groups"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4db0f87a-9d70-4f2e-b3d1-7bb910cac292",
   "metadata": {
    "microsoft": {
     "language": "python",
     "language_group": "synapse_pyspark"
    }
   },
   "outputs": [],
   "source": [
    "# Define the calculation group\n",
    "with connect_semantic_model(dataset=dataset, readonly=False, workspace=workspace) as tom:\n",
    "    \n",
    "    calculation_group_name = \"Time Intelligence\"\n",
    "    precedence=1,\n",
    "    description=\"Calculation group for Time Intelligence\",\n",
    "    hidden=False\n",
    "    calculation_items = [\n",
    "        {\n",
    "            \"table_name\": \"Europe Weather Data\",\n",
    "            \"name\": \"Current\",\n",
    "            \"expression\": \"SELECTEDMEASURE()\"\n",
    "        },\n",
    "        {\n",
    "            \"table_name\": \"Europe Weather Data\",\n",
    "            \"name\": \"PY\",\n",
    "            \"expression\": \"CALCULATE(SELECTEDMEASURE(), SAMEPERIODLASTYEAR('Date Table'[Year]))\"\n",
    "        },\n",
    "        {\n",
    "            \"table_name\": \"Europe Weather Data\",\n",
    "            \"name\": \"YOY\",\n",
    "            \"expression\": \"SELECTEDMEASURE() - CALCULATE(SELECTEDMEASURE(), 'Time Intelligence'[Name] = \\\"PY\\\")\"\n",
    "        },\n",
    "        {\n",
    "            \"table_name\": \"Europe Weather Data\",\n",
    "            \"name\": \"YOY%\",\n",
    "            \"expression\": \"DIVIDE(CALCULATE(SELECTEDMEASURE(), 'Time Intelligence'[Name]=\\\"YOY\\\"), CALCULATE(SELECTEDMEASURE(), 'Time Intelligence'[Name]=\\\"PY\\\"))\"\n",
    "        }\n",
    "    ]\n",
    "    \n",
    "\n",
    "    # Retrieve all calculation groups\n",
    "    all_calculation_groups = tom.all_calculation_groups()\n",
    "\n",
    "    # Check if the calculation group already exists\n",
    "    existing_group = next((cg for cg in all_calculation_groups if cg.Name == calculation_group_name), None)\n",
    "\n",
    "    if existing_group:\n",
    "        # Update existing calculation group\n",
    "        existing_group.CalculationItems = calculation_items\n",
    "        print(f\"Updated calculation group: {calculation_group_name}\")\n",
    "    else:\n",
    "        # Add new calculation group\n",
    "        calculation_group = {\n",
    "            \"name\": calculation_group_name,\n",
    "            \"calculationItems\": calculation_items\n",
    "        }\n",
    "        tom.add_calculation_group(\n",
    "            name=calculation_group_name,  \n",
    "            precedence=1,\n",
    "            description=\"DESC \",\n",
    "            hidden=False\n",
    "        )\n",
    "\n",
    "        for item in calculation_items:\n",
    "            tom.add_calculation_item(\n",
    "                table_name=calculation_group_name,\n",
    "                calculation_item_name=item['name'],\n",
    "                expression=item['expression']\n",
    "            )\n",
    "        print(f\"Added new calculation group: {calculation_group_name}\")\n"
   ]
  }
 ],
 "metadata": {
  "dependencies": {
   "environment": {
    "environmentId": "01bf77ce-89cf-4ccb-8ec2-e4c56617759d",
    "workspaceId": "b13519ad-75cb-40af-a287-ac043f3d3f2a"
   }
  },
  "kernel_info": {
   "name": "synapse_pyspark"
  },
  "kernelspec": {
   "display_name": "PySpark",
   "language": "Python",
   "name": "synapse_pyspark"
  },
  "microsoft": {
   "language": "python",
   "language_group": "synapse_pyspark",
   "ms_spell_check": {
    "ms_spell_check_language": "en"
   }
  },
  "nteract": {
   "version": "nteract-front-end@1.0.0"
  },
  "spark_compute": {
   "compute_id": "/trident/default",
   "session_options": {
    "conf": {
     "spark.synapse.nbs.session.timeout": "1200000"
    }
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}