November 21, 2009 at 11:31 am
Hello All,
I am using Microsoft's Business Intelligence Studio, and I am trying to get data from a table (which is a matrix) and multiply this matrix.
Using an ODE DB Source, I run a query on my data to produce the following table:
Average_Transition MatrixRow MatrixColum MatrixSize
1 1 1 3
2 1 2 3
3 1 3 3
2 2 1 3
2 2 2 3
4 2 3 3
3 3 1 3
1 3 2 3
5 3 3 3
which I connect to a script component. The script component has the input rows as above, and the one output column titled "FinalMatrix". What I am trying to do is store the above information into an array, then have the script return the square of the matrix.
The above data would produce the following matrix:
1 2 3
2 2 4
3 1 5
which when squared becomes:
14 9 26
18 12 34
20 13 38
But when I run the script I end up with:
1 0 0
0 4 0
0 0 25
The script component (in c#) is as follows
using System;
using System.Data;
using System.Collections;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
///////////////////////////////// Define Variables ///////////////////////////////////////////////
// Insert data into matrix
int rowNum = Row.MatrixRow - 1;
int colNum = Row.MatrixColumn - 1;
int FindSize = Row.MatrixSize;
float AverageTransition = Row.AverageTransition;
int[,] FindSize2 = new int [FindSize,FindSize];
FindSize2[rowNum, colNum] = Row.MatrixSize;
FindSize2.SetValue(Row.MatrixSize, rowNum, colNum);
int FindSize3 = FindSize2[rowNum, colNum];
float[,] c = new float[FindSize, FindSize];
float[,] arrMyArray = new float[FindSize, FindSize];
arrMyArray[rowNum, colNum] = AverageTransition;
arrMyArray.SetValue(AverageTransition, rowNum, colNum);
System.Windows.Forms.MessageBox.Show("Row Num = " + rowNum + " " + ", Col Num = " + " " + colNum + ", Value = " + " " + arrMyArray[rowNum, colNum].ToString());
//The above line shows that the matrix is populated properly
/////////////////////////////////////////////////////////////////////////////////////////////////
if (arrMyArray.GetLength(1) == arrMyArray.GetLength(0)) //Standard c# matrix multiplication code
{
c = new float[FindSize3, FindSize3];
for (int i = 0; i < FindSize3; i++)
{
for (int j = 0; j < FindSize3; j++)
{
c[i, j] = 0;
for (int k = 0; k < FindSize3; k++)
c[i, j] += arrMyArray[i, k] * arrMyArray[k, j];
}
}
}
else
{
System.Windows.Forms.MessageBox.Show("Not a Square Matrix");
}
////////////////////////////////////Output Rows /////////////////////////////////////////////////
OutputBuffer.AddRow();
OutputBuffer.FinalMatrix = c[rowNum, colNum];
}
}
I can't figure what is going on here. If I hardcode the array into the script then the multiplication part works fine, and
the squared matrix is calculated correctly. Why doesn't my code work? Im guessing it has something to do with the multiple input rows. I can't hardcode this script as I want to be able to work with multiple matrix sizes. Does anyone know how to help with this?
Thanks in advance.
November 30, 2009 at 9:31 am
I'm not sure why, other than i would have thought that the pipeline (inputrowbuffer) is handling the rows, ones at a time, and your logic to capture/hold the entire matrix is flawed.
If my mxmath is correct, you're seeing the square of a matrix that contains only the 1, 2 and 5 (ie cells 1, 5 and 9).
Not saying it's optimally performant, but I would look at using a dataflow task and then a script task, where:
- in the data flow read the first row, and creates the array/matrix (store in variable, scoped at the control flow level, so avail to everything in the control flow) of appropriate size (ie in your example, 3x3)
- it also reads (row by row) into the matrix, using the coordinates in youyr source to slot the value to the correct location in the md array/matrix
- add a script task (in the control flow, not data flow) that executes after the dataflow completes (ie the matrix is fully loaded) and perform your multiplication (squaring in this case) in this script.
- if you want to output the result, save it to another variable and then use the variable as a datasource and write it out in another data flow step.
hth,
Steve.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply