Can't Seem to Figure Out Why I See What I Do -- Should Be EASY

Let's say my customer dimension is similar to the following:

Customer_key | CustName | AcctOwner
--
1 | ABC Company | Joe Cool
1 | ABC Company | John Doe

* My customer dimension is a Named Query in the DSV where I'm doing a LEFT OUTER JOIN to generate the AcctOwner

Now let's say my Sales Fact table looks like the following:

Customer_key | Product_key | InvoiceAmt
-
1 | 1 | 5.00
1 | 2 | 2.75
1 | 3 | 0.25

Now if my pivot simply contains CustName, AcctOwner and InvoiceAmt, shouldn't I see the following:

CustName | AcctOwner | InvoiceAmt
--
ABC Company | Joe Cool | 8.00
ABC Company | John Doe | 8.00

However, I only see one line and I cannot figure out why I see the line that I do in the cube; can't seem to figure out why it's showing the Account Owner that it's showing.

Surely I'm having a brain fart and it's something really stupid where I'll be embarrassed. Please let me know if additional information is needed.

It's an AS2005 SP2 cube.

It's because your customer keys are not unique. When SSAS builds the dimension it will collect a unique list of the keys, then it will generate the CustName and AcctOwner attributes, with logic Equivalent to:

UDPATE <dimension> SET AcctOwner = dimCustomer.AcctOwner WHERE Customer_Key = 1

I suspect that what you have here is that AcctOwner should not be an attribute of the customer, but rather is a many to many relationship where one account owner can have many customers and one customer can have many account owners.

If this is the case you need to create a bridge table with the account owner and customer relationship and set this up as a measure group which you can use in a many-to-many relationship. There is information in BOL on many-to-many relationships, but there is also an extensive whitepaper available here: http://www.sqlbi.eu/Projects/Manytomanydimensionalmodeling/tabid/80/Default.aspx

|||

Darren Gosbell wrote:

It's because your customer keys are not unique. When SSAS builds the dimension it will collect a unique list of the keys, then it will generate the CustName and AcctOwner attributes, with logic Equivalent to:

UDPATE <dimension> SET AcctOwner = dimCustomer.AcctOwner WHERE Customer_Key = 1

I suspect that what you have here is that AcctOwner should not be an attribute of the customer, but rather is a many to many relationship where one account owner can have many customers and one customer can have many account owners.

If this is the case you need to create a bridge table with the account owner and customer relationship and set this up as a measure group which you can use in a many-to-many relationship. There is information in BOL on many-to-many relationships, but there is also an extensive whitepaper available here: http://www.sqlbi.eu/Projects/Manytomanydimensionalmodeling/tabid/80/Default.aspx

Darren,

Thanks for the input. It should NOT be a many-to-many relationship, but there are currently a few cases of bad data (from the source CRM system) where a Customer has more than one Account Owner. Once the data cleanup is performed, it will be a one-to-one relationship.

With that said, I was just testing this attribute (i.e., Account Owner) and came across the above scenario and became baffled on what I saw. I had expected both Account Owners to populate in the pivot.

Any additional thoughts?

|||

If you data is dirty you either need to clean it before loading it, or adjust your design to allow it to display. You could build a dimension table that uses an identity column to generate a unique key for each row, but

You will not see both account owners, because for a given Customer_key value there can only be one Account Owner value, so usually what happens is that the last in wins. Essentially in the scenario you have outlined, the dimension processing updates the Account Owner twice, once to set it to the first value "Joe Cool" and the "Joe Cool" gets overwritten with the value "John Doe".

The relationship between the key of an dimension and the other attributes can either be a one to one or a one to many where many keys can map to a single attribute value, but a single key CANNOT map to more than on attribute value.

Can't save view - linked server

