November 21, 2011 at 7:28 am
Can you not bring in a field that has duplicate values when you don't include the key field?
For example, I have a field which holds invoice collection notes. When you do a count just on the invoice collection notes, there are duplicates. However, when you do a count on the invoice collection notes and the invoice key, there aren't any duplcates.
select InvoiceCollectionNotes, COUNT(*) AS "total"
into ##Count3
from Invoice_Fact
group by InvoiceCollectionNotes
select * from ##Count3 where total > 1
Results:
Invoice Collection Notes
pmt to be rec'd 3/9/11 6
11/3/11 - flipped in Xign, pay in 30 days SBM 2
select SourceInvoiceKey, InvoiceCollectionNotes, COUNT(*) AS "total"
into ##Count2
from Invoice_Fact
group by SourceInvoiceKey,InvoiceCollectionNotes
select * from ##Count2 where total > 1
Results: 0
November 22, 2011 at 7:50 am
I think more information is needed, but as a general rule, you would not want to import straight duplicates.
November 22, 2011 at 7:59 am
These are comments on particular invoices and the same comments can be put on multiple invoices. so if you just look at the comments, there are duplicates. But if you look at the invoice key and the comments there aren't any duplicates.
Am I understanding this correctly that because one field has duplicates without looking at any other field, that field can't be brought into analysis services?
November 22, 2011 at 8:06 am
Yes, this would be ok. In fact, it would be desireable, assuming the comments could be made into an attribute.
November 22, 2011 at 8:11 am
then i'm not sure what i'm doing wrong. I bring the field in but when i go to process the cube it tells me it can't because there are duplicates in the attribute field and it names the comments for the invoices as the problem field.
November 22, 2011 at 8:17 am
You can have the duplicates in the fact table, but not in the dimension table. Is that the issue?
November 22, 2011 at 8:29 am
the table is called invoice_fact but when i imported it into analysis services it shows up at invoice fact.dim. is there a way i can make this show up like invoice fact.fact?
November 22, 2011 at 8:48 am
Create a cube using it as a fact table. A table can be a dimension or fact table, though normally the relationship with other tables is the determing factor. A fact table essentially facilitates a many-to-many relationship between dimension tables. I'm telling you that because you may have design issues that are the bottom line issue here, and if so, you may need more help than I can provide in this forum.
November 22, 2011 at 9:31 am
Thank you for all the help. I realize what i need to do.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply