1. API Interface

1.1 Dimension

/dimension/list

Usage: List Dimensions

JSON Input:

 { "prefix": "string" }    prefix: optional

/dimension/get

Usage: Get Dimensions
JSON Input:
```
 {  "name": "dim_name" } 
```

/dimension/set

Usage: Set Dimensions
JSON Input:

{
	"overwrite_mode" : "False",   // Default, append mode   
	  "name": "Version",
	  "elements": [
		"Actual",
		"Budget",
	"Forecast"
	  ],
	  "edges": [ ]    // if defined, then overwrite old definition in system
}

or
{
	"overwrite_mode" : "True",   
	"name": "Version",
	"elements": [
		{"name": "Actual", "type": "N", "index": 1},
		{"name": "Budget", "type": "N", "index": 2},
		{"name": "Forecast", "type": "N", "index": 3}
	]
}

Or
{
  "name": "Year",
  "elements": [
	"All_Years",
	"FY23",
	"FY24",
	"FY25",
	"FY26",
	"FY27"
  ],
  "edges": [
	["All_Years", "FY23"],
	["All_Years", "FY24"],
	["All_Years", "FY25"],
	["All_Years", "FY26"],
	["All_Years", "FY27"]
  ] 
}

Or
{
  "name": "Month",
  "elements": [
	"Full_Year",
	"Q1", "Q2", "Q3", "Q4",
	"Jan", "Feb", "Mar", "Apr", "May", "Jun",
	"Jul", "Aug", "Sep", "Oct", "Nov", "Dec"
  ],
  "edges": [
	["Full_Year", "Q1"],
	["Full_Year", "Q2"],
	["Full_Year", "Q3"],
	["Full_Year", "Q4"],
	["Q1", "Jan"],
	["Q1", "Feb"],
	["Q1", "Mar"],
	["Q2", "Apr"],
	["Q2", "May"],
	["Q2", "Jun"],
	["Q3", "Jul"],
	["Q3", "Aug"],
	["Q3", "Sep"],
	["Q4", "Oct"],
	["Q4", "Nov"],
	["Q4", "Dec"]
  ]
}

Or
{
  "name": "Measure",
  "elements": [
	"Sales",
	"COGS",
	"Gross Profit",
	"Admin expenses",
	"R&D expenses",
	"EBIT",
	"Finance cost",
	"EBT",
	"Income tax",
	"Net Profit"
  ],
  "edges": [
	["Net Profit", "Income tax", -1],
	["Net Profit", "EBT"],
	["EBT", "Finance cost", -1],
	["EBT", "EBIT"],
	["EBIT", "R&D expenses", -1],
	["EBIT", "Admin expenses", -1],
	["EBIT", "Gross Profit"],
	["Gross Profit", "COGS", -1],
	["Gross Profit", "Sales"]
  ]
}

/dimension/load

Usage: Load Dimension From CSV

JSON Input:

 // file_path: relative path to instance folder, use “/” or “\\” on windows
 // type:  "hierarchy_levels" or “parent_child”
 	- hierarchy_levels indicates hierarchy / wide-table mode;
 	- parent_child indicates long-table mode, where the data source has only two columns (child, parent). The hierarchy depth is not fixed (ragged hierarchy).
 
 Syntax：
 {
   "dimension_name": "Product",
   "overwrite_mode": true,
   
   "source": {
 	"file_path": "Imports/products.csv",   
 	"encoding": "utf-8",
 	"has_header": true,
 	"delimiter": ","
   },
 
   "mapping": {
 	"hierarchy": {
 	  "type": "hierarchy_levels", 
 	  "columns": ["Product Code", "Sub Category", "Category"],
 	  "weight_column": "Weight"    // default is 1
 	},

 	// if no attribute, remove below items or leave them in blank
 	"attributes": {  
 	  "Alias": "Product Name",
 	  "Color": "Color",
 	  "Price": "Price"
 	},
 	
 	"attribute_types": {  
 		"Price": "Numeric",
 		"Color": "String"
 	}
   }
 }

 Sample:
 {
   "dimension_name": "Product",
   "overwrite_mode": true, 
   "source": {
 	"file_path": "Source/Products.csv",
 	"encoding": "utf-8",
 	"has_header": true,
 	"delimiter": ","
   },
   "mapping": {
 	"hierarchy": {
 	  "type": "hierarchy_levels",
 	  "columns": ["Product", "Category", "Top"]
 	}
   }
 }
 
 Or
 
 {
   "dimension_name": "Product_with_Attr",
   "overwrite_mode": true, 
   "source": {
 	"file_path": "Source/Product_attr.csv",
 	"encoding": "utf-8",
 	"has_header": true,
 	"delimiter": ","
   },
   "mapping": {
 	"hierarchy": {
 	  "type": "hierarchy_levels",
 	  "columns": ["Product", "ProductCategory", "Top"]
 	}
 	"attributes": { "Category": "Category" },
 	"attribute_types": {  "Category": "String" }
   }
 }
 
 Or
 
 {
   "dimension_name": "month_with_attr",
   "overwrite_mode": true, 
   "source": {
 	"file_path": "Source/months_with_attr.csv",
 	"encoding": "utf-8",
 	"has_header": true,
 	"delimiter": ","
   },
   "mapping": {
 	"hierarchy": {
 	  "type": "hierarchy_levels",
 	  "columns": ["Month", "Quarter", "Top"]
 	},
 	"attributes": { "Prev": "Prev",
 					"Next": "Next",
 					"ChineseName": "ChineseName"
 	},
 	"attribute_types": {  "Prev": "String",
 						  "Next": "String",
 						  "ChineseName": "Alias"
 	}
   }
 }

