Data stream and SQL

Hi all,

I am just trying to establish if there is a way that I can capture a direct data stream(feed) from another server and have it input into a table.

The scenario is -
I have a PABX that is outputting a stream of data with each record being 83 characters long. Each of the fields is seperated by a space. The end of the record is distinguished by a "Line Feed". The port it is being sent to on my local machine is Port 5000.

Is this possible to do or not? I know that some of our DB guys who use oracle can do it, I would like to have this as a MSSQL database so that I can use it and manipulate it a bit further.

Thanks.So, the PORT is the output or the port is what you can connect to with TCP?|||Port 5000 is set on my SQL Server that the information (from PABX) is sent to. So I need to capture information from this allocated TCP port.

Data source problem with data-driven subscription

I have a stored procedure that receives an input parameter of ReporID and then sends back the appropriate dataset to the data-driven subscription. In the stored procedure, there is some concatenated sql in producting the final select statement to send to the subscription, but it is generating the following error:

The dataset cannot be generated. An error occurred while connecting to a data source, or the query is not valid for the data source. (rsCannotPrepareQuery) Get Online Help

Insert Error: Column name or number of supplied values does not match table definition.

Because there are some optional columns that need to conditionally appear based on the report, I can't avoid concatenation. My only other option is to have hundreds of individual tables to source all our reports, which I don't have time to manage.

Please let me know how I can allow the data-driven subscription setup process to discover the columns for my concatenated SQL so I can seutp my schedules based on the single stored procedure with all my logic nested in my table structure.

Thanks.

If anyone has an answer, even that I can't accomplish what I want to, I would love to hear it. Otherwise, I will have to march down a very high-maintenance road that will probalby encompass more objects than I can maintain along with my other responsibilities.

I appreciate any help you can offer.

|||>>Because there are some optional columns that need to conditionally appear based on the report, I can't avoid concatenation.

Does the result of the concatenation always result in the same number of columns according to your report definition, or could it possibly be creating the wrong result sometimes?

I do understand that your report has optional columns. However the dataset as defined in the report does have a set number of columns, some of which you presumably suppress for some runs. I'm saying that the stored proc may have to include placeholders for those columns in cases where they are not needed, to match up with what the RDL has defined.

Does that make sense? I'm not 100% sure that this is your problem, from your description, but it seems like a likely cause.

>L<

|||

Lisa, thanks for the response. I understand what you are saying but my solution is much more complex... but designed for very simple administration once the backend work is done.

I have (for starters) 76 reports x 50 client databases x ? custom scheduling requests with unique parameters. I am facilitating all of the interactive reporting and scheduled reporting through one copy of the 76 reports.

In addition, to minimize the number of objects to maintian I have a single stored procedure that is designed to act as a data source for all data-driven subscriptions for any report, receiving in some combination of ReportID and SettingGroup. This allows me to use structure to drive the entire operation and later develop an application front end where these complex schedule setups can be managed by anyone with the business knowledge to modify boolean values and make selections in an interface.

The complexity enters with the input parameters for reports which vary greatly. Some reports require no input parameters and always use default values coded into the report. Others may use two specific input parameters in one schedule and just one (with default on others) for a different schedule. Plus the input parameters for different reports are notably going to be different based on the nature of the report.

I have addressed input parameters in a way that requires no manual schema administration. The parameter setting names, values, and datatypes are stored in rows, one row per column name, and identified by the ReportID, SettingGroup, and ClientCode. When the stored procedure for the data source of the data-driven subscription executes, it checks for any related parameter setting records. If they exist, it creates a table on the fly, converting the row values for column definitions/values into actual columns of data. Then the concatenated SQL selects the standard columns plus any parameter settings as needed.

It all works perfectly, except that the "schema discovery" doesn't work in the data-driven subscription setup process. If i could work around that somehow, I would be set. Otherwise, i will probably be forced to have my process dynamically create a table for every report using the same stored procedure. The problem with this solution is that I can't execute the stored procedure at run time for the report so that the data is fully updated with any setting changes or ClientCode activiation/inactivations before the report runs.

