Cache-MDX2JSON

RESTful web api for MDX2JSON transformation (also JSONP and XML/A). Also supports requests about Dashboards and Widgets. Supports Caché 2014.1+.

Installation

1. Download Installer.cls.xml (from MDX2JSON folder in repository or releases page) and import it into any namespace (via Studio or SMP or $System.OBJ.Load())

2. Run in terminal (import namespace) under user with %All role:

    Do ##class(MDX2JSON.Installer).setup()
   

Offline Installation

1. Download zip and unpack it.

2. Import Installer.cls.xml (from MDX2JSON folder in unpacked archive) and import it into any namespace (via Studio or SMP or $System.OBJ.Load())

3. Run in terminal (same namespace as 2) under user with %All role:

    Set pVars("SourceDir") = {SourceDir}
    Do ##class(MDX2JSON.Installer).setup(.pVars)
   

where:

  {SourceDir} is a directory where you unpacked zip \ MDX2JSON (see 1).

For information on how to work with this RESTful web API please refer to included documentation.

What Installer does

Regardless of installation method chosen, here's the list (by the order of appearance) of what installer does:

1. If Namespace variable is undefined, set it to MDX2JSON
2. Create MDX2JSON role
3. If Namespace does not exist then create it
4. If SourceDir is provided then import and compile all files from there. Otherwise download import and compile all required files from GitHub
5. If /Namespace web application does not exist then create it and give it MDX2JSON role
6. If %All namespace (used for mapping purposes) does not exist then create it and map MDX2JSON package and ^MDX2JSON global there
7. Map MDX2JSON package and ^MDX2JSON global into SAMPLES namespace
8. If User and Password variables are provided, then create User and give him MDX2JSON role

Additional installation parameters

As a first parameter to Do ##class(MDX2JSON.Installer).setup(.pVars) you can pass pVars - a local array of additional variables (see sample in Offline Installation step 3).

• Namespace is a namespace you want to install MDX2JSON to (Not namespace with dashes). If it does not exist it would be created automatically. If it does exist only MDX2JSON package would be overwritten. WebApplication would be named /Namespace. Strongly not recommended to change the default. [MDX2JSON]
• User is a Caché user to create or modify. He will be given SELECT access to %DeepSee_Dashboard.Definition table in Namespace
• Password must be supplied alongside User parameters
• SourceDir - all xmls from this directory would be imported and compiled
• Import - import code from GitHub or SourceDir, defaults to 1, set to 0 to skip import

Update

1. Run in terminal (namespace where you installed MDX2JSON):

   Do ##class(MDX2JSON.Installer).Update()
   

You can also supply parameters such as fork, desired branch/commit, target namespace, authorization information. Please refer to Caché documentation of MDX2JSON.Installer class for correct syntax.

Uninstall

1. Run in terminal (any namespace from where MDX2JSON package can be accessed):

   Do ##class(MDX2JSON.Installer).Uninstall()
   

This action would delete MDX2JSON namespace, database (with physical directory) and web application. Mappings and %DB_MDX2JSON role and resource would also be deleted.

Requests