/dimension/extract_hierarchy

Usage: Extract Dimension Hierarchy into Flat

JSON Input:

 // support "json_records","json_compact","csv"
 // file_folder: relative folder to instance main folder, default “Export”, if absolute folder, it must be under instance main folder; filename like "Dim_<dim_name>_YYYYMMDD_HHMMSS.csv"
 {
   "dimension": "dim_name",
   "output_format": "csv", 
   "csv_sep": ",",             // only for csv
   "file_folder": "string"  
 }

/dimension/reindex

Usage: Reindex Dimension

JSON Input:

 Syntax:
 Auto Reindex 
 {
   "dimension": "Region",
   "mode": "alpha",           // "alpha" | "numeric" | "manual"
   "sort_order": "desc"       // "asc" (default) | "desc"
 }

 Manual Reindex
 // elements sort according to order_list, for those not in list, then sort according to "sort_order" with mode "alpha"
 {
   "dimension": "Month",
   "mode": "manual",      
   "sort_order": "asc",     // "asc" | "desc", default "asc"
   "order_list": [
 	"Total_Year",
 	"Q1", "Jan", "Feb", "Mar",
 	"Q2", "Apr", "May", "Jun",
 	"Q3", "Jul", "Aug", "Sep",
 	"Q4", "Oct", "Nov", "Dec"
   ]
 }
 
 Sample:
 {
   "dimension": "Month",
   "mode": "manual",      
   "sort_order": "asc",  
   "order_list": [
 	"Jan", "Feb", "Mar", "Apr", "May", "Jun",
 	"Jul", "Aug", "Sep", "Oct", "Nov", "Dec",
 	"Q1", "Q2", "Q3", "Q4", 
 	"Total_Year"
   ]
 }

/dimension/delete

Usage: Delete Dimension
JSON Input:
```
 { "name": "dim_name" }
```

1.2 Attribute

/dimension/attribute/list

Usage: List Attribute
JSON Input:
```
 { "dimension": "dim_name" }
```

/dimension/attribute/get

Usage: Get Attribute

JSON Input:

 {  "dimension": "dim_name" }
 Or
 { 
 	"dimension": "dim_name",
 	"attribute" : "attr_name"
 }

/dimension/attribute/set

Usage: Set Attribute

JSON Input:

 {
   "dimension": "Measure",
   "attribute": "Accrued_Category",
   "type": "String", 
   "items": {
      "Sales": "Credit",
      "COGS": "Debit",
      "Gross Profit": "Credit",
      "Admin expenses": "Debit",
      "R&D expenses": "Debit",
      "EBIT": "Credit",
      "Finance cost": "Debit",
      "EBT": "Credit",
      "Income tax": "Debit",
      "Net Profit": "Credit"
   }
 }
 
 or
 
 {
   "dimension": "Month",
   "attribute": "EnglishName",
   "type": "Alias", 
   "items": {
 	  "Jan": "January",
 	  "Feb": "February",
 	  "Mar": "March",
 	  "Apr": "April",
 	  "May": "May",
 	  "Jun": "June",
 	  "Jul": "July",
 	  "Aug": "August",
 	  "Sep": "September",
 	  "Oct": "October",
 	  "Nov": "November",
 	  "Dec": "December"
   }
 }

/dimension/attribute/set_batch

Usage: Set Attribute in Batch

JSON Input:

 {
   "dimension": "Month",
   "attributes": [
     {
       "name": "EnglishName",
       "type": "Alias", 
       "items": {
         "Jan": "January",
         "Feb": "February",
         "Mar": "March",
         "Apr": "April",
         "May": "May",
         "Jun": "June",
         "Jul": "July",
         "Aug": "August",
         "Sep": "September",
         "Oct": "October",
         "Nov": "November",
         "Dec": "December"
       }
     },
     {
       "name": "ChineseName",
       "type": "Alias",
       "items": {
         "Jan": "一月",
         "Feb": "二月",
         "Mar": "三月",
         "Apr": "四月",
         "May": "五月",
         "Jun": "六月",
         "Jul": "七月",
         "Aug": "八月",
         "Sep": "九月",
         "Oct": "十月",
         "Nov": "十一月",
         "Dec": "十二月"
       }
     },
     {
       "name": "MonthNum",
       "type": "Numeric",
       "items": {
         "Jan": 1,
         "Feb": 2,
         "Mar": 3,
         "Apr": 4,
         "May": 5,
         "Jun": 6,
         "Jul": 7,
         "Aug": 8,
         "Sep": 9,
         "Oct": 10,
         "Nov": 11,
         "Dec": 12
       }
     },
     {
       "name": "Prev",
       "type": "String",
       "items": {
         "Jan": "Dec",
         "Feb": "Jan",
         "Mar": "Feb",
         "Apr": "Mar",
         "May": "Apr",
         "Jun": "May",
         "Jul": "Jun",
         "Aug": "Jul",
         "Sep": "Aug",
         "Oct": "Sep",
         "Nov": "Oct",
         "Dec": "Nov"
       }
     },
     {
       "name": "Next",
       "type": "String",
       "items": {
         "Jan": "Feb",
         "Feb": "Mar",
         "Mar": "Apr",
         "Apr": "May",
         "May": "Jun",
         "Jun": "Jul",
         "Jul": "Aug",
         "Aug": "Sep",
         "Sep": "Oct",
         "Oct": "Nov",
         "Nov": "Dec",
         "Dec": "Jan"
       }
     }
   ]
 }

