Logical Keys and DVS

All,

I was trying to understand the perpose of putting logical Keys on the DSV, I have read articles and book saying that we should use/set them, but since they don't create the Keys at teh database level I was wondering how this helps? Is this just to aide the various Wizards?

Regards,

Yes, they are mainly to help the various wizards. It helps the dimension wizard identify dimesion keys and the cube wizard can use them to help identify the roles (fact/dimension) that the particular tables are playing. Strictly speaking you can get away without using them and set the relevant properties in the dimensions and cubes yourself. These keys have no effect on performance.

Logic Building Problem

Hi

I am having some problem with the logic building block of the following sproc. While using the Stock column, it raises an error saying invalid column.

Code Snippet

ALTER PROCEDURE [dbo].[usp_Issue]

@.ProductIDint,

@.IssueQtyint

AS

BEGIN

SELECTStock= ( ISNULL (SUM(R.ActualQty), 0) - ISNULL (SUM(I.IssueQty), 0))

FROM Products P

LEFT JOIN Receives R

ON P.ProductID = R.ProductID

LEFT JOIN Issues I

ON P.ProductID = I.ProductID

WHERE P.ProductID = @.ProductID

GROUP BY P.ProductID

IF Stock IS NULL

-- Error 'Invalid Product'

ELSE IF Stock < @.IssueQty

-- Error 'Not enough stock'

ELSE

-- INSER INTO ISSUES..

END

Anyone please help me writing the logical block.

Regards

Kapalic

You have to assign the value in the varaible for logical validation..

Code Snippet

ALTER PROCEDURE [dbo].[usp_Issue]

@.ProductID int,

@.IssueQty int

AS

BEGIN

Declare @.Stock as Int;

SELECT @.Stock = ( ISNULL (SUM(R.ActualQty), 0) - ISNULL (SUM(I.IssueQty), 0))

FROM Products P

LEFT JOIN Receives R

ON P.ProductID = R.ProductID

LEFT JOIN Issues I

ON P.ProductID = I.ProductID

WHERE P.ProductID = @.ProductID

GROUP BY P.ProductID

IF @.Stock IS NULL

-- Error 'Invalid Product'

Raiserror 'Invalid Product', 16, 1

ELSE IF @.Stock < @.IssueQty

-- Error 'Not enough stock'

Raiserror 'Not Enough Stock', 16, 1

ELSE

-- INSER INTO ISSUES..

END