duplicate values

hey guys,

ive run into an issue and im not quite sure how to fix it.. let me explain

i have a cube, by market and i wish to show sales and freight costs.

The freight Fact table has no relationship to the market dimension as freigth is charged on customer order level, not item level.

I have data showing like the following:

Year

2006

Market

Sales

Freight Amount

Auto OEM

$7,115,315.22

$56,620.73

Auto Repl

$18,517,004.07

$56,620.73

Intercompany

$4,772,910.37

$56,620.73

Ind OEM

$12,039.74

$56,620.73

Ind Repl

$18,679,313.71

$56,620.73

Other

$84,263.19

$56,620.73

Grand Total

$49,180,846.30

$56,620.73

I would only like the freigth amount to show at the Grand Total Level. Is this possible? or, if not, is there a way you can combine two dimensions into 1? ie, combine a Freight Account dimemsion and the Market Dimension so that i can see Markets, then the different Freight Accounts by rows?

Thanks in advance,

Scotty

If you look in the cube structure tab in BIDS, you will see that each measure group has a property called IgnoreUnrelatedDimensions, this defaults to true which means that when you slice a measure by an unrelated measure you see the total amount (which is the behaviour you are seeing). Changing this property to false will mean that the measures will only display when they are either sliced by a related dimension or at the highest level.

It's hard to be sure, but from what you have said I don't think combining the two dimension would work.

Duplicate Record

Hi guys how do you hide duplicate records, how would I do a select statement for that
In (SELECT [AccountNo] FROM [2006_CheckHistory] As Tmp GROUP BY [AccountNo] HAVING Count(*)>1 )
I have about had it with this database I have been asked to make a report out ofwhat do you want to do ?

Lists only unique AccountNo ?
select distinct AccountNo from [2006_CheckHistory]|||i personally don't "hide" duplicate rows, i prevent them from happening|||so there was nothing I could do about the duplicates, they are making the query difficult to create, and this thing is driving me crazy. Some took data and just threw it into a database and called it a day (was a VB programmer)

what do you want to do ?

Lists only unique AccountNo ?
select distinct AccountNo from [2006_CheckHistory]

I'm trying to make the AccountNo unique, you dont need it entered more then once in a table.|||I want to delete them I just want to create a query that shows me all the duplicates and then delete them, leaving only one AccountNo instead of several. Does that make sense??|||okay, you want one row per AccountNo

what about the other columns? which values would you like from those?

here's an example of the problem you could be facing --
AccountNo Name Age City
1234 Fred 23 Chicago
1234 Fred 23 Waukegan
1234 Fred 26 Chicago
1234 Todd 23 Chicago
5678 Mary 16 Milwaukee
5678 Mary 15 Milwaukee
5678 Mary 17 Milwaukeenow how are we supposed to know which row to keep?|||Yeah, the problem that I need to work around is when I have it as a report I cant create a total per AccountNo it just totals it for all the records rather then per account... like if there is an account for number 000111222 then I cant get the total amount for just that record. I need one record to show and its total for that year and this is such a BIG PAIN. I didnt create this database so its a matter of some serious fixing. The problem is gettign this done before the 22nd of January...ACCCKKKK HELP PLEASE!!!!!!!!!!!!!!

Then as soon as I'm done I'm going to do some serious restructuring|||It seems that you are talking about one table, but think about those tables which are related with this one.

Whatever you plan, plan considering all related tables in the database.|||Take your offending table, and run this query against just that table (no, joins, no fancy stuff, just this query alone):SELECT Count(AccountNo), Count(DISTINCT AccountNo)
FROM OffendingTable-PatP|||Oh yeah, while you are at it could you explain what you consider to be a "duplicate record" ? Does that mean that all columns in more than one row are identical? Does it mean that one column (AccontNo) in more than one row are identical? Does it mean something completely different that I haven't thought of yet?

-PatP|||Thank you so much guys,

It means that there is identical [AccountNo] in the table, these are businesses, now when the business changes ownership they indicate that with a letter like A, B..etc, or if theres equipment at that business thats indicated with a letter. I was thinking of making the AccountNo Unique by getting rid of the duplicates and making it unique. And having a seperate table with the A, B and the date they changed hands linked to the AccountNo table, since this is table would reflect changes to the [AccountNo] table. The [AccountNo] table would have the information concering the business, like address, name of business..etc. Does that make sense??|||Only problem is what about the other columns...which values do you want? Does it matter to you? It usually does.

Post the DDL of the table and some sample data, and what you want in the final table...

And what are the report req's?|||Only problem is what about the other columns...which values do you want? Does it matter to you? It usually does.

Post the DDL of the table and some sample data, and what you want in the final table...

And what are the report req's?