/dimension/attribute/delete

Usage: Delete Attribute

JSON Input:

 { 
 	"dimension": "dim_name",
 	"attribute" : "attr_name"
 }

1.3 Subset

/dimension/subset/list

Usage: List Subset
JSON Input:
```
 { "dimension": "dim_name" }
```

/dimension/subset/get

Usage: Get Subset

JSON Input:

 {
   "dimension": "dim_name",
   "subset": "subset_name"
 }

/dimension/subset/set

Usage: Set Subset

JSON Input:

 Static subset
 {
   "dimension": "dim_name",
   "name": "subset_name",
   "mode": "static",  
   "elements": [
     "string"
   ]
 }
 
 Dynamic subset
 {
   "dimension": "dim_name",
   "name": "subset_name",
   "mode": "dynamic",  
   "expression": "string"
 }
 
 Expression Sample: 
 "expression": "Report == 'IS' "        
 "expression": "Type != 'Expense' "
 "expression": "MonthNum <= 6 "
 "expression": "Report == 'IS' and AccountType == 'Revenue' "
 "expression": "Department in ['Sales', 'Marketing'] "
 "expression": "`Chinese Name` == '一月' " 
 "expression": "Level == 0"   
 "Level == 0 and Index <= 6"      //by element index
 
 Note:
 // conditional operators follow Python-style syntax.
 // Use double equals (==) for comparison 
 // use "`" if variable name contains space 
 
 Sample:
 {
   "dimension": "Month",
   "name": "Year&Months",
   "mode": "static",
   "elements": [
 	    "Full_Year", 
 	    “Jan","Feb","Mar","Apr","May","Jun",
 	    "Jul","Aug","Sep","Oct","Nov","Dec"
   ]
 }
 
 Or
 
 {
   "dimension": "Measure",
   "name": "Debit_Account",
   "mode": "dynamic",
   "expression": "Accrued_Category == 'Debit' "
 }

/dimension/subset/resolve

Usage: Resolve Subset

JSON Input:

 {
   "dimension": "dim_name",
   "subset": "subset_name"
 }

/dimension/subset/delete

Usage: Delete Subset

JSON Input:

 {
   "dimension": "dim_name",
   "subset": "subset_name"
 }

1.4 Cube

/cube/list

Usage: List Cube

JSON Input:

 { 
 	"prefix": "string"         // prefix: optional
 }

/cube/create

Usage: Create Cube

JSON Input:

 {
   "name": "cube_name",
   "dimensions": [
     "dim_name_1", "dim_name_2", "dim_name_3",
     "dim_name_4", "dim_name_5", "dim_name_6"
   ]
 }
 
 Sample
 {
   "name": "IncomeSheet",
   "dimensions": [
     "Version",
     "Region",
     "Department",
     "Year",
     "Month",
     "Measure"
   ]
 }

/cube/get

Usage: Get Cube
JSON Input:
```
 { 
 	"names": "cube_name"
 }
```

/cube/delete

Usage: Delete Cube
JSON Input:
```
 { 
 	"names": "cube_name"
 }
```

/cube/save

Usage: Save Cube
JSON Input:
```
 { 
 	"names": "cube_name"
 }
```

/cube/extract_hierarchies

Usage: Extract Dimension Hierarchies by Cube

JSON Input:

 // support "json_records","json_compact","csv"
 // file_folder: relative folder to instance main folder, default “Export”, if absolute folder, it must be under instance main folder; filename like "Dim_<dim_name>_YYYYMMDD_HHMMSS.csv"
 {
   "cubes": "cube_name",  // cube or list of cubes, None for all
   "output_format": "csv",
   "csv_sep": ",",
   "file_folder": "string"
 }

1.5 View

/view/list

Usage: List View
JSON Input:
```
 { 
 	"cube": "cube_name"
 }
```

/view/get

Usage: Get View

JSON Input:

 {
   "cube_name": "cube_name",
   "views": "view_name"
 }

/view/set

Usage: Set View

JSON Input:

 // Similar to View_grid
 // "selection" in row/column/titles share same input of below:
 //   - single element, subset, or list of elements, subsets, and mix of both, subset should be expressed as "dim:subset";
 //   - if multiple elements in titles, then consolidated data will be displayed;
 //   - Blank or null or "All" or dimension omitted, mean to list all elements including the Top level element, suitful to row and column; But for titles, it's easy leading to duplication of summary. Please be careful on it, not suggest for it.

 	
 Sample:
 {
   "name": "test",
   "cube_name": "IncomeSheet",
   "rows": [
     { "dimension": "Region",    
       "selection": ["Shanghai", "Beijing", "Region:WestChina"],
       "hidden": false  
     }  // adhoc与subset混合型
   ],
   "columns": [
     { "dimension": "Month",
       "selection": "All",
       "hidden": false
     }
   ],
   "titles": [
     { "dimension": "Department", 
       "selection": "Department:ASR", 
       "hidden": false  
     },
     { "dimension": "Version",
       "selection": "Actual",
       "hidden": false  
     },
     { "dimension": "Year",
       "selection": "FY25",
       "hidden": false
     },
     { "dimension": "Measure",
       "selection": "Sales",
       "hidden": false  
     }
   ]
 }
 
 or
 
 {
   "cube_name": "IncomeSheet",
   "name": "Sales of FY25 by Region",
   "rows": [
         { "dimension": "Region", 
           "selection": "Region:Region_Level"
         }  
   ],
   "columns": [
         { "dimension": "Month", 
           "selection": "Month:Year&Months"
         } 
   ],
   "titles": [
         {"dimension": "Version", "selection": "Actual"},
         {"dimension": "Year", "selection": "FY25"},
         {"dimension": "Department", "selection": "Department:LoS_Level"},
         {"dimension": "Measure", "selection": "Sales"}
   ]
 }
 
 or
 
 {
   "cube_name": "IncomeSheet",
   "name": "Sales of FY25 by Region and department",
   "rows": [
         { "dimension": "Region" } 
   ],
   "columns": [
         { "dimension": "Department", "selection": "Department:Los_Level" } 
   ],
   "titles": [
         { "dimension": "Version", "selection": "Actual"},
         { "dimension": "Year", "selection": "FY25"},
         { "dimension": "Month", "selection": "Full_Year"},
         { "dimension": "Measure", "selection": "Sales"}
   ]
 }
 
 or
 
 {
   "cube_name": "IncomeSheet",
   "name": "Sales of FY25 by City and BU",
   "rows": [
         { "dimension": "Region", "selection": "Region:City_Level" } 
   ],
   "columns": [
         { "dimension": "Department", "selection": "Department:BU_Level"} 
   ],
   "titles": [
         { "dimension": "Version", "selection": "Actual"},
         { "dimension": "Year", "selection": "FY25"},
         { "dimension": "Month", "selection": "Full_Year"},
         { "dimension": "Measure", "selection": "Sales"}
   ]
 }
 
 or
 
 Multi-index view
 {
   "cube_name": "IncomeSheet",
   "name": "Sales of FY25 by City and Quarter-BU",
   "rows": [ 
 		{ "dimension": "Region", "selection": "Region:City_Level" } 
   ],
   "columns": [  
 		{ "dimension": "Month", "selection": "Month:Year&Quarters"},
 	    { "dimension": "Department", "selection": "Department:BU_Level"} 
   ],
   "titles":  [
         { "dimension": "Version", "selection": "Actual"},
         { "dimension": "Year", "selection": "FY25"},
         { "dimension": "Measure", "selection": "Sales"}
              ]
 }

/view/delete

Usage: Delete View

JSON Input:

 {
   "cube_name": "cube_name",
   "views": "view_name"
 }

1.6 Rule

/cube/rule/get

Usage: Get Rule in JSON Style
JSON Input:
```
 {
   "cube_name": "cube_name",
 }
```

/cube/rule/set

Usage: Set Rule in JSON Style

JSON Input:

 // JSON style: each rule in "" and end with ","
 Syntax:
 {
   "cube_name": "cube_name",
   "rules": [ 
 		rule_string1,
 		rule_string2,
 	    rule_string3
   ]
 }
 
 Sample:
 {
   "cube_name": "IncomeSheet",
   "rules": [
     "['COGS'] = N: ['Sales'] * 0.5",
     "['Income tax'] = N: ['EBT'] * 0.1" 
   ]
 }
 
 or
 
 {
   "cube_name": "IncomeSheet",
   "rules": [
     "['COGS'] = N: ['Sales'] * db('CostAssumption', !Version, !Year, !Measure)",
     "['Admin expenses'] = N: ['Sales'] * db('CostAssumption', !Version, !Year, !Measure)",
     "['R&D expenses'] = N: ['Sales'] * db('CostAssumption', !Version, !Year, !Measure)",
     "['Finance cost'] = N: ['Sales'] * db('CostAssumption', !Version, !Year, !Measure)",
     "['Income tax'] = N: ['EBT'] * db('CostAssumption', !Version, !Year, !Measure)" ,
     "['FY25','Jan','Forecast','Sales'] = N: db('IncomeSheet', 'Actual', !Region, !Department, !Year, !Month, !Measure)",
     "['FY25','Feb','Forecast','Sales'] = N: db('IncomeSheet', 'Forecast', !Region, !Department, !Year, 'Jan', !Measure)",
     "['FY25','Mar','Forecast','Sales'] = N: db('IncomeSheet', 'Forecast', !Region, !Department, !Year, 'Jan', !Measure) * 1.5",
 
     "['FY25','Apr','Forecast','Sales'] = N: db('IncomeSheet', !Version, !Region, !Department, !Year, dimnm('Month', dimix('Month', !Month)-1), !Measure)",
     "['FY25','May','Forecast','Sales'] = N: db('IncomeSheet', !Version, !Region, !Department, !Year, dimnm('Month', dimix('Month', !Month)-1), !Measure) * 1.1",
     "['FY25','Jun','Forecast','Sales'] = N: ( db('IncomeSheet', !Version, !Region, !Department, !Year, dimnm('Month', dimix('Month', !Month)-1), !Measure) + db('IncomeSheet', !Version, !Region, !Department, !Year, dimnm('Month', dimix('Month', !Month)-2), !Measure) + db('IncomeSheet', !Version, !Region, !Department, !Year, dimnm('Month', dimix('Month', !Month)-3), !Measure) )/3 ",
     "['FY25',{'Jul','Aug','Sep'},'Forecast','Sales'] = N: db('IncomeSheet', !Version, !Region, !Department, !Year, ATTRS('Month', !Month,'Prev'), !Measure) * 1.2" 
   ]
 }

