SSAS 2005: How to I change logical primary key in data source view

[Vimas]

For some reason, when I add views to data source view in SSAS, some of them
have completely incorrect logical primary key. But I cannot find a way to
change that key. If table does not have logical primary key, I can select
fields and choose from menu "Set Logical Primary Key". But there is no
option to delete existing primary key.

Thanks

[Vimas]

Just to add more info:
I know that option "Delete logical primary key" is not available when SSAS
thinks that this PK is defined in SQL database. Problem is that I have a
view, not real table and index that SSAS creates based on database structure
is incorrect.
My view is somethink like this:

CREATE VIEW vwMyView AS
SELECT A.Acol1, A.Acol2, B.BCol1, B.Bcol2, C.CCol1, C.CCol2
FROM TableA A
JOIN TableB B ON B.BCol1 = A.BCol1
JOIN TableC C ON C.CCol1 = A.CCol1

PK that SSAS creates is:
A.Acol1, A.Acol2, B.BCol1, C.CCol1

ACol1, ACol2 is actuall PK on talbe and that what should be in SSAS.

My current workaround is to use LFFT OUTER JOIN in this case, but that
should not be like that.

Thanks

"Vimas" <Vi...@nospam.nospam> wrote in message
news:uNnxcnLh...@TK2MSFTNGP05.phx.gbl...

[privatenews]

Hello,

Based on my scope, SSAS will identiy all primary keys in the tables as the
primary key in a view. Usually there is not necessary to change this
behavior. Will you let me know why you want to change this? Since
Bcol1=Acol1=Col1, and there is no necessary to select all the columns
because they are redundent.

Based on my test, if you want to change this behavior, you could drop
primary key on tableb and tablec, and then try to remove/add the view to
the view again. You shall see the primary keys are acol1 and acol2 at this
time, and then you could add primary key again on tableb and tablec.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================

This posting is provided "AS IS" with no warranties, and confers no rights.

[Vimas]

H Peter,

Thanks for your respond. Unfortunatelly, SSAS does not properly recognize
keys in my view. I have one main table and 3 lookup tables. Primary key
supposed to be just from main table. When I do INNER JOIN to lookup tables,
then logical primary key that SSAS creates is join off all keys from main
table and 3 lookup tables. That is not correct. Simply SSAS just does not
recognize keys properly.
As a workaround I join main table to lookup tables using LEFT OUTER JOIN,
then SSAS makes logical primay key as expected - keys from main table. I
have large SSAS model - about 40 measure groups. Biggest problems with
logical primary keys I have are in facts tables that have certain joins. 10
out of 40 measure groups have incorrect logical keys! I do not mind that, I
just was wondering if I could override SSAS behaviour in that part.

There is a sample script to prove my point:
CREATE TABLE a (ID1 int NOT NULL, ID2 int NOT NULL, FID1 int, FID2 int,
JustAField int NOT NULL)

go

ALTER TABLE a ADD CONSTRAINT PK_a PRIMARY KEY(ID1, ID2)

go

CREATE TABLE b (FID1 int NOT NULL, FField varchar(10) NOT NULL)

go

ALTER TABLE b ADD CONSTRAINT PK_b PRIMARY KEY(FID1)

go

CREATE VIEW x AS SELECT A.ID1, A.ID2, B.FID1, B.FField, A.JustAField

FROM a JOIN b ON B.FID1 = A.FID1

go

Now load view x into SSAS data source view. SSAS will create fields ID1, ID2
and FID1 as logical primary key on this view.

So, if you were able to reproduce this problem - how can I make SSAS choose
logical primary keys the way I want? Is this possible?

I am using SSAS 2005, 64bit, SP1.

Thanks

""privatenews"" <pet...@online.microsoft.com> wrote in message
news:9Zki3DUh...@TK2MSFTNGXA01.phx.gbl...

[Vimas]

