The Python Analysis transform lets you write scripts using the Python programming language to perform analysis on data. To learn more about the Python language, see python.org.
Before you can use the Python Analysis transform in Dundas BI, the Python programming environment must be installed on the server.
See Install Python for more details.
The Python Analysis transform requires one or more input transforms (each of which may produce multiple columns of data).
For example, the input could be a SQL Select transform that reads data from the AdventureWorks table, [Sales].[SalesOrderDetail].
3. Add the transform
To add this transform to an existing data cube, first select the connection link between two connected transforms.
Go to the toolbar, click Insert Other, and then select Python Analysis.
The Python Analysis transform is inserted between the two transforms and appears red because it needs further configuration.
4. Configure the transform
Double-click the Python Analysis transform (You can also select the Configure option from the context menu or the toolbar).
In the configuration dialog for the transform, the objective is to decide which input column(s) you want to analyze or apply processing to, and then enter a script that performs this analysis or processing on the input data.
For example, suppose you want to calculate the total sum of the OrderQty column from the input transform, and send this sum as the output.
To begin, click Define element placeholders in the configuration dialog. Click Add placeholder. In the Identifier text box, enter a variable name (for example, qty) for the input column which you will use in the Python script. Use the dropdown to select the corresponding input column (for example, OrderQty).
You can now write a Python script that references the qty variable by enclosing it between dollar sign characters. For example:
This script calculates the sum of the OrderQty input column and returns the result as the output.
The output of the Python Analysis transform depends on the Python script it is configured with. It can be a single value, a column of values, or multiple columns.
6. Example Python script
The following example uses the polyfit function to find the least squares polynomial fit for the input columns.
This particular example relies on the NumPy package in Python and some input values:
- To install the NumPy package, open command prompt as an administrator, navigate to the Python scripts folder (for example, C:\Program Files\Python36\Scripts), and type:
pip install numpy
- On a new data cube, create a Data Input transform with the following two columns using the Double data type:
- X: [0.0, 1.0, 2.0, 3.0, 4.0, 5.0]
- Y: [0.0, 0.8, 0.9, 0.1, -0.8, -1.0]
6.2. Define placeholders
Add the Python Analysis transform and double-click to configure it. Click Define element placeholders, and then define the two placeholders X and Y.
6.3. Create the script
To fit the input values to a third-power polynomial, add the following script to the Python Analysis transform:
import numpy as np Z = np.polyfit($X$, $Y$, 3) P = np.poly1d(Z) return ($X$, $Y$, P($X$))
The generated output is in the form of a table consisting of the X and Y inputs, as well as the polynomial values for each entry.
6.4. Adjust the column names and the output
Configure the transform again and click Edit output elements.
Edit each output element and provide a relevant column name.
Select the Process Result transform and edit the X measure. Click Switch to hierarchy and then select Count from the supported aggregators.
6.5. View the result
On a new dashboard, drag all three elements from the data cube and Re-Visualize as a Curved Line.
7. See also