/cube/rule/view_raw

Usage: View Rule in TM1 Style
JSON Input:
```
 {
   "name": "cube_name"
 }
```

/cube/rule/edit_raw

Usage: Edit Rule in TM1 Style

Input:

Cube_name: “cube_name”
Rule: string of below, each rule ends by “;”, sames as TM1
all rules should be input here because it will overwrite rule definition of that cube once run

 ['COGS'] = N: ['Sales'] * db('CostAssumption', !Version, !Year, !Measure);
 ['Admin expenses'] = N: ['Sales'] * db('CostAssumption', !Version, !Year, !Measure);
 ['R&D expenses'] = N: ['Sales'] * db('CostAssumption', !Version, !Year, !Measure);
 ['Finance cost'] = N: ['Sales'] * db('CostAssumption', !Version, !Year, !Measure);
 ['Income tax'] = N: ['EBT'] * db('CostAssumption', !Version, !Year, !Measure);
 ['FY25', 'Forecast','Sales'] = N: 
            if( !Month == 'Jan', 
                ['Actual'], 
                db('IncomeSheet', 'Forecast', !Region, !Department, !Year, DIMNM('Month', DIMIX('Month', !Month)-1), !Measure) * 1.1 
             );

1.7 Data

/cell/read

Usage: Read Cell

JSON Input:

 {
 	"cube_name": "IncomeSheet",
 	"coordinates": [ "Actual", "Beijing", "TAX501", 
 				      "FY25", "jan", "COGS"
 	]
 }

/cell/write

Usage: Write Cell

JSON Input:

 {
 	"cube_name": "IncomeSheet",
 	"coordinates": [ "Actual", "Beijing", "TAX501", 
 				      "FY25", "jan", "COGS"
 	],
 	"value": 2010.0
 }

/cube/load

Usage: Write Cells in Batch with Dict input

JSON Input:

 // "overwrite": false (acuumulate mode), true (overwrite mode)
 
 {
   "cube_name": "IncomeSheet",
   "overwrite": false,  // false: Incremental mode
   "data": [
     {
       "Version": "actual",
       "Entity": "shanghai",
       "CostCenter": "ADV310",
       "Year": "FY25",
       "Month": "jan",
       "Account": "COGS",
       "Value": 1848.0  
     },
     {
       "Version": "actual",
       "Entity": "shanghai",
       "CostCenter": "ASR110",
       "Year": "FY25",
       "Month": "jan",
       "Account": "COGS",
       "Value": 1234.5
     },
     {
       "Version": "budget",
       "Entity": "shanghai",
       "CostCenter": "TAX501",
       "Year": "FY25",
       "Month": "feb",
       "Account": "COGS",
       "Value": 2000.0
     }
   ]
 }

/cube/batch_write

Usage: Write Cells in Batch (Same as /cube/load) with coordinates input

JSON Input:

 {
   "cube_name": "IncomeSheet",
   "overwrite": false,      //  false (acuumulate mode)
   "batch_data": [
     {
       "coordinates": ["actual", "shanghai", "ADV310", "FY25", "jan", "COGS"],
       "value": 1848.0
     },
     {
       "coordinates": ["actual", "shanghai", "ASR110", "FY25", "jan", "COGS"],
       "value": 1234.5
     },
     {
       "coordinates": ["budget", "shanghai", "TAX501", "FY25", "feb", "COGS"],
       "value": 2000.0
     }
   ]
 }

/cube/load_csv

Usage: load data from CSV

