ASAM ODS data for Microsoft Power BI Desktop

ASAM ODS data for Microsoft Power BI Desktop#

This notebook provides simple examples accessing ASAM ODS data with Microsoft Power BI Desktop using the ASAM ODSBox in combination with Python.

Prerequisites#

To work with ASAM ODS data in Microsoft Power BI Desktop you have to install Python and the ASAM ODSBox on your local computer.

Look into this article Microsoft article for further requirements how to Run Python scripts in Power BI Desktop.

You can then copy these examples directly into Microsoft Power BI and Run the script and import data

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.

Note: Use con_i in a with clause in your Python project to automatically close the session after importing the data.

Read Measurement Data#

First we query for a submatrix containing the bulk data structure (time series data) of a certain measurement. We use this submatrix to query for bulk data in the next step - directly converted to a DataFrame …

Try different query options …

from odsbox.con_i import ConI
from odsbox.submatrix_to_pandas import submatrix_to_pandas

with ConI(url='http://79.140.180.128:10032/api', auth=('Demo','mdm'), verify_certificate=False) as con_i:
    # find submatrix (bulk root) of a certain measurement
    submatrices = con_i.query_data(
        {
            "AoSubmatrix": {"measurement.name": {"$like": "Profile_68"}},
            "$attributes": {"name": 1, "id": 1},
            "$options": {"$rowlimit": 50},
        }
    )

    # get the bulk data of first submatrix
    df = submatrix_to_pandas(con_i, submatrices.iloc[0,1])

The code below simply verifies that data has actually been loaded and it should looks the same in Microsoft Power BI 💪

df.head()

	Time	U_q	Coolant	Stator_winding	U_d	Stator_tooth	Motor_speed	I_d	I_q	Pm	Stator_yoke	Ambient	Torque
0	0.0	-1.250622	26.948739	26.401552	-0.269267	26.165591	-0.003114	-2.000875	1.097745	28.011248	26.141670	26.280088	2.785112e-07
1	0.5	-0.510442	26.950800	26.396721	0.110050	26.172235	21.150647	-4.751546	-14.421556	28.002123	26.155253	26.275925	-1.122167e+01
2	1.0	-0.388580	26.953776	26.408694	0.907830	26.177153	42.000488	-15.815420	-49.228154	27.996198	26.159354	26.280659	-3.799358e+01
3	1.5	-0.260576	26.962654	26.401838	1.678359	26.181832	60.023822	-25.347121	-77.327501	27.993540	26.173699	26.280193	-5.986944e+01
4	2.0	0.134215	26.948778	26.396926	2.581367	26.172657	80.657265	-32.076248	-97.315560	28.001602	26.181490	26.277929	-7.541367e+01

Read Descriptive Data#

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.

from odsbox.con_i import ConI
from odsbox.submatrix_to_pandas import submatrix_to_pandas

with ConI(url='http://79.140.180.128:10032/api', auth=('Demo','mdm'), verify_certificate=False) as con_i:

    # get measurement information
    measurement_df = con_i.query_data(
        {
            "AoMeasurement": {"name": {"$like": "Profile*"}},
            "$attributes": {
                "name": 1,
                "measurement_begin": 1,
                "test": {
                    "name": 1,
                    "parent_test": {
                        "name": 1,
                        "parent_test": {
                            "name": 1,
                            "parent_test": {"name": 1},
                        },
                    },
                },
            },
        },
        date_as_timestamp=True 
    )

measurement_df.head()

	MeaResult.Name	MeaResult.MeasurementBegin	TestStep.Name	Test.Name	StructureLevel.Name	Project.Name
0	Profile_73	2024-08-10	Profile_73	Campaign_06	ElectricMotorTemperature	ElectricMotorTemperature
1	Profile_62	2024-08-12	Profile_62	Campaign_05	ElectricMotorTemperature	ElectricMotorTemperature
2	Profile_65	2024-08-14	Profile_65	Campaign_06	ElectricMotorTemperature	ElectricMotorTemperature
3	Profile_63	2024-08-17	Profile_63	Campaign_05	ElectricMotorTemperature	ElectricMotorTemperature
4	Profile_43	2024-08-19	Profile_43	Campaign_04	ElectricMotorTemperature	ElectricMotorTemperature

License#

The training material in this repository is licensed under a Creative Commons BY-NC-SA 4.0 license. See LICENSE file for more information.