OwnerManagerID AccountNo CheckDate CheckNumber CheckAmount OwnerAddress1 NameFor1099 OwnerCity OwnerState OwnerZip
A 00010002 2006-09-20 37065 $621.75 11070 BLAH-DE-BLAH FOL-DER-OL EUREKA KS 90120-1234
A 00010002 2006-03-20 19006 $679.25 11070 BLAH-DE-BLAH FOL-DER-OL EUREKA KS 90120-1234
A 00010002 2006-10-23 38341 $587.13 11070 BLAH-DE-BLAH FOL-DER-OL EUREKA KS 90120-1234
A 00010002 2006-02-16 28548 $723.38 11070 BLAH-DE-BLAH FOL-DER-OL EUREKA KS 90120-1234
A 00010002 2006-06-20 33119 $994.00 11070 BLAH-DE-BLAH FOL-DER-OL EUREKA KS 90120-1234
A 00010002 2006-08-23 35806 $578.75 11070 BLAH-DE-BLAH FOL-DER-OL EUREKA KS 90120-1234
A 00010002 2006-07-24 34551 $488.50 11070 BLAH-DE-BLAH FOL-DER-OL EUREKA KS 90120-1234
A 00010002 2006-05-18 31820 $573.50 11070 BLAH-DE-BLAH FOL-DER-OL EUREKA KS 90120-1234
A 00010002 2006-04-21 30734 $582.88 11070 BLAH-DE-BLAH FOL-DER-OL EUREKA KS 90120-1234
A 00010002 2006-12-20 40892 $614.00 11070 BLAH-DE-BLAH FOL-DER-OL EUREKA KS 90120-1234
A 00010002 2006-01-17 24366 $427.75 11070 BLAH-DE-BLAH FOL-DER-OL EUREKA KS 90120-1234
A 00010002 2006-11-17 39614 $730.00 11070 BLAH-DE-BLAH FOL-DER-OL EUREKA KS 90120-1234
A 00010004 2006-06-22 23676 $1,972.79 123 SESAME STREET #937 LUXE SUITES DRY GULCH TX 90937-0937
A 00010004 2006-02-09 23644 $1,737.02 123 SESAME STREET #937 LUXE SUITES DRY GULCH TX 90937-0937
A 00010004 2006-03-10 23660 $1,632.50 123 SESAME STREET #937 LUXE SUITES DRY GULCH TX 90937-0937
A 00010004 2006-05-31 32492 $1,586.44 123 SESAME STREET #937 LUXE SUITES DRY GULCH TX 90937-0937
A 00010004 2006-09-12 36608 $1,778.60 123 SESAME STREET #937 LUXE SUITES DRY GULCH TX 90937-0937
A 00010004 2006-07-20 34540 $1,715.68 123 SESAME STREET #937 LUXE SUITES DRY GULCH TX 90937-0937
A 00010004 2006-04-11 30257 $1,951.40 123 SESAME STREET #937 LUXE SUITES DRY GULCH TX 90937-0937
A 00010004 2006-08-07 35355 $1,362.58 123 SESAME STREET #937 LUXE SUITES DRY GULCH TX 90937-0937
A 00010004 2006-01-06 25876 $1,834.49
A 00010004 2006-12-06 40426 $1,647.72 123 SESAME STREET #937 LUXE SUITES DRY GULCH TX 90937-0937
A 00010004 2006-11-13 39605 $1,795.44 123 SESAME STREET #937 LUXE SUITES DRY GULCH TX 90937-0937
A 00010004 2006-11-06 39164 $1,524.11 123 SESAME STREET #937 LUXE SUITES DRY GULCH TX 90937-0937|||I want in the ownermanager_table Ownermanagerid, Address, city, state zip..ect, AccountNo, fedtaxid.

That should be one table and the other table should be the check amount, checkdate, name of bank..etc|||sorry, that looked like really private information so i obfuscated it|||Thank you appreciate it|||WOOHOO I GOT THE QUERY I NEEDED ITS OVER THIS HORRIBLE NIGHTMARE IS OVER....WOOOHOOOOOOOO!!!!!!!!!!!!!!!!!!!

I'M SO HAPPY I COULD SCREAM...Good grief this person just took live DATA and stuck in a table and just called it a day, no documentation no nothing. The whole thing is structured so badly its a nightmare...EEEEEEEEEEEEEEEEEE!!!!!!!!!!!!!!!!!!!! !!!!!!!!!!

Thank you guys

Duplicate key ignored !

Hi guys,

i have the following tables:

Table1:
[PKID] WITH INDEX "IGNORE DUPLICATE KEY"

Table2:
[FKID]

and i have the following SQL statement:

insert into Table1
select distinct FKID
from Table2
where FKID not in (select PKID from Table1)

The above SQL statement is in a DTS package which raises "Duplicate key ignored" error... i can't see how that can happen since i am already checking the key if it already exists. ..

Please help !!!

TNT :sDuplicates are probably in Table2.

Not that this has ever happened to me, but I read about it in this book once. ;)

-PatP|||Thanks for replying pat. :)

Duplicates are not selected from table2 since the 'distinct' keyword is used.

Very wierd problem... i don't have much hair left to pull out :s

