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.

Cant insert data through view

I am a database administrator at my company.
Users report that insert statements against the view named
Production.viewTest do not succeed. But i firmed that the insert
statements that are being used are valid.
The only table that the view uses is named Production.ExpiredProduct.
The table has the following definition:
CREATE TABLE Production.ExpiredProduct (
ExpiredProductID INT IDENTITY CONSTRAINT PK_EXPIREProduct PRIMARY
KEY,
Name NVARCHAR(50) NOT NULL,
ListPrice MONEY NULL)
How can i ensure that insert statement can be complete successfull by
using SQL Server Management Studio (SSMS).Any possible issue like
"Schema","Indexes", or "Statictis" caused this problem?Hi
What does not mean "does not work"? What is the error? Why would you want to
use SSMS rather than Query Builder?
<chaukimwai1978@.gmail.com> wrote in message
news:1177236011.207593.66000@.b58g2000hsg.googlegroups.com...
>I am a database administrator at my company.
> Users report that insert statements against the view named
> Production.viewTest do not succeed. But i firmed that the insert
> statements that are being used are valid.
> The only table that the view uses is named Production.ExpiredProduct.
> The table has the following definition:
> CREATE TABLE Production.ExpiredProduct (
> ExpiredProductID INT IDENTITY CONSTRAINT PK_EXPIREProduct PRIMARY
> KEY,
> Name NVARCHAR(50) NOT NULL,
> ListPrice MONEY NULL)
> How can i ensure that insert statement can be complete successfull by
> using SQL Server Management Studio (SSMS).Any possible issue like
> "Schema","Indexes", or "Statictis" caused this problem?
>|||> How can i ensure that insert statement can be complete successfull by
> using SQL Server Management Studio (SSMS).
You can't ensure the insert will succeed but you can test the insert by
running an insert from a query window. Assuming your view DDL is:
CREATE VIEW Production.viewTest
AS
SELECT
ExpiredProductID,
Name,
ListPrice
FROM Production.ExpiredProduct
GO
You can test with something like:
INSERT INTO Production.viewTest (Name, ListPrice)
VALUES('test', 1.0)
GO
The insert will fail if you try to specify an explicit identity value for
ExpiredProductID (unless you turn on IDENTITY_INSERT):
INSERT INTO Production.viewTest (ExpiredProductID, Name, ListPrice)
VALUES(1, 'test', 1.0)
--
Hope this helps.
Dan Guzman
SQL Server MVP
<chaukimwai1978@.gmail.com> wrote in message
news:1177236011.207593.66000@.b58g2000hsg.googlegroups.com...
>I am a database administrator at my company.
> Users report that insert statements against the view named
> Production.viewTest do not succeed. But i firmed that the insert
> statements that are being used are valid.
> The only table that the view uses is named Production.ExpiredProduct.
> The table has the following definition:
> CREATE TABLE Production.ExpiredProduct (
> ExpiredProductID INT IDENTITY CONSTRAINT PK_EXPIREProduct PRIMARY
> KEY,
> Name NVARCHAR(50) NOT NULL,
> ListPrice MONEY NULL)
> How can i ensure that insert statement can be complete successfull by
> using SQL Server Management Studio (SSMS).Any possible issue like
> "Schema","Indexes", or "Statictis" caused this problem?
>|||1) We need more information to assist you properly. At a minimum we would
need the view definition, the INSERT statement that fails as well as the
error reported after the failure.
2) From BOL:
Updatable Views
You can modify the data of an underlying base table through a view, as long
as the following conditions are true:
a.. Any modifications, including UPDATE, INSERT, and DELETE statements,
must reference columns from only one base table.
b.. The columns being modified in the view must directly reference the
underlying data in the table columns. The columns cannot be derived in any
other way, such as through the following:
a.. An aggregate function: AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV,
STDEVP, VAR, and VARP.
b.. A computation. The column cannot be computed from an expression that
uses other columns. Columns that are formed by using the set operators
UNION, UNION ALL, CROSSJOIN, EXCEPT, and INTERSECT amount to a computation
and are also not updatable.
c.. The columns being modified are not affected by GROUP BY, HAVING, or
DISTINCT clauses.
d.. TOP is not used anywhere in the select_statement of the view together
with the WITH CHECK OPTION clause.
INSTEAD OF Trigger is a possible alternative to attempting to directly aply
DML to a view.
--
TheSQLGuru
President
Indicium Resources, Inc.
<chaukimwai1978@.gmail.com> wrote in message
news:1177236011.207593.66000@.b58g2000hsg.googlegroups.com...
>I am a database administrator at my company.
> Users report that insert statements against the view named
> Production.viewTest do not succeed. But i firmed that the insert
> statements that are being used are valid.
> The only table that the view uses is named Production.ExpiredProduct.
> The table has the following definition:
> CREATE TABLE Production.ExpiredProduct (
> ExpiredProductID INT IDENTITY CONSTRAINT PK_EXPIREProduct PRIMARY
> KEY,
> Name NVARCHAR(50) NOT NULL,
> ListPrice MONEY NULL)
> How can i ensure that insert statement can be complete successfull by
> using SQL Server Management Studio (SSMS).Any possible issue like
> "Schema","Indexes", or "Statictis" caused this problem?
>