Our customer has an external SQL server on which they want to make a
lookup into a table/view.
I have setup a Linked server and retrieve data from the linked server.
Example:
EXEC sp_addlinkedserver
@.server = 'NPRSQL',
@.srvproduct = 'SQLServer OLEDB Provider',
@.provider = 'SQLOLEDB',
@.datasrc = 'SQL01'
Go
SELECT *
FROM NPRSQL.DTS.dbo.ItemView
However I can "create" the view, get the data, but when I try to save
the view I get the following error:
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The
operation could
not be performed because the OLE DB provider 'SQLOLEDB' was unable to
begin a
distributed transaction.[Microsoft][ODBC SQL Server Driver][SQL
Server]
[OLE/DB
provider returned message: New transaction cannot enlist in the
specified transaction
coordinator.]
How can it be that I can get the data but not save the view?
I have tried to make a similarly scenario on the Northwind database,
create a view on the linked server and the "call" this view via a view
on my local server with the same result.
/refdkHi
see this link
http://support.microsoft.com/kb/839279
try this first and let us know
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"refdk" <fuhlendorf@.gmail.com> wrote in message
news:1181027684.578393.61140@.o5g2000hsb.googlegroups.com...
> Our customer has an external SQL server on which they want to make a
> lookup into a table/view.
> I have setup a Linked server and retrieve data from the linked server.
> Example:
> EXEC sp_addlinkedserver
> @.server = 'NPRSQL',
> @.srvproduct = 'SQLServer OLEDB Provider',
> @.provider = 'SQLOLEDB',
> @.datasrc = 'SQL01'
> Go
> SELECT *
> FROM NPRSQL.DTS.dbo.ItemView
> However I can "create" the view, get the data, but when I try to save
> the view I get the following error:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The
> operation could
> not be performed because the OLE DB provider 'SQLOLEDB' was unable to
> begin a
> distributed transaction.[Microsoft][ODBC SQL Server Driver][SQ
L
> Server]
> [OLE/DB
> provider returned message: New transaction cannot enlist in the
> specified transaction
> coordinator.]
> How can it be that I can get the data but not save the view?
> I have tried to make a similarly scenario on the Northwind database,
> create a view on the linked server and the "call" this view via a view
> on my local server with the same result.
>
> /refdk
>|||I had already done most of the stuff in the kb-issue. However, I have
now completed every step but the error persists.
/refdk|||Hi
Can you post the view statement
Regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"refdk" <fuhlendorf@.gmail.com> wrote in message
news:1181109437.668792.166410@.q75g2000hsh.googlegroups.com...
>I had already done most of the stuff in the kb-issue. However, I have
> now completed every step but the error persists.
> /refdk
>|||SELECT dbo.Hornsh=F8j$Item.No_ AS [No], dbo.Hornsh=F8j
$Item.Description, dbo.Hornsh=F8j$Item.[Unit Price], SUM(dbo.[Hornsh
=F8j
$Item Ledger Entry].Quantity)
AS Quantity
FROM dbo.Hornsh=F8j$Item INNER JOIN
dbo.[Hornsh=F8j$Item Ledger Entry] ON dbo.Hornsh=F8j
$Item.No_ =3D dbo.[Hornsh=F8j$Item Ledger Entry].[Item No_]
GROUP BY dbo.Hornsh=F8j$Item.No_, dbo.Hornsh=F8j$Item.Description,
dbo.Hornsh=F8j$Item.[Unit Price]|||> How can it be that I can get the data but not save the view?
Are you trying to create the view inside of a transaction? Why is it going
through ODBC/OLEDB? Shouldn't you be creating the view in a query window
connected to that server, instead of trying to create the view through your
app via the linked server? (Well, that's how I would do it, anyway.)

Can't save view - linked server

Our customer has an external SQL server on which they want to make a
lookup into a table/view.
I have setup a Linked server and retrieve data from the linked server.
Example:
EXEC sp_addlinkedserver
@.server = 'NPRSQL',
@.srvproduct = 'SQLServer OLEDB Provider',
@.provider = 'SQLOLEDB',
@.datasrc = 'SQL01'
Go
SELECT *
FROM NPRSQL.DTS.dbo.ItemView
However I can "create" the view, get the data, but when I try to save
the view I get the following error:
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The
operation could
not be performed because the OLE DB provider 'SQLOLEDB' was unable to
begin a
distributed transaction.[Microsoft][ODBC SQL Server Driver][SQL
Server]
[OLE/DB
provider returned message: New transaction cannot enlist in the
specified transaction
coordinator.]
How can it be that I can get the data but not save the view?
I have tried to make a similarly scenario on the Northwind database,
create a view on the linked server and the "call" this view via a view
on my local server with the same result.
/refdkHi
see this link
http://support.microsoft.com/kb/839279
try this first and let us know
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"refdk" <fuhlendorf@.gmail.com> wrote in message
news:1181027684.578393.61140@.o5g2000hsb.googlegroups.com...
> Our customer has an external SQL server on which they want to make a
> lookup into a table/view.
> I have setup a Linked server and retrieve data from the linked server.
> Example:
> EXEC sp_addlinkedserver
> @.server = 'NPRSQL',
> @.srvproduct = 'SQLServer OLEDB Provider',
> @.provider = 'SQLOLEDB',
> @.datasrc = 'SQL01'
> Go
> SELECT *
> FROM NPRSQL.DTS.dbo.ItemView
> However I can "create" the view, get the data, but when I try to save
> the view I get the following error:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The
> operation could
> not be performed because the OLE DB provider 'SQLOLEDB' was unable to
> begin a
> distributed transaction.[Microsoft][ODBC SQL Server Driver][SQL
> Server]
> [OLE/DB
> provider returned message: New transaction cannot enlist in the
> specified transaction
> coordinator.]
> How can it be that I can get the data but not save the view?
> I have tried to make a similarly scenario on the Northwind database,
> create a view on the linked server and the "call" this view via a view
> on my local server with the same result.
>
> /refdk
>|||I had already done most of the stuff in the kb-issue. However, I have
now completed every step but the error persists.
/refdk|||Hi
Can you post the view statement
Regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"refdk" <fuhlendorf@.gmail.com> wrote in message
news:1181109437.668792.166410@.q75g2000hsh.googlegroups.com...
>I had already done most of the stuff in the kb-issue. However, I have
> now completed every step but the error persists.
> /refdk
>|||SELECT dbo.Hornsh=F8j$Item.No_ AS [No], dbo.Hornsh=F8j
$Item.Description, dbo.Hornsh=F8j$Item.[Unit Price], SUM(dbo.[Hornsh=F8j
$Item Ledger Entry].Quantity)
AS Quantity
FROM dbo.Hornsh=F8j$Item INNER JOIN
dbo.[Hornsh=F8j$Item Ledger Entry] ON dbo.Hornsh=F8j
$Item.No_ =3D dbo.[Hornsh=F8j$Item Ledger Entry].[Item No_]
GROUP BY dbo.Hornsh=F8j$Item.No_, dbo.Hornsh=F8j$Item.Description,
dbo.Hornsh=F8j$Item.[Unit Price]|||> How can it be that I can get the data but not save the view?
Are you trying to create the view inside of a transaction? Why is it going
through ODBC/OLEDB? Shouldn't you be creating the view in a query window
connected to that server, instead of trying to create the view through your
app via the linked server? (Well, that's how I would do it, anyway.)

Can't save view - linked server

Our customer has an external SQL server on which they want to make a
lookup into a table/view.
I have setup a Linked server and retrieve data from the linked server.
Example:
EXEC sp_addlinkedserver
@.server = 'NPRSQL',
@.srvproduct = 'SQLServer OLEDB Provider',
@.provider = 'SQLOLEDB',
@.datasrc = 'SQL01'
Go
SELECT *
FROM NPRSQL.DTS.dbo.ItemView
However I can "create" the view, get the data, but when I try to save
the view I get the following error:
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The
operation could
not be performed because the OLE DB provider 'SQLOLEDB' was unable to
begin a
distributed transaction.[Microsoft][ODBC SQL Server Driver][SQL
Server]
[OLE/DB
provider returned message: New transaction cannot enlist in the
specified transaction
coordinator.]
How can it be that I can get the data but not save the view?
I have tried to make a similarly scenario on the Northwind database,
create a view on the linked server and the "call" this view via a view
on my local server with the same result.
/refdk
I had already done most of the stuff in the kb-issue. However, I have
now completed every step but the error persists.
/refdk
|||SELECT dbo.Hornshj$Item.No_ AS [No], dbo.Hornshj
$Item.Description, dbo.Hornshj$Item.[Unit Price], SUM(dbo.[Hornshj
$Item Ledger Entry].Quantity)
AS Quantity
FROM dbo.Hornshj$Item INNER JOIN
dbo.[Hornshj$Item Ledger Entry] ON dbo.Hornshj
$Item.No_ = dbo.[Hornshj$Item Ledger Entry].[Item No_]
GROUP BY dbo.Hornshj$Item.No_, dbo.Hornshj$Item.Description,
dbo.Hornshj$Item.[Unit Price]
|||> How can it be that I can get the data but not save the view?
Are you trying to create the view inside of a transaction? Why is it going
through ODBC/OLEDB? Shouldn't you be creating the view in a query window
connected to that server, instead of trying to create the view through your
app via the linked server? (Well, that's how I would do it, anyway.)