ai.dataSource

function previewData(context) {
 var dataSource = context.getDataSource();
 var allTablesInThisDataSource = dataSource.getTables();
 //Navigate to Designer Settings tab, drag and drop a Static Integer parameter from the Settings Component
 //Setting Name: Custom Integer and Value: 100
 var getSetting=dataSource.getSetting("Custom Integer" );
 ai.log.logInfo(getSetting.getValue()); //100
}
 

//Consider a sample data source
dataSample = {
     rows: [
        {"Value": 10, "Measure": "", "Test": "No Space", "Effective Date": "2018-11-01"},
        {"Value": 20, "Measure": " ", "Test": "One Space", "Effective Date": "2018-11-01"},
        {"Value": 30, "Measure": "  ", "Test": "Two Spaces", "Effective Date": "2018-11-01"},
        {"Value": 40, "Measure": "   ", "Test": "Three Spaces", "Effective Date": "2018-11-01"},
        {"Value": 50, "Measure": "1 ", "Test": "Aus" + '\\-' + "tin" + '\\-', "Effective Date": "2018-11-01"},
        {"Value": 10, "Measure": "2  ", "Test": "Austin", "Effective Date": "2018-12-01"},
        {"Value": 20, "Measure": "3   ", "Test": "Austin", "Effective Date": "2018-12-01"},
        {"Value": 30, "Measure": "4    ", "Test": "Austin", "Effective Date": "2018-12-01"},
        {"Value": 40, "Measure": "5     ", "Test": "Austin", "Effective Date": "2018-12-01"}
    ]
};
ExternalMetadataDefinition = {
    Tables : [
        {
            name : "dataSample",
            displayName : "Sample",
            id:"dataSample",
            parameterId:"sampleParameter",
            columns : [
                {name : "Value", displayName : "Value", type: "float", includeByDefault : true, },
                {name : "Measure", displayName : "Measure", type: "text", includeByDefault : true},
                {name : "Test", displayName : "Test", type: "text", includeByDefault : true},
                {name : "Effective Date", displayName : "Date", type : "datetime", includeByDefault : true}
            ]
        }
    ]
};

//Create a table structure
function importStructure(context) {
    var addColumn = function(table, column, order) {
        var recordColumn = table.addColumn(column.name);
        recordColumn.setDisplayName(column.displayName);
        switch (column.type) {
            case "text":
                recordColumn.setTextColumnType(column.length);
                break;
            case "int":
                recordColumn.setIntegerColumnType();
                break;
            case "float":
                recordColumn.setFloatColumnType();
                break;
            case "boolean":
                recordColumn.setBooleanColumnType();
                break;
            case "datetime":
                recordColumn.setDateTimeColumnType();
                break;
            default:
                recordColumn.setTextColumnType(200);
                break;
        }
        recordColumn.setDisplayOrder(order);
        recordColumn.setIncludeByDefault(column.includeByDefault || false);
        recordColumn.setIncludeInKey(column.includeInKey || false);
        recordColumn.setMandatoryForImports(column.mandatoryForImport || false);
        recordColumn.setRemoteColumnType(column.remoteColumnType || "String");
    };
    var builder = context.getStructureBuilder();
    var dataSource=context.getDataSource();
    for (var i = 0; i < ExternalMetadataDefinition.Tables.length; i++) {
        var externalTable = ExternalMetadataDefinition.Tables[i];
        var table = builder.addTable(externalTable.name);
        table.setDisplayName(externalTable.displayName);
        for (var j = 0; j < externalTable.columns.length; j++) {
            addColumn(table, externalTable.columns[j], j + 1);
        }
    }
}

//Examples for ai.datasource.table
function importData(context){
    var dataSource = context.getDataSource();
    var alltablesinds=dataSource.getTables();
    var rowset=context.getRowset();
    //Example: getTableId;
    var tableId = rowset.getTableId(); ////dataSample
    var table= dataSource.getTable( tableId );
    //Example: getDisplayName 
    var dname=table.getDisplayName(); //Sample
    ai.log.logInfo("Table Display Name",dname);
    //Example: getLastImported
    var lastImported=table.getLastImported();
    ai.log.logInfo("Last Imported:",lastImported); //Last Imported 10/23/2020 01:12:59
    //Example: getId
    ai.log.logInfo("Table id",table.getId()) //dataSample
    
}

