Prior to using the ExoInsight Alteryx Integration with Microsoft Analysis Services, ExoInsight for SSAS must be installed and configured. Please refer to the ExoInsight for SSAS Installation Guide for detailed instructions.
Once installed, you can pull the ExoInsight Alteryx Connector for SSAS into the Alteryx canvas. The connector will be available under the Connectors menu.
Once you pull into the Alteryx canvas, you’ll need to connect to your SSAS instance:
ExoInsight URL: This is the ExoInsight instance. If installed locally, you can leave the URL as-is. If installed on a remote server, you’ll need to update the URL to point to the appropriate place.
SSAS Server name or IP: The server name or IP address of your SSAS instance.
SSAS Username: The username you use to log into SSAS. If using Windows authentication, this can be left blank.
SSAS Password: The password you use to log into SSAS. If using Windows authentication, this can be left blank.
Once you have logged in, you’ll be presented with drop-downs to select the catalog, cube, and measure groups that you have access to.
Using the POV String Builder
The POV String is a proprietary language developed by Casabase Software to make it easy to query your OLAP data sources such as SSAS. The POV String defines a slice of data that you want to work with from your SSAS cubes.
The ExoInsight Alteryx Connector for SSAS comes with a POV String Builder functionality that you can access directly in the Alteryx Plug-in. You can also access this functionality with the following URL in a Chrome or Firefox browser window:
http://localhost:9190/ExoInsight_SSAS
If you installed ExoInsight on a remote server, then localhost would need to be replaced with that host name.
To start building your POV String, simply click the button next to the member names you want to pull in, or right click a hierarchy and select a set operator such as Children, Descendants, etc:
A valid POV String for ExoInsight for SSAS consists of the following:
- A minimum of one member from the Measures dimension
- A minimum of one member from two other dimensions
As you select your members, the POV String will be dynamically built in the box above:
You can then click the Run query button to display the results to make sure you’re getting what you expected:
Pulling Dimensions and Hierarchies
The ExoInsight Alteryx Connector for SSAS also allows you to pull dimension and hierarchy information from your SSAS sources. To do this, simply enter the dimension/hierarchy name into the input box:
When you run the workflow, the hierarchy information will be returned
MDX Support
The ExoInsight Alteryx Connector for SSAS supports all SSAS MDX. To use MDX, enter the MDX statement into the input box.
DAX Support
The ExoInsight Alteryx Connector for SSAS supports all SSAS DAX. To use DAX, enter the DAX statement into the input box.
Passing in Parameters
Instead of manually entering your ExoInsight URL, application name, query, etc, you can instead dynamically pass in these parameters as part of your workflow. If you choose to pass in parameters, they all must be present in the Text Input node. The required parameters are as follows:
url: The ExoInsight URL
server: The SSAS server
username: Leave blank (Windows Authentication is used)
password: Leave blank (Windows Authentication is used)
catalog: The SSAS catalog name
cube: The SSAS cube name
entry: Either a) A valid POV String, b) A valid MDX statement, c) a valid DAX statement, d) Dimension name
Parameters that are passed in will override any parameter values manually entered into the ExoInsight Alteryx Plug-in. It is recommended that you keep the default values of the ExoInsight SSAS Output plug-in and do not manually log in since all these parameter values will be passed in instead.