Invoke Command

This command is useful for querying CAT data sources.

When Should I Use It? #

When working with some providers, you don’t have any GUI for displaying results of queries. This makes authoring and troubleshooting tests painful and cumbersome. And that is exactly the situation when Invoke-CatCommand comes in handy.

Invoke-CatCommand allows you to examine result of a plain query sent to a datasource of your choice.

How Does It Work? #

The command has two parameters - DataSourceName and Command. That means, you already must have a CAT project open.

So first, open your project:

Open-CatProject "D:\TestProjects\Aero\Aero.cat.yaml"

If you already run “shortcut” command Invoke-CatProject, you don’t have to, your project is already open in your session.

CAT is now aware of data sources you defined (even if they are loaded from an external source). Just pass a name of an existing data source to the DataSourceName parameter.

Command parameter expects a string with a query, usualy SQL. But this is provider dependant, against PowerBI@1 or Dax@1 providers you need to specify DAX.

Your command will look something like this:

Open-CatProject "D:\TestProjects\Outputs\Aero.cat.yaml"
$query = 'SELECT * FROM DIM.GATES'
(Invoke-CatCommand -DataSourceName 'AERO_DWH' -Command $query).Data

The result has two properties - Data, with actual data CAT retrieved from the data source, and Messages with any messages the client got form the server (messages are so far implemented only for SqlServer@1).

Tips #

This feature is NOT designed for large amounts of data. Don’t use it for large datasets, otherwise you might experience freezing. It’s OK to use it for tousands of rows, but for more the performance will degrade.

The returned data are objects, so you can leverage all PowerShell bells and whistles, like sorting, filtering, grouping, formatting, covnerting etc. E.g., if you don’t like PowerShell and want to see the data rather in Excel, do this:

(Invoke-CatCommand -DataSourceName 'AERO_DWH' -Command $query).Data | Export-Csv 'MyData.csv'

This feature was introduced mainly to support GUI (CAT team is already working on it), but may be useful also for troubleshooting in automated pipelines.