This may be too much information, but I'm just frustrated because I'm sitting on the edge of completion for an elegant design that will make it possible for me to manage all these subscriptions while fulfilling my DBA, System Admin, and Developer roles. Otherwise, I fear a lot of unpaid overtime in my future.

If there is a way for me to trigger the stored procedure to run before the data-driven subscriptions run against a static table that will work too. I just don't know if that is an option.

Thanks in advance for your help.

|||

HI there,

I do appreciate the complexity of your situation and honestly didn't mean to minimize it by trying to explore how and what wasn't working out in a simple way.

The important thing to remember is that all complexity must be sieved down to a pure problem and most wide architecture has to be funnelled into a narrow place when it comes to integrating your work with some standardized, simplified solution (which is what RS data subscriptions is).

As I see it there are two ways we can go about this:

1) You can tell us more about your architecture and provide a simplified, reproducible version that, while not sharing all the features of your app, does serve to illustrate the place at which things go wrong with subscriptions. Then we can kick it around and try to see what changes would serve to "jimmy" the thing to work as you need.

In the process we may suggest things that will not work in your more fully-worked scenarios, and this may frustrate you further -- please be patient with us if this happens <s>.

We may also suggest architectural changes that may be extremely frustrating, even though we understand you're really happy with what you've come up with (except for this one piece)... and we'll understand if you reject those solutions.

2) You can accept the fact that RS data subscriptions are only one way of handling what you need to do, and not the best way at that. I use them, but also do *not* use them where they do not fit a business need or where my framework seems to "stretch" them in ways they were not meant to stretch.

As I have described on another thread (see http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2019010&SiteID=1) there are many scenarios in which, quite frankly, RS data subscriptions simply do not do what one wants to do, whether in terms of exception reporting, subscription channels, or whatever -- and it is a lot better to roll your own.

It seems to me that your scenario is one of them, and that SSIS is a good fit here. You want to run a stored proc before running the report, go for it.

Just remember that RS data subscriptions are only one potential flow through which scheduled reports can be managed and this is really not a "one size fits all" type of story. The actual elements of the story can be endlessly recombined (see that thread) and each task element is really pretty simple to do so from my POV RS data subscriptions isn't adding all that much. If it's getting in your way, jettison it.

I hope this helps.

>L<

|||

First, I didn't mean to imply your were minimizing the complexity of my situation. I know you were just responding to a minimally defined issue that I threw out there, and it is appreciated.

Second, you are my hero! I'm a huge fan of SSIS and use it often for looping automation of tasks as well as data flows. It never occured to me to use it for this, but after looking at your other forum post and your script code, I see that this is probably the road for me. It allows me the flexibility to use very open ended, branching logic so I can handle all exceptions in one package making for easier management. I can even consolidate an FTP SSIS package I created for report delivery into this one package for an all encompassing process.

Thank you for your time and insight.

Sincerely,

Hugh

|||

Good for you! The important thing is that you are willing to do the work and you see the possibilities in the tools. It's people who think that there is something holy about the shortcuts presented along with the base tool set -- again, IMHO, data subscriptions is just one such shortcut -- that really have problems.

One needs to see that the shortcuts provide you with insight into the *potential* of the tools, not the only possible end result... you clearly get this, and you'll do fine!

Regards,

>L<

Data source problem with data-driven subscription

I have a stored procedure that receives an input parameter of ReporID and then sends back the appropriate dataset to the data-driven subscription. In the stored procedure, there is some concatenated sql in producting the final select statement to send to the subscription, but it is generating the following error:

The dataset cannot be generated. An error occurred while connecting to a data source, or the query is not valid for the data source. (rsCannotPrepareQuery) Get Online Help

Insert Error: Column name or number of supplied values does not match table definition.