T.|||Without having sample tables and data, it is tough (near impossible) to eliminate all of the possibilities. My guess is that you have non-duplicate values in table2 that become duplicates in table1 (this often happens due to type changes, etc).

If you can post the table definitions and some sample data, I'm certain that one of us can figure out your problem... Without them, all we can do is guess... Your description of the problem probably won't help us much.

-PatP|||Thanks for your response.
i will post some data when the problem occurs again.

Thanks again.
TNTsql

Duplex report

Hello guys,
Well, nobody could help me and after many trys to produce my two sided
pages report, I cannot succeed. I think now that it's impossible to
produce a report having two sides of each page with different printing
logic. Am I right ?
Jean-MarcWhy would the opposite side be different? I am not sure I am following you
here. It has been my experience that anything is possible. It however may
not be practical. can you describe in detail what you are attempting to do?
"jean-marc.audrin@.future.ca" wrote:
> Hello guys,
> Well, nobody could help me and after many trys to produce my two sided
> pages report, I cannot succeed. I think now that it's impossible to
> produce a report having two sides of each page with different printing
> logic. Am I right ?
> Jean-Marc
>

DTS Transaction Trouble

Hi Guys,

I have a DTS Routine which archives a large amount of data into archive tables in the same database(Personally I would rather archive into a separate db but the paperwork for the customer involved in doing this is ridiculous).

The routine seems to archive all tables except for one (the largest one) However if I split the routine and run the rest of it first then the problem table then the table archives ok. To me it smells of transaction size or something but does anyone have any other ideas.. If it is transaction size, is there a way that I can break the routine down into multiple transactions?

Any help is as always greatly appreciated.

Cheers,

Chris
--------------------
If we were meant to count in Hex, why do we only have X fingers?
--------------------if it is the same database, I have to ask why DTS and why not just a INSERT INTO? How big is the table?|||My reason for using DTS is that I needed to do some transformation of the data along the way which I did using vbscript, I had originally intended to implement the routine as an SQL script.

Chris|||do you have an error message? have you checked the log? there is not enough in your post. I am having a hard time grasping what could have been so hard as to nmot have done it in SQL. Read Brett's sticky up top.|||There is no error message, the routine simply hangs when it gets to the transform for one of the tables.

If i disable this step then the routine runs through without problem.

I originally created the routine using DTS because whilst currently the archive tables are in the same schema this won't be the case once the routine goes live. The live routine will most likely be archiving to a mirror database on a different server. Also I will want to run the routine as a job.

I will post the log results here however does anyone have an answer to my original question asking if there was a way to break a DTS routine into multiple transactions?

Thanks,

Chris|||you have a solution in mind without having truly diagnosed the problem. DTS is a toy I avoid because it is full of issues. Even when it goes to another server you could still write it in sql and run it as a job over linked servers.|||You are putting the cart before the horse a little here.

Apart from this issue I have already built the DTS routine so I am not in a position to easily start again so I am looking for the solution to the problem rather than settling on a solution of starting again.

I understand your thoughts on DTS having developed solutions using the tool and other solutions using pure SQL scripts. However I have a number of customers that run similar routines on a nightly basis without issue.

So before I resort to starting again I want to investigate resolving the immediate problem.

Regards,

Chris

DTS to do SQL to MS Access

Okay guys, maybe I'm making this much more complex than it needs to be. I've got a SQL DB and want to export certain tables to Access. My first thought was DTS, right? So I started down the road of the DTS - build the DTS on my local SQL box, made the Access DB, and everything was good.

Then I thought about my deployment environment: there are several clients that will use this. Some will have their own environment, so no problem. Yet others will host at an ISP, which typically means that the SQL box only has TCP/IP access to the webserver, so where do you tell the DTS package to put the Access DB? Then I thought about using the FTP task, but this blasted thing only gives you a mapped network location to put the resulting file. DAMN! (Now why wouldn't you have the ability to put an FTP ADDRESS AS THE DESTINATION OF THE FREAKING FTP TASK - I suppose that would make too much f-ing sense!)

So here I am, at a loss for what to do. I thought about coding the transformation in .NET, but what a major pain in the ****! Then maybe thought about using DTS from within C#, but can't find any solid examples of doing this.

HELP!!!Hi Rob

Maybe this will get you on the right track. http://www.sqlteam.com/item.asp?ItemID=12408

If not, there are a couple of other FTP and DTS articles on SQLTeam|||Okay, I've worked up a little C# routine to actually execute the DTS packages and it gets so far, but then get an error that it doesn't like the userID because it's null (although I am passing the userID into the DTS execution). I've got to be missing something pretty simple here - any ideas?

Here's the C# code that calls the DTS package:

DTS.Package2Class package = new DTS.Package2Class();
object pVarPersistStgOfHost = null;
package.LoadFromSQLServer("sqlmachinenamehere", "sa", "passwordhere", DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrustedConnection.DTSSQLStgFlag_Default, null, null, null, "Export_Association_Table", ref pVarPersistStgOfHost);

And the error that is produced:

Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.