JSON Input:

 Syntax
 //"file_path": relative path to instance folder, use "/" or "\\" on windows;
 // "has_header": if false, use column number, 1/2/3, instead;
 //"overwrite": false (acuumulate mode), true (overwrite mode);
 //"mode": "wide" means flat table, "long" means long table;
 // "transformations": use Python expression
 	// example：take "Jun" from "7-Jun"
 	// "Order Date": "x.split('-')[1]", 
 
 	// example：trim for above
 	// "Order Date": "x.split('-')[1].strip()",
 
 	// example：convert amount "$1,000.00" to number
 	// "Amount": "float(x.replace('$', '').replace(',', ''))",
   
 	// example: extract first 3 chars 
 	// "Product Code": "x[:3]"
       
 {
   "cube_name": "cube_name", 
   "source": {
     "file_path": "CSV file", 
     "has_header": true, 
     "encoding": "utf-8",
     "overwrite": true
   },
   "mapping": {
     "mode": "wide",
 
     // Ignore columns list
     "ignore_columns": ["Sales Staff"],
 
     // 2. Constant values
     "constants": {
       "Year_Column": "FY26",        // temporary variable
       "Scenario_Column": "Budget"   // temporary variable
     },
 
     // 3. Transformed values
     "transformations": {
       // Case 1: simple transform with one variable
       // "Order Month" is temporary variable
       "Order Month": {
         "expression": "x.split('-')[1]",
         "x": "Order Date"   // x maps CSV "Order Date" column in expression
       },
 
       // Case 2: Multiple variables involved
       // generate a temporary variable "Total Amount"
       "Total Amount": {
         "expression": "float(p) * int(q)", 
         "p": "Unit Price",
         "q": "Quantity"
       },
 
       // Case 3: String combination
       // generate a temporary variable "Full Description"
       "Full Description": {
         "expression": "f'{a} - {b}'",
         "a": "Product Code",
         "b": "Product Name"
       }
     },
 
     // 4. dimension mapping: "dimension name":"CSV column name"
     "fixed_dimensions": {
       "Subsidiary": "Subsidiaries",     // CSV column
       "Product": "Product Type",        // CSV column
       "Version": "Scenario_Column",     // Temp variable from constant
       "Year": "Year_Column",            // Temp variable from constant	      
       "Month": "Order_Month_Column"     // Temp variable from transformations
     },
     
     // 2. Measure dimension
     "measure_dimension": "Price_and_Cost_Measures",
     
     // 3. Measure dimension mapping:  "Measure dimension element name" : "CSV column name"
     "measure_mapping": {
       "Unit Cost": "Unit cost",
       "Unit Sale Price": "Unit sale price"
     }
   }
 }
 
 
 Sample:	
 {
   "cube_name": "Price_and_Cost", 
   "source": {
     "file_path": "Source/Cube_PriceandCost.csv", 
     "has_header": true, 
     "encoding": "utf-8",
     "overwrite": true 
   },
   "mapping": {
     "mode": "wide",
 
     "constants": {
       "Year_column": "FY26"    
     },
     
     "fixed_dimensions": {
       "Product": "Product Type",
       "Version": "Version",
       "Channel": "Channel",
       "Year": "Year_column",
       "Month": "Month"
     },
 
     "measure_dimension": "Price_and_Cost_Measures",
     
     "measure_mapping": {
       "Unit Cost": "Unit cost",
       "Unit Sale Price": "Unit sale price"
     }
   }
 }
 
 or
 
 {
   "cube_name": "Sales_Plan", 
   "source": {
     "file_path": "Source/Cube_Salesplan.csv",
     "has_header": true,
     "encoding": "ansi",
     "overwrite": false
   },
   "mapping": {
     "mode": "wide",
 
     "ignore_columns": ["Sales Staff"],
 
     "constants": {
       "Order_Year": "FY26"
     },
      
     "transformations": {
       "Order_Month": {
         "expression": "x.split('-')[1]",
         "x": "Order Date"  
       }
     },
     
     "fixed_dimensions": {
       "Subsidiary": "Subsidiaries",
       "Channel": "Channel",
       "Product": "Product Type",
       "Version": "Version",
       "Year": "Order_Year",
       "Month": "Order_Month"
     },
     
     "measure_dimension": "Sales_Plan_Measures",
  
     "measure_mapping": {
       "Quantity": "Quantity",
       "Cash Discount": "Cash Discount",
       "Volume Discount": "Volume Discount",
       "Freight": "Freight",
       "Returns and Allowances": "Returns and Allowances"
     }
   }
 }

/cube/extract

Usage: Extract Cube Data to File