Because there are some optional columns that need to conditionally appear based on the report, I can't avoid concatenation. My only other option is to have hundreds of individual tables to source all our reports, which I don't have time to manage.

Please let me know how I can allow the data-driven subscription setup process to discover the columns for my concatenated SQL so I can seutp my schedules based on the single stored procedure with all my logic nested in my table structure.

Thanks.

If anyone has an answer, even that I can't accomplish what I want to, I would love to hear it. Otherwise, I will have to march down a very high-maintenance road that will probalby encompass more objects than I can maintain along with my other responsibilities.

I appreciate any help you can offer.

|||>>Because there are some optional columns that need to conditionally appear based on the report, I can't avoid concatenation.

Does the result of the concatenation always result in the same number of columns according to your report definition, or could it possibly be creating the wrong result sometimes?

I do understand that your report has optional columns. However the dataset as defined in the report does have a set number of columns, some of which you presumably suppress for some runs. I'm saying that the stored proc may have to include placeholders for those columns in cases where they are not needed, to match up with what the RDL has defined.

Does that make sense? I'm not 100% sure that this is your problem, from your description, but it seems like a likely cause.

>L<

|||

Lisa, thanks for the response. I understand what you are saying but my solution is much more complex... but designed for very simple administration once the backend work is done.

I have (for starters) 76 reports x 50 client databases x ? custom scheduling requests with unique parameters. I am facilitating all of the interactive reporting and scheduled reporting through one copy of the 76 reports.

In addition, to minimize the number of objects to maintian I have a single stored procedure that is designed to act as a data source for all data-driven subscriptions for any report, receiving in some combination of ReportID and SettingGroup. This allows me to use structure to drive the entire operation and later develop an application front end where these complex schedule setups can be managed by anyone with the business knowledge to modify boolean values and make selections in an interface.

The complexity enters with the input parameters for reports which vary greatly. Some reports require no input parameters and always use default values coded into the report. Others may use two specific input parameters in one schedule and just one (with default on others) for a different schedule. Plus the input parameters for different reports are notably going to be different based on the nature of the report.

I have addressed input parameters in a way that requires no manual schema administration. The parameter setting names, values, and datatypes are stored in rows, one row per column name, and identified by the ReportID, SettingGroup, and ClientCode. When the stored procedure for the data source of the data-driven subscription executes, it checks for any related parameter setting records. If they exist, it creates a table on the fly, converting the row values for column definitions/values into actual columns of data. Then the concatenated SQL selects the standard columns plus any parameter settings as needed.

It all works perfectly, except that the "schema discovery" doesn't work in the data-driven subscription setup process. If i could work around that somehow, I would be set. Otherwise, i will probably be forced to have my process dynamically create a table for every report using the same stored procedure. The problem with this solution is that I can't execute the stored procedure at run time for the report so that the data is fully updated with any setting changes or ClientCode activiation/inactivations before the report runs.

This may be too much information, but I'm just frustrated because I'm sitting on the edge of completion for an elegant design that will make it possible for me to manage all these subscriptions while fulfilling my DBA, System Admin, and Developer roles. Otherwise, I fear a lot of unpaid overtime in my future.

If there is a way for me to trigger the stored procedure to run before the data-driven subscriptions run against a static table that will work too. I just don't know if that is an option.

Thanks in advance for your help.

|||

HI there,

I do appreciate the complexity of your situation and honestly didn't mean to minimize it by trying to explore how and what wasn't working out in a simple way.

The important thing to remember is that all complexity must be sieved down to a pure problem and most wide architecture has to be funnelled into a narrow place when it comes to integrating your work with some standardized, simplified solution (which is what RS data subscriptions is).

As I see it there are two ways we can go about this:

1) You can tell us more about your architecture and provide a simplified, reproducible version that, while not sharing all the features of your app, does serve to illustrate the place at which things go wrong with subscriptions. Then we can kick it around and try to see what changes would serve to "jimmy" the thing to work as you need.

In the process we may suggest things that will not work in your more fully-worked scenarios, and this may frustrate you further -- please be patient with us if this happens <s>.