Cant insert data through view

I am a database administrator at my company.
Users report that insert statements against the view named
Production.viewTest do not succeed. But i firmed that the insert
statements that are being used are valid.
The only table that the view uses is named Production.ExpiredProduct.
The table has the following definition:
CREATE TABLE Production.ExpiredProduct (
ExpiredProductID INT IDENTITY CONSTRAINT PK_EXPIREProduct PRIMARY
KEY,
Name NVARCHAR(50) NOT NULL,
ListPrice MONEY NULL)
How can i ensure that insert statement can be complete successfull by
using SQL Server Management Studio (SSMS).Any possible issue like
"Schema","Indexes", or "Statictis" caused this problem?Hi
What does not mean "does not work"? What is the error? Why would you want to
use SSMS rather than Query Builder?
<chaukimwai1978@.gmail.com> wrote in message
news:1177236011.207593.66000@.b58g2000hsg.googlegroups.com...
>I am a database administrator at my company.
> Users report that insert statements against the view named
> Production.viewTest do not succeed. But i firmed that the insert
> statements that are being used are valid.
> The only table that the view uses is named Production.ExpiredProduct.
> The table has the following definition:
> CREATE TABLE Production.ExpiredProduct (
> ExpiredProductID INT IDENTITY CONSTRAINT PK_EXPIREProduct PRIMARY
> KEY,
> Name NVARCHAR(50) NOT NULL,
> ListPrice MONEY NULL)
> How can i ensure that insert statement can be complete successfull by
> using SQL Server Management Studio (SSMS).Any possible issue like
> "Schema","Indexes", or "Statictis" caused this problem?
>|||> How can i ensure that insert statement can be complete successfull by
> using SQL Server Management Studio (SSMS).
You can't ensure the insert will succeed but you can test the insert by
running an insert from a query window. Assuming your view DDL is:
CREATE VIEW Production.viewTest
AS
SELECT
ExpiredProductID,
Name,
ListPrice
FROM Production.ExpiredProduct
GO
You can test with something like:
INSERT INTO Production.viewTest (Name, ListPrice)
VALUES('test', 1.0)
GO
The insert will fail if you try to specify an explicit identity value for
ExpiredProductID (unless you turn on IDENTITY_INSERT):
INSERT INTO Production.viewTest (ExpiredProductID, Name, ListPrice)
VALUES(1, 'test', 1.0)
Hope this helps.
Dan Guzman
SQL Server MVP
<chaukimwai1978@.gmail.com> wrote in message
news:1177236011.207593.66000@.b58g2000hsg.googlegroups.com...
>I am a database administrator at my company.
> Users report that insert statements against the view named
> Production.viewTest do not succeed. But i firmed that the insert
> statements that are being used are valid.
> The only table that the view uses is named Production.ExpiredProduct.
> The table has the following definition:
> CREATE TABLE Production.ExpiredProduct (
> ExpiredProductID INT IDENTITY CONSTRAINT PK_EXPIREProduct PRIMARY
> KEY,
> Name NVARCHAR(50) NOT NULL,
> ListPrice MONEY NULL)
> How can i ensure that insert statement can be complete successfull by
> using SQL Server Management Studio (SSMS).Any possible issue like
> "Schema","Indexes", or "Statictis" caused this problem?
>|||1) We need more information to assist you properly. At a minimum we would
need the view definition, the INSERT statement that fails as well as the
error reported after the failure.
2) From BOL:
Updatable Views
You can modify the data of an underlying base table through a view, as long
as the following conditions are true:
a.. Any modifications, including UPDATE, INSERT, and DELETE statements,
must reference columns from only one base table.
b.. The columns being modified in the view must directly reference the
underlying data in the table columns. The columns cannot be derived in any
other way, such as through the following:
a.. An aggregate function: AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV,
STDEVP, VAR, and VARP.
b.. A computation. The column cannot be computed from an expression that
uses other columns. Columns that are formed by using the set operators
UNION, UNION ALL, CROSSJOIN, EXCEPT, and INTERSECT amount to a computation
and are also not updatable.
c.. The columns being modified are not affected by GROUP BY, HAVING, or
DISTINCT clauses.
d.. TOP is not used anywhere in the select_statement of the view together
with the WITH CHECK OPTION clause.
INSTEAD OF Trigger is a possible alternative to attempting to directly aply
DML to a view.
TheSQLGuru
President
Indicium Resources, Inc.
<chaukimwai1978@.gmail.com> wrote in message
news:1177236011.207593.66000@.b58g2000hsg.googlegroups.com...
>I am a database administrator at my company.
> Users report that insert statements against the view named
> Production.viewTest do not succeed. But i firmed that the insert
> statements that are being used are valid.
> The only table that the view uses is named Production.ExpiredProduct.
> The table has the following definition:
> CREATE TABLE Production.ExpiredProduct (
> ExpiredProductID INT IDENTITY CONSTRAINT PK_EXPIREProduct PRIMARY
> KEY,
> Name NVARCHAR(50) NOT NULL,
> ListPrice MONEY NULL)
> How can i ensure that insert statement can be complete successfull by
> using SQL Server Management Studio (SSMS).Any possible issue like
> "Schema","Indexes", or "Statictis" caused this problem?
>