JSON Input:

 Syntax：
 // "c_level_included": false (default), if ture, export consolidation level data;
 // "rule_calc_included": false (default), if ture, export rule calculated data;
 // "output_format": "json_records","json_compact","csv"
 // "csv_sep": default ",", can define your own;
 // "file_folder": 1) Target output folder. Defaults to 'Export' if empty; 2) Relative paths are resolved from the instance directory. 3) Absolute paths must also point under the instance directory, otherwise an error occurs. 4) File names are auto-generated in the format: Cube_<cube_name>_YYYYMMDD_HHMMSS.xxx
 
 // "filters": similar to "titles" of get_view_grid;
 //     - single element, subset, or list of elements, subsets, and mix of both, subset should be expressed as "dim:subset";
 //     - Blank or null or "All" or dimension omitted, mean to list all elements including the Top level element, suitful to row and column; But for titles, it's easy leading to duplication of summary. Please be careful on it, not suggest for it.
 //     - if C=false, child elements will be used for a listed C-level element. For example, if "Q1" is used for "Month" dimension, then system will export ["Jan", "Feb", Mar"] as an adjustment;
    
 {
   "cube_name": "cube_name",
   "filters": {                                                 
     "Year": ["2025"],                                          
     "Version": ["Actual"],                                     
     "Month": ["Full_Year", "Jan", "Feb", "Month:Year&Month" ]  
   },
 
   "c_level_included": false, 
   "rule_calc_included": false,
 
   "output_format": "csv", 
   "csv_sep": ",", 
   "file_folder": "Export" 	
 }
 
 Sample:
 // Mode 1: (C=False, Rule=False)
 // export data of whole cube
 // no calculation, just retrieve data from Parquet file, qiuck
 {
   "cube_name": "IncomeSheet",
   "output_format": "csv"
 }
 
 // Mode 1 (C=False, Rule=False)
 // export data of adhoc view	
 {
   "cube_name": "IncomeSheet",
   "filters": {
     "Year": ["2025"], 
     "Version": ["Actual"], 
     "Month": ["Full_Year", "Jan", "Feb", "Month:Year&Month" ] 
   },
   "c_level_included": false, 
   "rule_calc_included": false, 
 
   "output_format": "csv",
   "csv_sep": ",",   
   "file_path": "Export" 
 }
 
 // Mode 2 (C=True, Rule=True)
 // export data of Saved View
 {
   "cube_name": "IncomeSheet",
   "view_name": "Budget_2025_View",
   "c_level_included": true,
   "rule_calc_included": true,
   "output_format": "json_records"
 }

 // Mode 3 (C=True, Rule=False) 
 // export data of adhoc view	
 {
   "cube_name": "IncomeSheet",
   "filters": {
     "Region": ["Total China", "Total USA"],
     "Year": ["2025"]
   },
   "c_level_included": true,
   "rule_calc_included": false,
   "output_format": "csv",
   "csv_sep": "|"
 }
 
 // Mode 4 (C=False, Rule=True)
 // export data of adhoc view
 {
   "cube_name": "IncomeSheet",
   "filters": {
     "Year": ["2025"],
     "Month": ["Jan", "Month:Q1_Months", "Dec"], 
     "Version": ["Budget", "Version:Scenario_A"]
   },
   "c_level_included": false,
   "rule_calc_included": true,
   "output_format": "json_compact"
 }

 Export format：
 "json_records" ：
 [
   {"Year": "2025", "Region": "China", "Value": 100.0},
   {"Year": "2025", "Region": "USA",   "Value": 200.0},
   {"Year": "2026", "Region": "China", "Value": 150.0}
 ]
 
 "json_compact" ：
 {
   "columns": ["Year", "Region", "Value"],
   "data": [
     ["2025", "China", 100.0],
     ["2025", "USA",   200.0],
     ["2026", "China", 150.0]
   ]
 }

/cube/clear

Usage: Clear Cube Data, ZeroOut

JSON Input:

 ZerouOut whole cube 
 	{
 	  "cube_name": "IncomeSheet",
 	}
 
 ZerouOut Saved View
 	{
 	  "cube_name": "IncomeSheet",
 	  "view_name": "Budget_2025_View"
 	}
 
 ZerouOut Adhoc View
 	{
 	  "cube_name": "IncomeSheet",
 	  "filters": {
 	    "Year": ["2025"],
 	    "Version": ["Budget", "Forecast"],
 	    "Region": ["Total China"]
 	  }
 	}

/view/grid

Usage: Get View Grid

JSON Input:

 // same as /view/set
 // "selection" in row/column/titles share same input of below:
 //   - single element, subset, or list of elements, subsets, and mix of both, subset should be expressed as "dim:subset";
 //   - if multiple elements in titles, then consolidated data will be displayed;
 //   - Blank or null or "All" or dimension omitted, mean to list all elements including the Top level element, suitful to row and column; But for titles, it's easy leading to duplication of summary. Please be careful on it, not suggest for it.
 // "suppress_zeros": if ture, no display for rows/columsn with all values in 0;
 
 Sample:
 // Adhoc-view
 {
   "cube_name": "IncomeSheet",
   "rows": [
     { "dimension": "Region",     "selection": "Beijing" }
   ],
   "columns": [  
     { "dimension": "Month",      "selection": ["Jan","Feb","Mar","Q1"] }
   ],
   "titles": [
     { "dimension": "Version",    "selection": "Actual"  },
     { "dimension": "Year",       "selection": "FY25"    },
     { "dimension": "Department", "selection": "ASR110"  },
     { "dimension": "Measure",    "selection": "Sales"   }
   ],
   "suppress_zeros": false
 }

 or
 
 {
   "cube_name": "IncomeSheet",
   "rows": [
     { "dimension": "Region",     "selection": null      }
   ],
   "columns": [  
     { "dimension": "Month",      "selection": null      }
   ],
   "titles": [
     { "dimension": "Version",    "selection": "Actual"  },
     { "dimension": "Year",       "selection": "FY25"    },
     { "dimension": "Department", "selection": "ASR110"  },
     { "dimension": "Measure",    "selection": "Sales"   }
   ],
   "suppress_zeros": false
 }
 
 or 
 
 // multi-index
 {
   "cube_name": "Sales_plan",
   "rows": [
     { "dimension": "Version",        "selection": "Budget Version 1"  },
     { "dimension": "Product",        "selection": null }
   ],
   "columns": [  
     { "dimension": "Year",           "selection": "FY26"    },
     { "dimension": "Month",          "selection": "All" }
   ],
   "titles": [
     { "dimension": "Sunsidiary",    "selection": "GO Americas"  },
     { "dimension": "Channel",       "selection": "Golf Shop"  },
     { "dimension": "Sales_plan_Measures",    "selection": "Returns and Allowances"   }
   ],
   "suppress_zeros": true
 }
 
 or 

 // Saved View
 {
   "cube_name": "IncomeSheet",
   "view_name": "Sales of FY25 by Region and department",
   "suppress_zero": "True"
 }