We may also suggest architectural changes that may be extremely frustrating, even though we understand you're really happy with what you've come up with (except for this one piece)... and we'll understand if you reject those solutions.

2) You can accept the fact that RS data subscriptions are only one way of handling what you need to do, and not the best way at that. I use them, but also do *not* use them where they do not fit a business need or where my framework seems to "stretch" them in ways they were not meant to stretch.

As I have described on another thread (see http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2019010&SiteID=1) there are many scenarios in which, quite frankly, RS data subscriptions simply do not do what one wants to do, whether in terms of exception reporting, subscription channels, or whatever -- and it is a lot better to roll your own.

It seems to me that your scenario is one of them, and that SSIS is a good fit here. You want to run a stored proc before running the report, go for it.

Just remember that RS data subscriptions are only one potential flow through which scheduled reports can be managed and this is really not a "one size fits all" type of story. The actual elements of the story can be endlessly recombined (see that thread) and each task element is really pretty simple to do so from my POV RS data subscriptions isn't adding all that much. If it's getting in your way, jettison it.

I hope this helps.

>L<

|||

First, I didn't mean to imply your were minimizing the complexity of my situation. I know you were just responding to a minimally defined issue that I threw out there, and it is appreciated.

Second, you are my hero! I'm a huge fan of SSIS and use it often for looping automation of tasks as well as data flows. It never occured to me to use it for this, but after looking at your other forum post and your script code, I see that this is probably the road for me. It allows me the flexibility to use very open ended, branching logic so I can handle all exceptions in one package making for easier management. I can even consolidate an FTP SSIS package I created for report delivery into this one package for an all encompassing process.

Thank you for your time and insight.

Sincerely,

Hugh

|||

Good for you! The important thing is that you are willing to do the work and you see the possibilities in the tools. It's people who think that there is something holy about the shortcuts presented along with the base tool set -- again, IMHO, data subscriptions is just one such shortcut -- that really have problems.

One needs to see that the shortcuts provide you with insight into the *potential* of the tools, not the only possible end result... you clearly get this, and you'll do fine!

Regards,

>L<

Data source problem with data-driven subscription

I have a stored procedure that receives an input parameter of ReporID and then sends back the appropriate dataset to the data-driven subscription. In the stored procedure, there is some concatenated sql in producting the final select statement to send to the subscription, but it is generating the following error:

The dataset cannot be generated. An error occurred while connecting to a data source, or the query is not valid for the data source. (rsCannotPrepareQuery) Get Online Help

Insert Error: Column name or number of supplied values does not match table definition.

Because there are some optional columns that need to conditionally appear based on the report, I can't avoid concatenation. My only other option is to have hundreds of individual tables to source all our reports, which I don't have time to manage.

Please let me know how I can allow the data-driven subscription setup process to discover the columns for my concatenated SQL so I can seutp my schedules based on the single stored procedure with all my logic nested in my table structure.

Thanks.

If anyone has an answer, even that I can't accomplish what I want to, I would love to hear it. Otherwise, I will have to march down a very high-maintenance road that will probalby encompass more objects than I can maintain along with my other responsibilities.

I appreciate any help you can offer.

|||>>Because there are some optional columns that need to conditionally appear based on the report, I can't avoid concatenation.

Does the result of the concatenation always result in the same number of columns according to your report definition, or could it possibly be creating the wrong result sometimes?

I do understand that your report has optional columns. However the dataset as defined in the report does have a set number of columns, some of which you presumably suppress for some runs. I'm saying that the stored proc may have to include placeholders for those columns in cases where they are not needed, to match up with what the RDL has defined.

Does that make sense? I'm not 100% sure that this is your problem, from your description, but it seems like a likely cause.

>L<

|||

Lisa, thanks for the response. I understand what you are saying but my solution is much more complex... but designed for very simple administration once the backend work is done.