These are the possible requests to web application (add param ?Namespace={Desired Namespace} to query another namespace cubes

URL	Type	Body (JSON)	Response	Description
MDX	POST	{ "MDX":"QUERY" }	JSON	Results of MDX execution
MDX2JSONP	POST	{ "MDX":"QUERY" }	JSONP	Results of MDX execution
MDXDrillthrough	POST	{ "MDX":"QUERY" }	JSON	Results of MDX execution
MDX2XMLA	POST	{ "MDX":"QUERY" }	XMLA	Results of MDX execution
Dashboards	GET		JSON	All dashboards
Dashboards	POST	{Folder:"FolderName"}	JSON	All dashboards in FolderName. Empty FolderName for root scope.
Dashboard	POST	{Dashboard:"DashboardName"}	JSON	All widgets in a dashboard, with filters as part of dashboard
Widgets	POST	{Dashboard:"DashboardName"}	JSON	All widgets in a dashboard, with filters as part of widgets
DataSource	POST	{DataSource:"Pivot fullname"}	JSON	All info about Pivot
Action/:Cube/:Action	POST	{context object}	JSON	Execute cube action
Filters	POST	{ "DataSource": "DataSourceName.ext", "Values":1, Search:"SearchTerm", "RelatedFilters": [ {"Filter": "Filter", "Value": "Value"}]}	JSON	All filters for DeepSee DataSource (cube, pivot, kpi, metric) with values (if Values = 1, set to 0 or omit otherwise). If Search is not empty only filter values, containing search term would be returned. RelatedFilters - other filters and their values to limit search.
Format	GET		JSON	Default formatting
TermList	POST	{"TermList":"TermListName"}	JSON	Termlist key-value array
Config	POST	{"Application":"AppName", "Config":"value"}	JSON	Set config for arbitrary application for current user
Config/:Application	GET	{"Application":"AppName"}	JSON	Get config for Application for current user
Favorites	GET		JSON	Array of current user favorites
Favorites/:Item	POST		JSON	Add favorite item
Favorites/:Item	DELETE		JSON	Remove item from favorites
PivotVariables/:Cube	GET		JSON	Get all pivot variables for cube
Test	GET		JSON	Test info
Logout	GET		JSON	Close session

Example

Request URL: http://localhost:57772/MDX2JSON/MDX?Namespace=Samples

Request type: POST

Request body:

{"MDX": "SELECT NON EMPTY [Product].[P1].[Product Category].Members ON 0,NON EMPTY [Outlet].[H1].[Region].Members ON 1 FROM [HoleFoods]"} 

Result

Please note that corresponding cube must be compiled and built beforehand.

Localization

If requested data or meta-information is available in several different languages, you can choose one, by supplying Accept-Language header to your HTTP request, formed in accordance with RFC 2616.

Troubleshooting

If something goes wrong, server must report an error in the following format {Error : "Error description"}, and usually that is a good indicator of what went wrong. If you received an error in another format or an error without "Error description" please file an issue here.

Problem	Solution
CSP Error	User does not have enough rights. Configure User or Webapplication roles
Authenticated access	Web application must have password access, resource for database with MDX2JSON must have public RW rights enabled
No dashboards	Configure roles. Change dashboard scope
DeepSee errors	Build and compile DeepSee cube(s)
MDX errors	Don't forget to escape JSON strings here
Installation errors	Usually problems arise when installation is run under user without %All permissions. To repair the install rerun it under correct user. If the error persists then file an issue with installation log (terminal output) attached

Debugging

Use $$$Debug macro. It would evaluate as true only if there is a "Debug" URL parameter present. Example request URL:

    http://localhost:57772/MDX2JSON/MDX?Namespace=Samples&Debug

Use with post conditional expressions, or other flow control statements:

	w:$$$Debug "debugging"
	if $$$Debug { w "debugging" } else { w "not debugging"}

Also available are $$$Public and $$$Private macros. Evaluates to true based on request port (80 and 443 are public, private otherwise).

Querying

Querying this project is done via REST web client. It may be a standalone application or a browser plug-in, a number of different solutions are available.

For Google Chrome, install Advanced REST client extension. For Firefox, install REST client extension. Open installed extension and set the following parameters:

• URL to required web api method, e.g.: http://serverip:port/mdx2json/Dashboard?Namespace=Samples
• Request type to GET or POST
• Payload to {"Dashboard":"Listing with Filters.dashboard"}
• Content-Type to application/json (only in Advanced REST client)

Press Send button to view results (depending on your server configuration you may be asked to provide valid login/password to access MDX2JSON api).

Settings

User can save and get arbitrary settings for an abstract application (usually - Namespace). Settings are accessible throughout the system. To set a setting for a user, execute: do ##class(MDX2JSON.Users).SetConfig(Application, SettingsValue, Username) or send a request at a corresponding POST /Config with the JSON body containing Application property and Config property which can be a string or a JSON object. Note that in a WEB context user is not allowed to specify a username, it's calculated automatically. To get a setting for an abstract application write ##class(MDX2JSON.Users).GetConfig(Application, Username) or send a request at a corresponding GET /Config/:Application path.

User can have a MDX2JSONSettings role which allows him to get/set a default setting for an application. If the user (any user) does not have a setting for an Application, the default setting for an application would be used.

Calling MDX2JSON from another server

Here's an example.

Development

To develop MDX2JSON you need:

1. Install MDX2JSON in MDX2JSON namespace

2. Install Cache-Tort-Git

3. In terminal, MDX2JSON namespace execute:

    set ^Git("settings","hook") = $lb("MDX2JSON.Tests","OnCommit")
    set ^Git("settings","groupByFolder") = 1
   

4. Activate Cache-Tort-Git for MDX2JSON namespace

5. Commit all changes via Studio

KPI

To use KPIs and display row name add this method to KPI class

ClassMethod %OnGetKPIPropertyInfo(ByRef pList As %String, pPropNo As %Integer, pModelId As %String = "") As %Status
{
    Set pPropNo = pPropNo + 1
    Set pList(pPropNo) = "%series"
    Set pList(pPropNo, "defaultValue") = ""
    Set pList(pPropNo, "columnNo") = pPropNo
    Quit $$$OK
}

Postman

You can use Postman to query MDX2JSON API. Collection. Environment.