{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# ASAM ODS data for Microsoft Power BI Desktop\n",
    "\n",
    "This notebook provides simple examples accessing ASAM ODS data with [Microsoft Power BI Desktop](https://www.microsoft.com/power-platform/products/power-bi/desktop) using the [ASAM ODSBox](https://peak-solution.github.io/odsbox/) in combination with Python. \n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Prerequisites"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To work with ASAM ODS data in Microsoft Power BI Desktop you have to install [Python](https://www.python.org/) and the [ASAM ODSBox](https://pypi.org/project/odsbox/) on your local computer. \n",
    "\n",
    "Look into this article Microsoft article for further requirements how to [Run Python scripts in Power BI Desktop](https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-python-scripts).\n",
    "\n",
    "You can then copy these examples directly into Microsoft Power BI and [Run the script and import data](https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-python-scripts#run-the-script-and-import-data)\n",
    "\n",
    "The ODS HTTP API is a session based API. The session ID is called conI in the ODS documentation. The ASAM ODSBox uses con_i as API object representing the session. \n",
    "\n",
    "Note: Use `con_i` in a `with` clause in your Python project to automatically close the session after importing the data."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Read Measurement Data\n",
    "First we query for a submatrix containing the bulk data structure (time series data) of a certain measurement. \n",
    "We use this submatrix to query for bulk data in the next step - directly converted to a DataFrame ...\n",
    "\n",
    "Try different query options ... "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "from odsbox.con_i import ConI\n",
    "from odsbox.submatrix_to_pandas import submatrix_to_pandas\n",
    "\n",
    "with ConI(url='http://79.140.180.128:10032/api', auth=('Demo','mdm'), verify_certificate=False) as con_i:\n",
    "    # find submatrix (bulk root) of a certain measurement\n",
    "    submatrices = con_i.query_data(\n",
    "        {\n",
    "            \"AoSubmatrix\": {\"measurement.name\": {\"$like\": \"Profile_68\"}},\n",
    "            \"$attributes\": {\"name\": 1, \"id\": 1},\n",
    "            \"$options\": {\"$rowlimit\": 50},\n",
    "        }\n",
    "    )\n",
    "\n",
    "    # get the bulk data of first submatrix\n",
    "    df = submatrix_to_pandas(con_i, submatrices.iloc[0,1])\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The code below simply verifies that data has actually been loaded and it should looks the same in Microsoft Power BI 💪"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Time</th>\n",
       "      <th>U_q</th>\n",
       "      <th>Coolant</th>\n",
       "      <th>Stator_winding</th>\n",
       "      <th>U_d</th>\n",
       "      <th>Stator_tooth</th>\n",
       "      <th>Motor_speed</th>\n",
       "      <th>I_d</th>\n",
       "      <th>I_q</th>\n",
       "      <th>Pm</th>\n",
       "      <th>Stator_yoke</th>\n",
       "      <th>Ambient</th>\n",
       "      <th>Torque</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.0</td>\n",
       "      <td>-1.250622</td>\n",
       "      <td>26.948739</td>\n",
       "      <td>26.401552</td>\n",
       "      <td>-0.269267</td>\n",
       "      <td>26.165591</td>\n",
       "      <td>-0.003114</td>\n",
       "      <td>-2.000875</td>\n",
       "      <td>1.097745</td>\n",
       "      <td>28.011248</td>\n",
       "      <td>26.141670</td>\n",
       "      <td>26.280088</td>\n",
       "      <td>2.785112e-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.5</td>\n",
       "      <td>-0.510442</td>\n",
       "      <td>26.950800</td>\n",
       "      <td>26.396721</td>\n",
       "      <td>0.110050</td>\n",
       "      <td>26.172235</td>\n",
       "      <td>21.150647</td>\n",
       "      <td>-4.751546</td>\n",
       "      <td>-14.421556</td>\n",
       "      <td>28.002123</td>\n",
       "      <td>26.155253</td>\n",
       "      <td>26.275925</td>\n",
       "      <td>-1.122167e+01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1.0</td>\n",
       "      <td>-0.388580</td>\n",
       "      <td>26.953776</td>\n",
       "      <td>26.408694</td>\n",
       "      <td>0.907830</td>\n",
       "      <td>26.177153</td>\n",
       "      <td>42.000488</td>\n",
       "      <td>-15.815420</td>\n",
       "      <td>-49.228154</td>\n",
       "      <td>27.996198</td>\n",
       "      <td>26.159354</td>\n",
       "      <td>26.280659</td>\n",
       "      <td>-3.799358e+01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1.5</td>\n",
       "      <td>-0.260576</td>\n",
       "      <td>26.962654</td>\n",
       "      <td>26.401838</td>\n",
       "      <td>1.678359</td>\n",
       "      <td>26.181832</td>\n",
       "      <td>60.023822</td>\n",
       "      <td>-25.347121</td>\n",
       "      <td>-77.327501</td>\n",
       "      <td>27.993540</td>\n",
       "      <td>26.173699</td>\n",
       "      <td>26.280193</td>\n",
       "      <td>-5.986944e+01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2.0</td>\n",
       "      <td>0.134215</td>\n",
       "      <td>26.948778</td>\n",
       "      <td>26.396926</td>\n",
       "      <td>2.581367</td>\n",
       "      <td>26.172657</td>\n",
       "      <td>80.657265</td>\n",
       "      <td>-32.076248</td>\n",
       "      <td>-97.315560</td>\n",
       "      <td>28.001602</td>\n",
       "      <td>26.181490</td>\n",
       "      <td>26.277929</td>\n",
       "      <td>-7.541367e+01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Time       U_q    Coolant  Stator_winding       U_d  Stator_tooth  \\\n",
       "0   0.0 -1.250622  26.948739       26.401552 -0.269267     26.165591   \n",
       "1   0.5 -0.510442  26.950800       26.396721  0.110050     26.172235   \n",
       "2   1.0 -0.388580  26.953776       26.408694  0.907830     26.177153   \n",
       "3   1.5 -0.260576  26.962654       26.401838  1.678359     26.181832   \n",
       "4   2.0  0.134215  26.948778       26.396926  2.581367     26.172657   \n",
       "\n",
       "   Motor_speed        I_d        I_q         Pm  Stator_yoke    Ambient  \\\n",
       "0    -0.003114  -2.000875   1.097745  28.011248    26.141670  26.280088   \n",
       "1    21.150647  -4.751546 -14.421556  28.002123    26.155253  26.275925   \n",
       "2    42.000488 -15.815420 -49.228154  27.996198    26.159354  26.280659   \n",
       "3    60.023822 -25.347121 -77.327501  27.993540    26.173699  26.280193   \n",
       "4    80.657265 -32.076248 -97.315560  28.001602    26.181490  26.277929   \n",
       "\n",
       "         Torque  \n",
       "0  2.785112e-07  \n",
       "1 -1.122167e+01  \n",
       "2 -3.799358e+01  \n",
       "3 -5.986944e+01  \n",
       "4 -7.541367e+01  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Read Descriptive Data\n",
    "In the query below we retrieve some descriptive information about a certain set of measurements including information of referenced objects - in this case the so called test hierarchy. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "from odsbox.con_i import ConI\n",
    "from odsbox.submatrix_to_pandas import submatrix_to_pandas\n",
    "\n",
    "with ConI(url='http://79.140.180.128:10032/api', auth=('Demo','mdm'), verify_certificate=False) as con_i:\n",
    "\n",
    "    # get measurement information\n",
    "    measurement_df = con_i.query_data(\n",
    "        {\n",
    "            \"AoMeasurement\": {\"name\": {\"$like\": \"Profile*\"}},\n",
    "            \"$attributes\": {\n",
    "                \"name\": 1,\n",
    "                \"measurement_begin\": 1,\n",
    "                \"test\": {\n",
    "                    \"name\": 1,\n",
    "                    \"parent_test\": {\n",
    "                        \"name\": 1,\n",
    "                        \"parent_test\": {\n",
    "                            \"name\": 1,\n",
    "                            \"parent_test\": {\"name\": 1},\n",
    "                        },\n",
    "                    },\n",
    "                },\n",
    "            },\n",
    "        },\n",
    "        date_as_timestamp=True \n",
    "    )\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>MeaResult.Name</th>\n",
       "      <th>MeaResult.MeasurementBegin</th>\n",
       "      <th>TestStep.Name</th>\n",
       "      <th>Test.Name</th>\n",
       "      <th>StructureLevel.Name</th>\n",
       "      <th>Project.Name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Profile_73</td>\n",
       "      <td>2024-08-10</td>\n",
       "      <td>Profile_73</td>\n",
       "      <td>Campaign_06</td>\n",
       "      <td>ElectricMotorTemperature</td>\n",
       "      <td>ElectricMotorTemperature</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Profile_62</td>\n",
       "      <td>2024-08-12</td>\n",
       "      <td>Profile_62</td>\n",
       "      <td>Campaign_05</td>\n",
       "      <td>ElectricMotorTemperature</td>\n",
       "      <td>ElectricMotorTemperature</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Profile_65</td>\n",
       "      <td>2024-08-14</td>\n",
       "      <td>Profile_65</td>\n",
       "      <td>Campaign_06</td>\n",
       "      <td>ElectricMotorTemperature</td>\n",
       "      <td>ElectricMotorTemperature</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Profile_63</td>\n",
       "      <td>2024-08-17</td>\n",
       "      <td>Profile_63</td>\n",
       "      <td>Campaign_05</td>\n",
       "      <td>ElectricMotorTemperature</td>\n",
       "      <td>ElectricMotorTemperature</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Profile_43</td>\n",
       "      <td>2024-08-19</td>\n",
       "      <td>Profile_43</td>\n",
       "      <td>Campaign_04</td>\n",
       "      <td>ElectricMotorTemperature</td>\n",
       "      <td>ElectricMotorTemperature</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  MeaResult.Name MeaResult.MeasurementBegin TestStep.Name    Test.Name  \\\n",
       "0     Profile_73                 2024-08-10    Profile_73  Campaign_06   \n",
       "1     Profile_62                 2024-08-12    Profile_62  Campaign_05   \n",
       "2     Profile_65                 2024-08-14    Profile_65  Campaign_06   \n",
       "3     Profile_63                 2024-08-17    Profile_63  Campaign_05   \n",
       "4     Profile_43                 2024-08-19    Profile_43  Campaign_04   \n",
       "\n",
       "        StructureLevel.Name              Project.Name  \n",
       "0  ElectricMotorTemperature  ElectricMotorTemperature  \n",
       "1  ElectricMotorTemperature  ElectricMotorTemperature  \n",
       "2  ElectricMotorTemperature  ElectricMotorTemperature  \n",
       "3  ElectricMotorTemperature  ElectricMotorTemperature  \n",
       "4  ElectricMotorTemperature  ElectricMotorTemperature  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "measurement_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## License\n",
    "\n",
    "Copyright © 2024 [Peak Solution GmbH](https://peak-solution.de)\n",
    "\n",
    "The training material in this repository is licensed under a Creative Commons BY-NC-SA 4.0 license. See [LICENSE](../LICENSE) file for more information."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": ".venv",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}