1.8 System

/health check

Usage: Health Check
JSON Input:

/system/reload

Usage: Reload Data from Disk
JSON Input:

/system/save

Usage: Save Data to Disk
JSON Input:

2. Excel Functions

2.1 Dimension

M1_DIM_SET

M1_DIM_LIST(prefix)      Prefix: optional

M1_DIM_GET

M1_DIM_GET(dim_name)

M1_DIM_SET

M1_DIM_SET(run_trigger,dim_name,element_range,index_range,weight_range,parent_pos)

M1_DIM_REINDEX

M1_DIM_REINDEX(run_trigger,dim_name,mode,sort_order,order_range)

	mode: "alpha" | "numeric" | "manual"
	sort_order: "asc" | "desc", default "asc"
	order_range: sorted element list, only for mode manual

2.2 Attribute

M1_ATTR_LIST

M1_ATTR_LIST(dim_name)

M1_ATTR_GET

M1_ATTR_GET(dim_name,attr_name)

M1_ATTR_SET

M1_ATTR_SET(run_trigger,dim_name,element_range,header_range,data_range,type_range)

M1_ATTR_DELETE

M1_ATTR_DELETE(run_trigger,dim_name,attr_name_range)

2.3 Subset

M1_SUBSET_LIST

M1_SUBSET_LIST(dim_name)

M1_SUBSET_GET

M1_SUBSET_GET(dim_name,subset)

M1_SUBSET_SET

M1_SUBSET_SET(run_trigger,dim_name,subset_input,source_data,mode)

	Mode: "static" (default) or "dynamic"

2.4 Cube

M1_CUBE_LIST

M1_CUBE_LIST(prefix)              prefix: optional

M1_CUBE_GET

M1_CUBE_GET(cube_names)

M1_CUBE_CREATE

M1_CUBE_CREATE(run_trigger,cube_name,dim_range)

2.5 View

M1_VIEW_LIST

M1_VIEW_LIST(cube_name)

M1_VIEW_GET

M1_VIEW_GET(cube_name,view_name_range)

M1_VIEW_SET

M1_VIEW_SET(trigger,cube_name,view_name,row_range,col_range,title_range)

2.6 Rule

M1_RULE_GET

M1_RULE_GET(cube_name)

M1_RULE_SET

M1_RULE_SET(run_trigger,cube_name,rule_range)

2.7 Data

M1_READ/M1_GET/DBR

M1_READ(cube_name,coords,...)
M1_GET(cube_name,coords,...)
DBR(cube_name,coords,...)

M1_WRITE/DBRW

M1_WRITE(cube_name,coords,...,value)
DBRW(cube_name,coords,...,value)

M1_VIEW_READ/M1_VIEW

M1_VIEW(cube_name,row_dim,col_dim,filters,suppress_zeros)
M1_VIEW(cube_name,view, ,filters,suppress_zeros)

M1_VIEW_WRITE

M1_VIEW_WRITE(trigger,cube_name,row_dim_name,row_elements_range,col_dim_name,col_elements_range,value_area,filters_range)

M1_RANGE_WRITE

M1_RANGE_WRITE(trigger,cube_name,header_range,data_range)

M1_ZEROOUT

M1_ZEROOUT(run_trigger,cube_name,view_name,filter_range)

2.8 System

M1_SAVE

M1_SAVE(trigger,cube_name)

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

1. API Interface

1.1 Dimension

1.2 Attribute

1.3 Subset

1.4 Cube

1.5 View

1.6 Rule

1.7 Data

1.8 System

2. Excel Functions

2.1 Dimension

2.2 Attribute

2.3 Subset

2.4 Cube

2.5 View

2.6 Rule

2.7 Data

2.8 System

FilesExpand file tree

CubeOne_Function_Reference.md

Latest commit

History

CubeOne_Function_Reference.md

File metadata and controls

1. API Interface

1.1 Dimension

1.2 Attribute

1.3 Subset

1.4 Cube

1.5 View

1.6 Rule

1.7 Data

1.8 System

2. Excel Functions

2.1 Dimension

2.2 Attribute

2.3 Subset

2.4 Cube

2.5 View

2.6 Rule

2.7 Data

2.8 System