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-CatCommandallows you to examine result of a plain query sent to a datasource of your choice.
How Does It Work?
The command has two parameters -
Command. That means, you already must have a CAT project open.
So first, open your project:
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
Command parameter expects a string with a query, usualy SQL. But this is provider dependant, against
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
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.