I have (for starters) 76 reports x 50 client databases x ? custom scheduling requests with unique parameters. I am facilitating all of the interactive reporting and scheduled reporting through one copy of the 76 reports.

In addition, to minimize the number of objects to maintian I have a single stored procedure that is designed to act as a data source for all data-driven subscriptions for any report, receiving in some combination of ReportID and SettingGroup. This allows me to use structure to drive the entire operation and later develop an application front end where these complex schedule setups can be managed by anyone with the business knowledge to modify boolean values and make selections in an interface.

The complexity enters with the input parameters for reports which vary greatly. Some reports require no input parameters and always use default values coded into the report. Others may use two specific input parameters in one schedule and just one (with default on others) for a different schedule. Plus the input parameters for different reports are notably going to be different based on the nature of the report.

I have addressed input parameters in a way that requires no manual schema administration. The parameter setting names, values, and datatypes are stored in rows, one row per column name, and identified by the ReportID, SettingGroup, and ClientCode. When the stored procedure for the data source of the data-driven subscription executes, it checks for any related parameter setting records. If they exist, it creates a table on the fly, converting the row values for column definitions/values into actual columns of data. Then the concatenated SQL selects the standard columns plus any parameter settings as needed.

It all works perfectly, except that the "schema discovery" doesn't work in the data-driven subscription setup process. If i could work around that somehow, I would be set. Otherwise, i will probably be forced to have my process dynamically create a table for every report using the same stored procedure. The problem with this solution is that I can't execute the stored procedure at run time for the report so that the data is fully updated with any setting changes or ClientCode activiation/inactivations before the report runs.

This may be too much information, but I'm just frustrated because I'm sitting on the edge of completion for an elegant design that will make it possible for me to manage all these subscriptions while fulfilling my DBA, System Admin, and Developer roles. Otherwise, I fear a lot of unpaid overtime in my future.

If there is a way for me to trigger the stored procedure to run before the data-driven subscriptions run against a static table that will work too. I just don't know if that is an option.

Thanks in advance for your help.

|||

HI there,

I do appreciate the complexity of your situation and honestly didn't mean to minimize it by trying to explore how and what wasn't working out in a simple way.

The important thing to remember is that all complexity must be sieved down to a pure problem and most wide architecture has to be funnelled into a narrow place when it comes to integrating your work with some standardized, simplified solution (which is what RS data subscriptions is).

As I see it there are two ways we can go about this:

1) You can tell us more about your architecture and provide a simplified, reproducible version that, while not sharing all the features of your app, does serve to illustrate the place at which things go wrong with subscriptions. Then we can kick it around and try to see what changes would serve to "jimmy" the thing to work as you need.

In the process we may suggest things that will not work in your more fully-worked scenarios, and this may frustrate you further -- please be patient with us if this happens <s>.

We may also suggest architectural changes that may be extremely frustrating, even though we understand you're really happy with what you've come up with (except for this one piece)... and we'll understand if you reject those solutions.

2) You can accept the fact that RS data subscriptions are only one way of handling what you need to do, and not the best way at that. I use them, but also do *not* use them where they do not fit a business need or where my framework seems to "stretch" them in ways they were not meant to stretch.

As I have described on another thread (see http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2019010&SiteID=1) there are many scenarios in which, quite frankly, RS data subscriptions simply do not do what one wants to do, whether in terms of exception reporting, subscription channels, or whatever -- and it is a lot better to roll your own.

It seems to me that your scenario is one of them, and that SSIS is a good fit here. You want to run a stored proc before running the report, go for it.

Just remember that RS data subscriptions are only one potential flow through which scheduled reports can be managed and this is really not a "one size fits all" type of story. The actual elements of the story can be endlessly recombined (see that thread) and each task element is really pretty simple to do so from my POV RS data subscriptions isn't adding all that much. If it's getting in your way, jettison it.

I hope this helps.

>L<

|||

First, I didn't mean to imply your were minimizing the complexity of my situation. I know you were just responding to a minimally defined issue that I threw out there, and it is appreciated.