//Consider a sample JSON data set
dataSample = {
     rows: [
        {"Value": 10, "Measure": "", "Test": "No Space", "Effective Date": "2018-11-01"},
        {"Value": 20, "Measure": " ", "Test": "One Space", "Effective Date": "2018-11-01"},
        {"Value": 30, "Measure": "  ", "Test": "Two Spaces", "Effective Date": "2018-11-01"},
        {"Value": 40, "Measure": "   ", "Test": "Three Spaces", "Effective Date": "2018-11-01"},
        {"Value": 50, "Measure": "1 ", "Test": "Aus" + '\\-' + "tin" + '\\-', "Effective Date": "2018-11-01"},
        {"Value": 10, "Measure": "2  ", "Test": "Austin", "Effective Date": "2018-12-01"},
        {"Value": 20, "Measure": "3   ", "Test": "Austin", "Effective Date": "2018-12-01"},
        {"Value": 30, "Measure": "4    ", "Test": "Austin", "Effective Date": "2018-12-01"},
        {"Value": 40, "Measure": "5     ", "Test": "Austin", "Effective Date": "2018-12-01"}
    ]
};
ExternalMetadataDefinition = {
    Tables : [
        {
            name : "dataSample",
            displayName : "Sample",
            id:"dataSample",
            parameterId:"2018-01-01",
            columns : [
                {name : "Value", displayName : "Value", type: "float", includeByDefault : true, },
                {name : "Measure", displayName : "Measure", type: "text", includeByDefault : true},
                {name : "Test", displayName : "Test", type: "text", includeByDefault : true},
                {name : "Effective Date", displayName : "Date", type : "datetime", includeByDefault : true}
            ]
        }
    ]
};

function testConnection(context) {
    return true;
}

//Create a table structure
function importStructure(context) {
    var addColumn = function(table, column, order) {
        var recordColumn = table.addColumn(column.name);
        recordColumn.setDisplayName(column.displayName);
        switch (column.type) {
            case "text":
                recordColumn.setTextColumnType(column.length);
                break;
            case "int":
                recordColumn.setIntegerColumnType();
                break;
            case "float":
                recordColumn.setFloatColumnType();
                break;
            case "boolean":
                recordColumn.setBooleanColumnType();
                break;
            case "datetime":
                recordColumn.setDateTimeColumnType();
                break;
            default:
                recordColumn.setTextColumnType(200);
                break;
        }
        recordColumn.setDisplayOrder(order);
        recordColumn.setIncludeByDefault(column.includeByDefault || false);
        recordColumn.setIncludeInKey(column.includeInKey || false);
        recordColumn.setMandatoryForImports(column.mandatoryForImport || false);
        recordColumn.setRemoteColumnType(column.remoteColumnType || "String");
    };
    var builder = context.getStructureBuilder();
    var dataSource=context.getDataSource();
    for (var i = 0; i < ExternalMetadataDefinition.Tables.length; i++) {
        var externalTable = ExternalMetadataDefinition.Tables[i];
        var table = builder.addTable(externalTable.name);
        table.setDisplayName(externalTable.displayName);
        for (var j = 0; j < externalTable.columns.length; j++) {
            addColumn(table, externalTable.columns[j], j + 1);
        }
    }
}

//Examples for ai.datasource.table.column
function importData(context) {
    var columnArray = rowset.getColumns();
    var colIds=[];
    var colDisplayNames=[];
    var colTypes=[];
    var colDisplayOrder=[];
    var colIncludeByDefault=[];
    var colLength=[];
    for (var k = 0; k < columnArray.length; k++) {
        //Example:getColumnType
        colTypes.push(columnArray[k].getDisplayName().toString()+":"+columnArray[k].getColumnType().toString());
        //Example:getDisplayName
        colDisplayNames.push(columnArray[k].getDisplayName().toString());
        //Example:getDisplayOrder
        colDisplayOrder.push(columnArray[k].getDisplayName().toString()+":"+columnArray[k].getDisplayOrder().toString());
        //Example:getId
        colIds.push(columnArray[k].getId().toString());
        //Example:getIncludeByDefault
        colIncludeByDefault.push(columnArray[k].getDisplayName().toString()+":"+columnArray[k].getIncludeByDefault().toString());
        //Example:getLength
        if(columnArray[k].getColumnType()==="TextColumn")
        {
            colLength.push(columnArray[k].getDisplayName().toString()+":"+columnArray[k].getLength());
        }
    }

    ai.log.logInfo("Column Ids are "+colIds);
    //Column Ids are Effective Date,Value,Measure,Test
    ai.log.logInfo("Column Display names are "+colDisplayNames);
    //Column Display names are Date,Value,Measure,Test
    ai.log.logInfo("Column Types are "+colTypes);
    //Column Types are Date: DateTimeColumn, Value:FloatColumn, Measure:TextColumn,Test:TextColumn
    ai.log.logInfo("Column Display Order: "+colDisplayOrder); 
    //Column Display Order: Date:4,Value:1,Measure:2,Test:3
    ai.log.logInfo("Column Included by Default:"+colIncludeByDefault);
    //Column Included by Default:Date:true,Value:true,Measure:true,Test:true
    ai.log.logInfo("Length - "+colLength);
    //Length - Measure:200,Test:200
}