Just to add: it is not an option for me to temporary remove keys from DB. As
I do data source view refresh every day, next refresh will add keys back to
SSAS data source view :-(. I need this change done from SSAS side, not SQL
Server side.

"Vimas" <Vi...@nospam.nospam> wrote in message

news:eOq8DpXh...@TK2MSFTNGP04.phx.gbl...

[privatenews]

Hello,

Thank you for your update. I was not able to find other workaround on this
issue at present. Please rest assured this has been routed to the proper
channel. In the meantime, I also encourage you submit your feedback on this
behavior via the link below

http://lab.msdn.microsoft.com/productfeedback/default.aspx

Best Regards,

Peter Yang
MCSE2000, MCSA, MCDBA
Microsoft Partner Online Support

Get Secure! - www.microsoft.com/security

=====================================================

When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------
| From: "Vimas" <Vi...@nospam.nospam>
| References: <uNnxcnLh...@TK2MSFTNGP05.phx.gbl>
<eRZ3itLh...@TK2MSFTNGP04.phx.gbl>
<9Zki3DUh...@TK2MSFTNGXA01.phx.gbl>
<eOq8DpXh...@TK2MSFTNGP04.phx.gbl>
| Subject: Re: SSAS 2005: How to I change logical primary key in data
source view
| Date: Thu, 1 Jun 2006 09:00:32 -0400
| Lines: 101
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2869
| X-RFC2646: Format=Flowed; Response
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2869
| Message-ID: <eoN1etXh...@TK2MSFTNGP04.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.olap
| NNTP-Posting-Host:
cpe001310f5a811-cm0012c90f99c6.cpe.net.cable.rogers.com 70.29.102.55
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTNGP04.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.olap:70142
| X-Tomcat-NG: microsoft.public.sqlserver.olap

[Dan Hare]

I had the same problem.
If you add the table as a named query (eg SELECT * FROM myView) this will allow you to define the logical key yourself.

hth

From http://www.developmentnow.com/g/112_2006_5_0_0_765703/SSAS-2005-How-to-I-change-logical-primary-key-in-data-source-view.htm

Posted via DevelopmentNow.com Groups
http://www.developmentnow.com/g/

[mano]

Hi!
First Happy New Year!
I would like to ask some questions about Microsoft BI.
I am from Bulgaria - Sofia university and I chose to write diplom work for
my master degree on theme "Microsoft Business Intelligences technologies
research".
My diplom work instructor gave me the following questions as a base points
for my diplom work:
- ROLAP, MOLAP и HOLAP support in Microsoft BI
- data compression in multidimensional data model
- maximum possible number of dimensions
- how Microsoft BI server can process large dimensions containing 700
000-800 000 elements in a dimension
- are there specialized solutions to problems as segmentation of calculated
changes of dimension hierarchies for a given period in the time
- what methods are there for working with non-additive facts(for example
non-additive facts caused by "many to many" relations)
My main problems are with the last 2 questions - I have searched so hard,
but I have not found no info about this.
I would be very glad if you can help me or tell me where to find info about
this.
Every internet address or info you can give me will be very helpful for me.
Have a nice day!

[Piquet]

I am experiencing the same issue in SQL 2008 with a view in my DSV, however the ramifications are worse - the Logical Primary Key that BIDS selects for this view is NOT A UNIQUE KEY for the records exposed by this view - at least with the example provided above the logical primary key, whilst excessive, at least still provides uniqueness.
In my case, as I wish to provide fact-table details on a drill-through action, I have problems doing this as the logical primary key does not giver me aunique reference for each FACT row.
The effective primary key on this snapshot-style fact table (view) is LoanID + ReportingPeriodID, however BIDS thinks it is LoanID + CustomerID!

The workaround to this problem for now is to define a Named Query on top of the view - adding another layer of abstraction so that BIDS keeps its mittens off the Logical Primary Key for this fact table, enabling me to define it correctly (sigh).

[Salman@discussions.microsoft.com Muhammad Salman]

May be the following post help you:
http://muhammadsalmanx.wordpress.com/2009/12/15/facts-and-dimesions-on-views/

Thanks

"Piquet" wrote:

> .
>

[Mtimonera]

The work around this is to "View Code" on the DSV file and manually delete the primary key entry.

IE
<--unique name="Constraint1" msdata:PrimaryKey="true">
<--selector xpath=".//dbo_scv_ReportingModel" />
<--field xpath="WrongPrimaryKeyColumn" />
<--unique>

and just set the correct logical primary key

From http://www.developmentnow.com/g/112_2009_1_0_0_765703/SSAS-2005-How-to-I-change-logical-primary-key-in-data-source-view.htm