Monday, March 26, 2012
jtds blob size
Monday, March 12, 2012
Joining DataSets with Hashtables
Dimension data (Owner of Resources) in DataSource B, where A and B are
different Oracle servers. Linked Servers are not an option for at
least 3 months, if ever. These sources return DataSets A and B.
I need to develop a report where I total the Failures from A "Grouped
By" the Owner of a Resource from B. Very simple lookup.
If I use the Report custom Code, I can push the Resource/Owner mapping
into a (Public global variable) Hashtable, and pull it out in the
Details of a table. Data is pushed using a Table using DataSet A with
Detail rows that call a SetData(ID,Owner) function with a non-Visible
Details row. Data is retrieved in a separate table based on DataSet B
using a GetData(ID) function.
But:
* GetData() only appears to function in Expressions in the Details of
a table, even though GetData() is referencing the Hashtable.
* I cannot sort a Table on the GetData() results, data comes back in
random order
* I cannot Group by the GetData() results, the field appears to be set
to the Owner of the first ID (not for each row in the DataSet!!)
* I can add a Calculated Field to DataSet A that calls SetData(), and
it appears to work (I have SetData return the Owner, so I can see the
function is called), but if I add a Calculated Field to DataSet B that
calls GetData(), all the field contents are Nothing.
I am running out of ideas here. Why is an action so common made so
difficult in SSRS?
-- Scott-- Report Properties -> Code Pane
--
Public Shared mapping As New System.Collections.Hashtable()
Public Function StoreValue( ByVal dataSetName As String, ByVal
fieldName as String, _
ByVal idName As String, ByVal fieldValue as Object ) As Object
Dim hashName As String
hashName = dataSetName & "." & fieldName & "." & idName
If Not mapping.ContainsKey( hashName ) Then
mapping.Add( hashName, fieldValue )
End If
Return fieldValue
End Function
Public Function GetValue( ByVal dataSetName As String, ByVal fieldName
as String, _
ByVal idName As String ) As Object
Dim hashName As String
hashName = dataSetName & "." & fieldName & "." & idName
If mapping.ContainsKey( hashName ) Then
Return mapping( hashName )
Else
Return Nothing
End If
End Function
-- Report Body, Table Object --
-- Table 1, Details Section --
DataSet dsNames
=Code.StoreValue( "dsNames", "ORGNAME", Fields!RESOURCEID.Value,
Fields!ORGNAME.Value )
-- Table 2, Details Section --
DataSet dsResources
=Code.GetValue( "dsNames", "ORGNAME", Fields!RESOURCEID.Value )
Friday, March 9, 2012
Join Using a substring function
Hi All,
I am trying to achieve loading a fact table using my stage and dimesion data , I was planning to use a Lookup transformation to do this, however I am supposed to use a substring task to because the data in the stage table is in adifferent format from that in the dimensions, how do I incorporate this substring task within the data flow, any help is appreciated.
Thanks
I would do the join in Transact-SQL first, if possible.
|||
I already have a SQL script (SP) that works fine, however what I am trying to achieve is get rid of this SP/view concept and replace with SSIS packages/tasks/transformations. Thanks for helping.
|||You can use a Derived Column transform to do a substring. I would recommend that you use a Derived Column transform to get the stage data to match with your dimension table before doing the Lookup.
If you absolutely have to do the transformation on the dimension table data, you can override the SQL generated by the Lookup by checking the enable memory restriction option. This will negatively impact performance though. Or you could use a Merge Join (getting your data from the dimension via an OLE DB Source, run it through a Derived Column transform, thenMerge Join it to the stage data.
|||That Really helps, Thanks a lot, howevere another small thing, I have my datatypes different on the Dimesion table and the Stage table , its not allowing me to do a lookup, is there any workaround for this? also If I end up writing sql queries inside this task then I might be better of using the same SP to load the fact, any suggestions on this from performance perspetive. Appreciate your help.
|||
db_guy wrote:
That Really helps, Thanks a lot, howevere another small thing, I have my datatypes different on the Dimesion table and the Stage table , its not allowing me to do a lookup, is there any workaround for this? also If I end up writing sql queries inside this task then I might be better of using the same SP to load the fact, any suggestions on this from performance perspetive. Appreciate your help.
You can use a derived column to convert the input data type to match that of the column in the lookup table.|||Thanks for ur help Phil