Do I hear a volunteer?
Thanks,
IanONot sure what you are asking, but why not skip the Import Wizard and build your own package?
I perform data mining on all products and a specific product category.
Do I need to create 2 data source views, one for all products and the other one for the specific product category?
Afterward, I run the Data Mining Wizard 2 times to create 2 mining structures.
I also need to add the same mining model (e.g. Bayes, Cluster) to each of these mining structures.
Is there any simple way to do it?
Thanks.
Joe.
Yes you'd need to use two data sources. However instead of going through the wizard twice and recreating all the contained models, you could use SQL Mgmt Studio to script the mining structure and its contained models, modify the name in the generated script and then run t. To do this, right-click on the mining structure in SQL Mgmt Studio and select "Script Mining Structure As"-> CREATE TO.
|||Thanks Raman.Hi, all here,
I have question about copying a cube from other project to my current project by using XMLA, if they have the same data source and data source view? Is it possible?
Thanks a lot for any guidance.
You might have a problem with matching dimension names and ID's.
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Here is the documentation on the officialy supported data sources for Analysis Services:
http://msdn2.microsoft.com/en-us/library/ms175608.aspx
Here is the relevant info about DB2
IBM DB2 8.1 using Microsoft OLE DB Provider for DB2 (x86, x64, ia64) - only available for Microsoft SQL Server 2005 Enterprise Edition or Microsoft SQL Server 2005 Developer Edition and downloadable as part of the Feature Pack for Microsoft SQL Server 2005 Service Pack 1.Sorry, I misunderstood your question :(
In general, yes you can have multiple data sources in the same AS project. In theory it should work with both SQL and DB2 data sources. But there are certain implementation details around multiple data source support, that make me cautious about DB2 though. I know that several SQL data sources will work without problem, but I won't be surprised if there will be some issues with DB2 as a second data source.
|||I'm getting the following error when trying to build the dimension from DB2:
OLE DB error: OLE DB or ODBC error: Ad hoc access to OLE DB provider 'DB2OLEDB' has been denied. You must access this provider through a linked server.; 42000.
Any thoughts?
|||AS uses OPENROWSET when there are multiple data sources, and probably due to security it is off by default on SQL Server side.
I beleive there is a DisallowAdhocAccess registry setting which should be set to 0 for DB2OLEDB provider. I beleive it can be found under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Providers
|||Not sure whether you're using mainframe DB2, with a (18-char) limitation on identifier length - the issue that I ran into last year is that the SSAS DB2 cartridge (where handling of the 18 char limitation is specified) got bypassed when using SQL Server as a source with DB2. Haven't revisited this recently, to see if it's been fixed, but this issue was also mentioned in Teo Lachev's blog:
http://prologika.com/CS/blogs/blog/archive/2006/03/05/935.aspx
>>
A little known fact about the SSAS data architecture is that it uses “cartridges” to communicate with the data source. In brief, a cartridge is a XSL stylesheet that defines capabilities of a data source, as well as the rules for optimizing the SQL statements for relational querying and writing. SSAS 2005 ships with set of cartridges for Jet, SQL 70, SQL 2000, Oracle, Teradata, and DB2, which can be found in the \Program Files\Microsoft SQL Server\MSSQL.2\OLAP\bin\Cartridges folder. Vendors can plug in (server restart required) cartridges for other data sources if needed.
One gotcha is when the UDM uses multiple data sources in a single data source view. This scenario requires that the primary data source must be SQL Server because behind the scenes the server uses the SQL Server-specific OPENROWSET statement to extract data from the secondary data source(s). The problem with this approach is that it effectively bypasses the installed cartridge for the non-SQL Server data source. As a result, processing queries that normally execute just fine when the DSV uses that data source only, fail to execute in a multi-data source DSV.
There are at least three workarounds for this predicament. First, you can replace each table in the DSV with a named query which uses the right native syntax. Second, you can link the data server to your SQL Server and wrap the linked server tables with SQL views. A third solution is to split UDM per a data source – a SQL Server UDM and another UDM for the second data source. Then, you can link the dimensions and measure groups from one UDM to another. As you have probably guessed it, all of the above approaches may present maintenance and operational challenges. It will be great if a service pack of a future release solve this issue and honors the cartridges with heterogeneous queries.
>>
|||db1 - SQL Server 2005
db2 - DB2\AIX64
We are using the Microsoft OLE DB Provider for DB2.
On issue is encountered when defining the DB2 connection; AIX is not an option for the OS, so we are using DB2\NT.
I can get both Data Sources defined and the connections test successfully.
I can get both Data Views defined just fine.
When I build the Dimension using the DB2 Data Source, the only way to get it to process was to Check the box in the SQL Data Source definition that read something like 'Maintain a references to another object'. That actually changes the Provider in the connection string to DB2OLEDB in the SQL connection string. This does not appear to be correct.
We've checked the Registry and everything is fine there.
Any other thoughts?
Hello.
In SSIS, is it possible to add a record number to each row of data as I copy it from the source to the destination.
An example of my source data is below, For each MemberID want to record the number of times it occurs in the table.
MemberID
2898
2899
2899
What I want it to look like when it gets to the destination is:
MemberID RecordNumber
2898 1
2899 1
2899 2
Like an Identity column I suppose, not for the whole table but for each MemberID.
Thanks
Looks possible to me using a custom script component to compare incoming fields to the last set coming in. Would work nicely if the data is sorted.|||
bobbins wrote:
Hello.
In SSIS, is it possible to add a record number to each row of data as I copy it from the source to the destination.
An example of my source data is below, For each MemberID want to record the number of times it occurs in the table.
MemberID
2898
2899
2899
What I want it to look like when it gets to the destination is:
MemberID RecordNumber
2898 1
2899 1
2899 2
Like an Identity column I suppose, not for the whole table but for each MemberID.
Thanks
The Rank transformation will do this for you:
Rank Transform
http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Rank-Transform.aspx
-Jamie
|||
Jamie:
That's a very cool and useful component. Thanks! Great work.
- Mike
|||Jamie:
That's a very cool and useful component. Thanks! Great work.
- Mike
Mike,
Its a pleasure. I've been after that sort of feedback for a long time :)
Again I have to reiterate Darren Green's contribution. It was my idea but he realised the idea.
-Jamie
|||
Thanks for the info, I haven't got round to doing this yet but I'm sure it'll work.
Thanks very much, top people!
|||I've downloaded it and installed it but how do I use it in my packages? Should I now be able to see it in Visual Studio amongst all the other data flow transformation options.
Cheers
|||
I've downloaded it and installed it but how do I use it in my packages? Should I now be able to see it in Visual Studio amongst all the other data flow transformation options.
Cheers
The instructions here: http://msdn2.microsoft.com/fr-fr/library/ms136125.aspx are for custom tasks but its intuitively similar for custom components. Look for the section entitled "How to use the Task in SSIS Designer"
-Jamie
|||If you just want the row number function, then use the Row Number Tx http://www.sqlis.com/default.aspx?93, as it does not require a sorted input.|||
I still can't do it because when I select 'Choose toolbox items' in the data flow designer it just list what's there already, I only have an option to browse on the COM and .NET components, is this where I should be putting it?
Thanks.
|||You are looking at the Data Flow page in the Choose Toolbox Items?
You have scolled down to find the transform, in alphabetical order?
Double check this, and perhaps compare with some screenshots here-
Konesans - Frequently Asked Questions - How do I install a task or transform component?
(http://www.konesans.com/faq.aspx#installtask)
If this does not help, on the machine you are running Visual Studio, go to C:\Program Files\Microsoft SQL Server\90\DTS\PipelineComponents, and look for the DLL file. I'm not sure which transform you are trying right now, so I cannot specify the filename for you here. If the file is not there, then it will never show up in Choose Toolbox Items, so did the install fail?
|||Yes I am.
Yes I have, it's definately not there. I have checked against the link you provided and I cannot see the component in the list.
I am attempting to use the RankTransform component. I think it installed ok as there were no errors. I've searched for the dll ( it's full name is Conchango.SQLServer.SSIS.DataFlow.RankTransform.dll ) found that it is located in C:\Program Files\Microsof SQL Server\90\DTS\CompFldr so this looks like conformation that it has installed ok.
I've copied it to the directory that you specified and opened the 'choose items' again and it was listed, so I've added it.
I'd like to say a big thanks to yourself and Jamie for the help with this, as someone who doesn't know much about Visual Studio and dll's and stuff you've been an massive help.
I'll now try to use it (I'll have to go back to the instructions!) and let you know how it goes.
Thanks again
|||
Ok, the component works great but it is not putting in what I expected it to:
My source data is:
Member Number
2898
2899
2899
I added a sort task to sort it by Member Number ascending.
In the rank transform task I selected Member Number as the sort key and checked the partition box and selected row number as the output.
When the data reached the destination it looked like this:
Member Number Row Number
2898 1
2899 2
2899 3
I'd expected it to look like this:
Member Number Row Number
2898 1
2899 1
2899 2
because I assumed from what I'd selected in the rank transform task that it's essentially running this query:
SELECT [Member Number] , ROW_NUMBER() OVER (PARTITION BY [Member Number] ORDER BY [Member Number]) AS [Row Number]
FROM StatusHistory
which does give me the expected results.
Have I selected the wrong options in the rank transform task or I have I completely got the wrong end of the stick and am trying to use this task for something that it was not designed for?
It's a great thing to have anyway.
Thanks
|||Ok, the component works great but it is not putting in what I expected it to:
My source data is:
Member Number
2898
2899
2899
I added a sort task to sort it by Member Number ascending.
In the rank transform task I selected Member Number as the sort key and checked the partition box and selected row number as the output.
When the data reached the destination it looked like this:
Member Number Row Number
2898 1
2899 2
2899 3
I'd expected it to look like this:
Member Number Row Number
2898 1
2899 1
2899 2
because I assumed from what I'd selected in the rank transform task that it's essentially running this query:
SELECT [Member Number] , ROW_NUMBER() OVER (PARTITION BY [Member Number] ORDER BY [Member Number]) AS [Row Number]
FROM StatusHistory
which does give me the expected results.
Have I selected the wrong options in the rank transform task or I have I completely got the wrong end of the stick and am trying to use this task for something that it was not designed for?
It's a great thing to have anyway.
Thanks
Bobbins,
Thankyou for making me aware of this. it looks as though the partition functionality might not be working. I'll check it out.
-Jamie
|||
Had any luck with it?
Cheers
Hi,
I'm developing a Custom Report Item and would like to fill my properties dialog in design mode with real data. I have the name of the data source and the command text for the corresponding report dataset, read from the report design.
Is it possible to create an ado.net dataset so that I can display and use real data in my properties dialog box? First problem I have is to get the connection string from the data source string. I found no docs for that, especially the Microsoft.ReportingServices.DataExtensions.ReportDataSource class is not documented. Am I on the right way with that?
Thomas
Check out.. http://msdn.microsoft.com/msdnmag/issues/06/10/SQLServer2005/default.aspx
Sorry,
I can't see where that relates to my question. Did I miss the point?
After rereading the article, I didn't find any word on how to get actual data values in design mode.
Maybe I didn't state clear enough what I want to obtain:
I would like to get actual data values from the dataset in design mode in order to use them for a properties dialog. I have access to the sql statement from reportDataSet.CommandText already and I know the datasource name (that somehow is an alias for the db connection). What's missing is how to get the connection string from the datasource - then I can open the connection and read the data using ado.net.
Thomas
|||For connection string:
check the DataSourceDefinition class in Reporting Service web service
Hi,
I'm developing a Custom Report Item and would like to fill my properties dialog in design mode with real data. I have the name of the data source and the command text for the corresponding report dataset, read from the report design.
Is it possible to create an ado.net dataset so that I can display and use real data in my properties dialog box? First problem I have is to get the connection string from the data source string. I found no docs for that, especially the Microsoft.ReportingServices.DataExtensions.ReportDataSource class is not documented. Am I on the right way with that?
Thomas
Check out.. http://msdn.microsoft.com/msdnmag/issues/06/10/SQLServer2005/default.aspx
Sorry,
I can't see where that relates to my question. Did I miss the point?
After rereading the article, I didn't find any word on how to get actual data values in design mode.
Maybe I didn't state clear enough what I want to obtain:
I would like to get actual data values from the dataset in design mode in order to use them for a properties dialog. I have access to the sql statement from reportDataSet.CommandText already and I know the datasource name (that somehow is an alias for the db connection). What's missing is how to get the connection string from the datasource - then I can open the connection and read the data using ado.net.
Thomas
|||For connection string:
check the DataSourceDefinition class in Reporting Service web service
|||Did you ever make any progress on this? I'm trying to get to the Connection String property of the data source from my Custom Report Item code.Can anyone please tell me what is wrong with this query:
rsOtherSubCatagories.Source = "SELECT * FROM SubCatagories WHERE SubCatagoryID = " + Replace(rsSubCatagories__MMColParam, "'", "''") AND CatagoryID = " + Replace(rsOtherSubCatagories__MMColParam, "'", "''")
In DreamWeaver the bit in bold is greyed out, why?
rsOtherSubCatagories.Source = "SELECT * FROM SubCatagories WHERE SubCatagoryID = " + Replace(rsSubCatagories__MMColParam, "'", "''") AND CatagoryID = " + Replace(rsOtherSubCatagories__MMColParam, "'", "''")
Thanks Joe
Hey Joe,
you're missing some( + " ), try this:
rsOtherSubCatagories.Source = "SELECT * FROM SubCatagories WHERE SubCatagoryID = " + Replace(rsSubCatagories__MMColParam, "'", "''") + " AND CatagoryID = " + Replace(rsOtherSubCatagories__MMColParam, "'", "''")
|||Hikpeguero,
thanks very much for answering, that has stopped the error message but it isnt actually working though...
Im trying to test and see if the current record set is a subcategory in the database, the record im testing is, but it isnt being picked up as one.
This is the basic structure of the table "SubCatagories" im running the query on:
|SubCatagoryID|CatagoryID|"CatagoryName"|
|241|0|"Cat Types"|
|242|0|"Dog Types"|
|243|0|"Fish Types"|
|244|0|"Insect Types"|
|427|243|"Anglers"|
|428|243|"Anthias"|
|429|243|"Basslets"|
The values im trying to check above equate to the query string passed in the URL: page.asp?SubcatagoryID=428
If the current Query string is "428" (Anglers) then it is a subcategory of "243" (Fish Types) as the code loops through the records I need it to check if the query string "428" is associated with the SubCategoryID record being loped through at the time. It should check the Request.QueryString("SubCatagoryID") against the CategoryID of the other records for a match, When it gets to "243" (Fish Types) for example it should find that the current QueryString("SubcatagoryID") of 428 maches, because the CatagoryID in that record matches the (Fish Types) SubCatagoryID.
rsSubCatagories__MMColParam = 428 and rsOtherSubCatagories__MMColParam = 243 Thus the query:
rsOtherSubCatagories.Source = "SELECT * FROM SubCatagories WHERE SubCatagoryID = " + Replace(rsSubCatagories__MMColParam, "'", "''") + " AND CatagoryID = " + Replace(rsOtherSubCatagories__MMColParam, "'", "''")
What is the best way to test for this, should I open a new database connection and run a query for each record? And if so how would I construct the query to check this?
Thanks for your help, Joe
Hi Guys,
The way I understand a Data Flow Task is that it inserts the rows from the source to destination one by one. Is there a way to make it act like a bulk insert task? We have been experiencing performance issues when inserting a lot of rows from one table to another. If there's no way to actually do it, can a bulk insert task functionality be scripted? Coz what I need is a table to table insert, and the bulk insert task only accepts data files as sources.
Thanks!
Kervy
What destination object are you using? The OLEDB Destination and the SQL Server destination both enable bulk insert options for SQL Server.
How many rows are you inserting?
Donald
|||Oh you mean to say, by default, an OLEDB source inserts to an OLEDB Destination in bulk? We are only inserting a couple of hundred thousand rows, but the source table is in the US. destinations are in Europe and Asia Pacific, so we need to optimize this as much as we can given the proximity of the servers.
We are using OLEDB source and OLEDB Destination
Thanks
|||If you need to performance tune your data-flow then you should read a whitepaper that I have mentioned here: http://blogs.conchango.com/jamiethomson/archive/2006/04/09/3594.aspx
There's also more useful information here: http://blogs.conchango.com/jamiethomson/archive/2005/04/20/1319.aspx
Note that SSIS does not insert records one at a time - that would be a performance killer. The SSIS pipeline processes data in buffers and the contents of each buffer is inserted at the same time! A large part of performance tuning your application is finding the optimum buffer size.
Note that in your scenario the bottleneck is likely to be the georgraphic disparity of the source and destination, not how fast SSIS can process it. Try and reduce the amount of data that is coming over the wire if possible - i.e. Filter the data at source and only select columns that you require.
-Jamie
|||hi Jamie,
Thanks for that response. I will read more on how to performance tune my data flow. One accepted cause for the horrible execution time is the distance between servers (destinations are in 2 different continents!), that is why we are trying to figure out if there are other ways to improve on this. We tried testing with a destination server in the same area, and it took about 9 minutes.. compared to the 16 hours it took when the target was Asia Pacific.. we may have to accept that performance issue for now..
total number of rows to be inserted = 200,000
total number of columns = 167
|||KervyChoa wrote:
hi Jamie,
Thanks for that response. I will read more on how to performance tune my data flow. One accepted cause for the horrible execution time is the distance between servers (destinations are in 2 different continents!), that is why we are trying to figure out if there are other ways to improve on this. We tried testing with a destination server in the same area, and it took about 9 minutes.. compared to the 16 hours it took when the target was Asia Pacific.. we may have to accept that performance issue for now..
total number of rows to be inserted = 200,000
total number of columns = 167
Exactly, hence my previous comment "Try and reduce the amount of data that is coming over the wire if possible - i.e. Filter the data at source and only select columns that you require."
The point here is to reduce the amount of data coming over the wire because that is quite obviously the bottleneck in your scenario.
-Jamie
|||I tried using perfmon to see what can be done, but I can't find the SQL Server: SSIS Pipeline Performance object in perfmon.. Am I missing something here?|||I tried using perfmon to see what can be done, but I can't find the SQL Server: SSIS Pipeline Performance object in perfmon.. Am I missing something here?
As we have already determined (twice) the bottleneck is not within the SSIS pipeline so looking at this perf counter is not going to help you.
Unless you pick one of the machines up and move it to a different continent, or improve the speed of the link between them, you're not going to see any eprformance improvement.
-Jamie