Second, you are my hero! I'm a huge fan of SSIS and use it often for looping automation of tasks as well as data flows. It never occured to me to use it for this, but after looking at your other forum post and your script code, I see that this is probably the road for me. It allows me the flexibility to use very open ended, branching logic so I can handle all exceptions in one package making for easier management. I can even consolidate an FTP SSIS package I created for report delivery into this one package for an all encompassing process.

Thank you for your time and insight.

Sincerely,

Hugh

|||

Good for you! The important thing is that you are willing to do the work and you see the possibilities in the tools. It's people who think that there is something holy about the shortcuts presented along with the base tool set -- again, IMHO, data subscriptions is just one such shortcut -- that really have problems.

One needs to see that the shortcuts provide you with insight into the *potential* of the tools, not the only possible end result... you clearly get this, and you'll do fine!

Regards,

>L<

sql

Data script task - how to generate multiple rows from one row ? (string splitter)

Hi

in input we have a set of rows, each one with a column containing a string (eg: "AAOOOOAAAOOA").

We'd like to split this string (using a vb.net data script task) into tokens (eg: "AA", then "OOOO","AAA","OO","A"), and to output one line per token.

How can we achieve that with a vb.net data script task ? (Or anything else ?)

best regards

ThibautJust take your source and throw it into a derived column transformation where you'll perform your string split to create new fields for each "token." Then you'll go into an unpivot transformation where you'll take the new columns and turn them into rows.

Why use a script task when you can use the optimized data flow tools as-is?|||

You need to create an asynchrnous transform. So add the transform, select and also setup columns as required. Ensure the SynchronousInputID of the output is set to 0, making it async.

Then read rows and add them to the output as required. Sum dummy code -

Public Overrides Sub Input_ProcessInputRow(ByVal Row As InputBuffer)

' Read the rows in...

While Row.NextRow() ' This happens once for each input row

' Do something here, and as required, add rows to the output, use a loop or whatever

For i As Int = i < 10

With OutputBuffer

.AddRow() ' Adding a new output row and setting values. Can do this as many times as we like, 0 or more times in the context of this input row loop interation

.Asset = Row.InputColumn

.Product = i

Next

End While

If Row.EndOfRowset Then

OutputBuffer.SetEndOfRowset()

End If

End Sub

|||Thanks a lot Darren ! That's really perfect (and thanks for the detailed sample, I really appreciate).

regards,

Thibaut|||Just be careful because some have tested the script task and it performs slower than the other, native dataflow tasks.|||Phil, I agree in principal, the native stock components should be faster as a rule, but have you got any references?|||

DarrenSQLIS wrote:

Phil, I agree in principal, the native stock components should be faster as a rule, but have you got any references?

Yep, no problem... http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=857796&SiteID=1|||Hi Phil

thanks for your input as well. Like Darren, I agree that a good rule of thumb is that a pipeline component will work generally faster than a corresponding script component.

A script component is generally one-shot code, which generally doesn't get the same level of testing and optimization. In the article you point to, the script task benchmark shows an average CPU usage of 5% (which is often a sign of synchronization issues, contention etc). Here (like suggested by a commenter), a modification of the implementation (like handling sets of rows instead of one row at a time) would most likely boost the performance a lot.

So I wouldn't draw conclusions based on a single script example, given that each implementation is likely to vary a lot in terms of performance and memory consumption - just like any kind of code!

But anyway - it seems that I have two solutions for my problem now. I don't hesitate to use script task when they prove useful, but I first try to stick to the pipeline components.

I'll keep you posted!

thanks again for the input

Thibaut Barrère|||Hi

I've finished the job using a script task (I will report back the details later).

I'm curious about how it would have been possible to implement this using a derived column transformation (at first sight I couldn't find out).

Phil could you give a bit more details ? I could not find any real string splitting functions in SSIS - were you thinking of using a FIND function recursively (or any other feature I've missed ?)

cheers

Thibaut

Data mining cube with 0-value facts

I'm defining a mining structure against an OLAP dimension. The continuous value that I'm using both as input and for forecasting represents the time to complete a certain process.

There's something that strikes me as if it could be a problem, but I'm not sure. Our fact table has multiple columns (with multiple correponding measures in the cube). The "time-to-complete" measure is only populated on some of the fact rows - the rows that represent completion information. Other rows represent other information, and the "time-to-complete" value is set to 0. This works fine for cumulative time-to-complete and average time-to-complete, but it seems like it could mess up data mining. Will those 0-value facts skew the mining results? I'm not seeing a way to filter out those entries and only include the non-zero facts in the mining processing.

Or perhaps I'm totally misunderstanding something, which is quite possible. :)

The zeroes will mess you up in DM and OLAP. An average of a value ignores nulls, but not zeroes.

You can set your measure to retain null values in the fact table - check out the data binding settings.

Hope this helps,

Richard

|||The zeros are properly handled in our average calculated measures in OLAP (we don't use the EE AverageOfChildren aggregation function).

Sounds like it will screw up DM stuff, though. If the values were nulls instead of 0, would DM handle it correctly?|||

Sine you've modeled the variable as continous, a 0 value will be treated as a possible value whereas a null would be treated as missing by DM. They would hence generate different DM model.

If the only zero value in your data correspond to null (missing) value, you can either filter those out from the input data or have another computed measure be null whenever the measure is 0 and use that measure as input to the data mining algorithm.

Hope this helps

|||>you can either filter those out from the input data

How would I do that? There isn't a dimension slice that represents this condition, and that's the only filtering that I've been able to find so far.

A computed measure wouldn't be ideal, but it might work. I'll think about that.
|||Sorry, my earlier statement was not accurate. You cannot filter by measure values in OLAP Mining Model.|||OK, after doing a little more testing, I'm more confused.

As a test, I created a new calculation that takes the original measure and converts 0-valued facts to Nulls. I added that new calculation to the mining structure and models and processed them. Then I ran a singleton prediction query for both the original and new measures. They came up with the exact same results. So, as far as I can tell, either:

a) The 0-value facts don't affect the mining results
b) The null facts are treated the same as the 0 facts (and not ignored)
c) Something about how I'm doing my test is flawed.

Anyone have any suggestions or thoughts here?|||It's possible that the column doesn't have any impact on the result at all? What algorithm are you using and does the column show up as "important" in the viewer?|||Actually, I'm working with a degeneratively simple test case here, where the mining structure only includes one input - a single continuous value, used both as input and for prediction. Perhaps that's too trivial for a reasonable test.

I'm using the Neural Network algorithm, but I can't bring up the viewer - I just get an error (which I reported in another thread in these forums).|||

Aha!

That's the reason for this problem and may be the reason for the viewer problem as well. It seems you found a bug in the system, the bug being that the software didn't return an error when you tried to create this model .

When you mark a column as "input" what you are stating is "use this column as an input to predict other columns." When you mark it as output you are stating "determine values of this column based on all other inputs." We have the concept of "input AND output" because we allow you to predict multiple attributes in a single model.

Therefore, what is happening is that for that column, you have no inputs, and it is an input for no other columns, since there are no more columns for which it could be an input. Simply put, it's an invalid model definition which we should have rejected from the start.

Thanks

-Jamie

|||Doh! Did I mention I was a noob with data mining? :)

I fixed the model, and the results seem to make more sense now. However, it didn't fix the problem with the mining model viewer.|||It's hard to say - there may not be enough differentiation for the viewer to show patterns at that point. I would try things out with a more well known dataset - e.g. the tutorial so you can get a feel for how the tools work in a tested environment and then see if the issue seems to be the software not working correctly or your data not being rich enough to demonstrate descriptive results.

Data input problem