//Navigate to Designer Settings tab and drag an Integer Static parameter from the Settings Component pane
//Name the setting Custom Integer and set the value to 100
 var dataSource= context.getDataSource();
 var setting=dataSource.getSetting("Custom Integer" );
 
 //Example: getDisplayName
 var name = setting.getDisplayName(); //Custom Integer

 //Example: getValue
 var value = setting.getValue(); //100

// DataType checkup for the setting.
if(setting.isInteger()){
    ai.log.logInfo("Setting is of the type Integer"); //Setting is of the type Integer
}
//Check for other data types using setting.isPeriodRange(), setting.isInteger(), setting.isBoolean(), setting.isText(), setting.isPassword(), setting.isDimension();
 

function importData(context)
{
    var dataSource = context.getDataSource();
    //getFromPeriod
    var getFromPeriod=dataSource.getSetting("Period Range").getValue().getFromPeriod();
    //getToPeriod
    var getToPeriod=dataSource.getSetting("Period Range").getValue().getToPeriod();
    //getFromPeriodStartDateTime
    var getFromPeriodStartDateTime = dataSource.getSetting("Period Range").getValue().getFromPeriodStartDateTime().substring(0, 10);
    ai.log.logInfo("getFromPeriodStartDateTime"+getFromPeriodStartDateTime); //2017-12-01
    //getFromPeriodEndDateTime
    var getFromPeriodEndDateTime = dataSource.getSetting("Period Range").getValue().getFromPeriodEndDateTime().substring(0, 10);
    ai.log.logInfo("getFromPeriodEndDateTime"+getFromPeriodEndDateTime); //2017-12-31
    //getToPeriodStartDateTime
    var getToPeriodStartDateTime = dataSource.getSetting("Period Range").getValue().getToPeriodEndDateTime().substring(0, 10);
    ai.log.logInfo("getToPeriodStartDateTime"+getToPeriodStartDateTime); //2018-01-01
    //getToPeriodEndDateTime
    var getToPeriodEndDateTime = dataSource.getSetting("Period Range").getValue().getToPeriodEndDateTime().substring(0, 10);
    ai.log.logInfo("getToPeriodEndDateTime"+getToPeriodEndDateTime); //2018-01-30
}
 

//Navigate to Designer Settings tab and drag a Static Dimension parameter from the Settings Component pane
//Name the setting "Custom Dimension" and set a value from the Static Dimension Setting dropdown
 var setting=dataSource.getSetting("Custom Dimension" );
 var value = setting.getValue();
 
 //Example: getDisplayName
 var name = value.getDimensionName();
 var settingValue=value.getValueName();

 ai.log.logInfo("Dimension Settings Name: "+name+" and Dimension Settings Value: "+settingValue);
//Dimension Settings Name: Level and Dimension Settings Value: Company B (40% owned)Example for Dimension Settings
//Navigate to Designer Settings tab and drag a Static Dimension parameter from the Settings Component pane
//Name the setting "Custom Dimension" and set a value from the Static Dimension Setting dropdown
var setting=dataSource.getSetting("Custom Dimension" );
var value = setting.getValue();

//Example: getDisplayName
var name = value.getDimensionName();
var settingValue=value.getValueName();

ai.log.logInfo("Dimension Settings Name: "+name+" and Dimension Settings Value: "+settingValue);
//Dimension Settings Name: Level and Dimension Settings Value: Company B (40% owned)