Subscribe to feed

 

Transformation: Creating a New Data Table

This custom transformation creates a new data table where the data is transformed and all settings are explicitly set.

AnalysisApplication app = this.Context.GetService<AnalysisApplication>();

// create the data source.
DataSource ds = app.Document.Data.CreateFileDataSource("c:\\test.txt");

// connect
DataSourceConnection connection = ds.Connect(app.Document.Data, 
                                    DataSourcePromptMode.RequiredOnly);

// create the reader
DataRowReader reader = connection.ExecuteQuery2();

DataFlowBuilder dfb = new DataFlowBuilder(
   connection.DataSource, // use the data source from the reader.
   app.ImportContext);

// configure pivot using known columns from the reader
PivotTransformation pt = new PivotTransformation();
pt.CategoryColumns = 
   new DataColumnSignature[] { 
       new DataColumnSignature(reader.Columns["Category1"]),
       new DataColumnSignature(reader.Columns["Category2"])
   };

pt.IdentityColumns = 
    new DataColumnSignature[] { 
        new DataColumnSignature(reader.Columns["Identity"]),
    };

pt.ValueColumns = 
    new ColumnAggregation[] {
        new ColumnAggregation(
            new DataColumnSignature(reader.Columns["Values"]), 
            "Sum")
    };

// add the transformation.
dfb.AddTransformation(
    pt);

// add more transformations here if needed.

// create the flow
DataFlow flow = dfb.Build();

// create a new table from the flow.
DataTable newTable = app.Document.Data.Tables.Add(flow.DocumentTitle, flow);