In Visual Web Developer Ihave created a data input form based on the documentation I found in the .NETFramework Class Library (SqlDataSource.InsertCommand Property). Originally my form contained 4 textboxes(FirstName, LastName, Phone, Email) and worked fine. All this data is nvarchar string data.

When I added a checkbox Iget an error that"Stringwas not recognized as a valid Boolean" when the checkbox is checked. When the checkbox is unchecked the data is input without a problem. This is a bit data field in the table.

Here is my VB code, InsertParameters for my SqlDataSource1, and button code:

<scriptrunat="server">

PrivateSub InsertData(ByValSourceAsObject,ByVal eAsEventArgs)

SqlDataSource1.Insert()

EndSub

</script>

<InsertParameters>

<asp:formParameterName="FirstName"Type="String"FormField="txtFirstName"/>

<asp:formParameterName="LastName"Type="String"FormField="txtLastName"/>

<asp:formParameterName="Phone"Type="String"FormField="txtPhone"/>

<asp:formParameterName="Email"Type="String"FormField="txtEmail"/>

<asp:formParameterName="FreeInfo"Type="Boolean"FormField="checkbox1"/>

</InsertParameters>

<asp:Buttonid="Button1"runat="server"text="Submit"OnClick="insertdata"/>

I suspect that the problemis with the VB code which will only accept string input data, but don't knowhow to fix it. Any thoughts on how tofix this problem? Thanks in advance forany help provided.

Check box return "On" whener page is postback

It does'nt return checked or unchecked.

You have to check this checkBox by code before inserting the data.

If checkBox is checked set the parametervalue = 1 else 0 .

Thank

Kunal

|||

Thanks for your feedback. I tried to implement your suggestions, but get this error message:

String was not recognized as a valid Boolean.

Here is my new VB code:

Private Sub InsertName(ByVal Source As Object, ByVal e As EventArgs)
If (CheckBox1.Checked = True) Then
CheckBox1.Equals(1)
Else : CheckBox1.Equals(0)
End If
SqlDataSource1.Insert()
End Sub

I am a newbie to VB, so my codeprobably doesn't accurately implement your recommendations. Any helpyou can provide in getting this to work will be greatly appreciated.

|||

Hi,

The formParameter always gets value from the Text property of the controls. In this case, you can try to use ControlParameter instead.

<asp:controlParameter Name="FreeInfo" ControlId="checkbox1" PropertyName="Checked"/>

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

|||

Thanks Kevin. That's exactly what I needed.

Data Flow: Converting data in multiple columns

Hi,

I'm just wondering what's the best approach in Data Flow to convert the following input file format:

Date, Code1, Value1, Code2, Value2

1-Jan-2006, abc1, 20.00, xyz3, 35.00

2-Jan-2006, abc1, 30.00, xyz5, 6.30

into the following output format (to be loaded into a SQL DB):

Date, Code, Value

1-Jan-2006, abc1, 20.00

1-Jan-2006, xyz3, 35.00

2-Jan-2006, abc1, 30.00

2-Jan-2006, xyz5, 6.30

I'm quite new to SSIS, so, I would appreciate detailed steps if possible. Thanks.

Ok, I have found a method to get what I wanted, but I'm not sure if it's the best approach. Any comments appreciated.

I first used a Multicast and feed the input data into 2 Script Components. The first Script Component has input columns of Date, Code1 & Value1, while 2nd Script Component has input columns of Date, Code2 & Value2. In both Script Components, the output columns are Date, Code & Value.

The output of each Script Component are then connected to a Sort and both Sort goes to a Merge. The output from the Merge will then go to a OLE DB Destination to be loaded into a SQL DB (not 2005 version).

Hope it makes sense.

|||

I think Unpivot tranformation cam deliver the same funcionality you put in your script components; but the multicast; the sort and merge join would be required anyway.

Rafael Salas

|||

Hi,

Why dont you have a union step feeded twice by the multicast

then

Delete and add lines so that you would have the following

Date date date

Field 2 cod1 cod2

Field 3 value1 value2

At the end you should have what you want,

Regards,