can't get to report manager

My company recently upgraded to CRM 3.0 and i was trying to get to the report
manager page in my browser to make custom reports. Unfortunately, whenever i
try to go to the report manager page i get the error listed below. Has anyone
ever seen this before? Thanks in advance for any help you can give me.
Server Error in '/ReportMgr' Application.
----
Configuration Error
Description: An error occurred during the processing of a configuration file
required to service this request. Please review the specific error details
below and modify your configuration file appropriately.
Parser Error Message: Assembly microsoft.crm.platform.proxy.dll security
permission grant set is incompatible between appdomains.
Source Error:
Line 15: <add assembly="Microsoft.Crm.ObjectModel, Version=3.0.5300.0,
Culture=neutral, PublicKeyToken=31bf3856ad364e35" />
Line 16: <add assembly="Microsoft.Crm.Platform.ComProxy,
Version=3.0.5300.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />
Line 17: <add assembly="Microsoft.Crm.Platform.Proxy,
Version=3.0.5300.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />
Line 18: <add assembly="Microsoft.Crm.Platform.Server,
Version=3.0.5300.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />
Line 19: <add assembly="Microsoft.Crm.Platform.Sdk, Version=3.0.5300.0,
Culture=neutral, PublicKeyToken=31bf3856ad364e35" />
Source File: c:\inetpub\wwwroot\web.config Line: 17
----
Version Information: Microsoft .NET Framework Version:1.1.4322.2300; ASP.NET
Version:1.1.4322.2300did you solve this problem I am seeing something similar?
"ACreel" wrote:
> My company recently upgraded to CRM 3.0 and i was trying to get to the report
> manager page in my browser to make custom reports. Unfortunately, whenever i
> try to go to the report manager page i get the error listed below. Has anyone
> ever seen this before? Thanks in advance for any help you can give me.
> Server Error in '/ReportMgr' Application.
> ----
> Configuration Error
> Description: An error occurred during the processing of a configuration file
> required to service this request. Please review the specific error details
> below and modify your configuration file appropriately.
> Parser Error Message: Assembly microsoft.crm.platform.proxy.dll security
> permission grant set is incompatible between appdomains.
> Source Error:
>
> Line 15: <add assembly="Microsoft.Crm.ObjectModel, Version=3.0.5300.0,
> Culture=neutral, PublicKeyToken=31bf3856ad364e35" />
> Line 16: <add assembly="Microsoft.Crm.Platform.ComProxy,
> Version=3.0.5300.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />
> Line 17: <add assembly="Microsoft.Crm.Platform.Proxy,
> Version=3.0.5300.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />
> Line 18: <add assembly="Microsoft.Crm.Platform.Server,
> Version=3.0.5300.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />
> Line 19: <add assembly="Microsoft.Crm.Platform.Sdk, Version=3.0.5300.0,
> Culture=neutral, PublicKeyToken=31bf3856ad364e35" />
>
> Source File: c:\inetpub\wwwroot\web.config Line: 17
>
> ----
> Version Information: Microsoft .NET Framework Version:1.1.4322.2300; ASP.NET
> Version:1.1.4322.2300