mapping Windows credentials to access linked server

Hi all,
I have linked SQL Server "SRV2" to SQL Server "SRV1" through
sp_addlinkedserver.
In my scenario, Windows user "U1" has access to "SRV1" while Windows
user "U2" has access to "SRV2".
Whenever I access "SRV1" as "U1" and execute a distributed query which
involves "SRV2", I would like "U1" to be mapped to "U2" for accessing
"SRV2".
Does anyone know whether this is possible and how?
I know that I can pass-through "U1" credentials to "SRV2" with
delegation and the default mapping, or map "U1" to a SQL User "sqlU2"
that can access "SRV2".
However what I would like to do is to map Windows user "U1" to Windows
user "U2".
I am using SQL Server 2005 which comes with Visual Studio Beta2.
Thanks in advance for any help,
-GianlucaHi
As fas as I know you will have to create a new login in "SRV2". with the
same permissions.
"Gianluca Torta" <giatorta@.hotmail.com> wrote in message
news:1121206171.989233.19240@.f14g2000cwb.googlegroups.com...
> Hi all,
> I have linked SQL Server "SRV2" to SQL Server "SRV1" through
> sp_addlinkedserver.
> In my scenario, Windows user "U1" has access to "SRV1" while Windows
> user "U2" has access to "SRV2".
> Whenever I access "SRV1" as "U1" and execute a distributed query which
> involves "SRV2", I would like "U1" to be mapped to "U2" for accessing
> "SRV2".
> Does anyone know whether this is possible and how?
> I know that I can pass-through "U1" credentials to "SRV2" with
> delegation and the default mapping, or map "U1" to a SQL User "sqlU2"
> that can access "SRV2".
> However what I would like to do is to map Windows user "U1" to Windows
> user "U2".
> I am using SQL Server 2005 which comes with Visual Studio Beta2.
> Thanks in advance for any help,
> -Gianluca
>

Many to many relationships

Hi,

I have a many-to-many relationship scenario and I had used a group dimension instead of the bridge table concept.

I had referred "Data Warehouse Lifecycle Toolkit" by Kimball and others.

For ex:
An Athlete belonging to multiple athletic teams and with the possiblity of changing every year.

So I had created an Athlete Team dimension, with a group key and the team code.

Athelete Group Key,
Athelete Team Code,
Athelete Team Name

with Athlete Group Key and Athlete Team Code as the key for the table.

And in the fact/measure group, referring to the Group Key.

Now, in SSAS, I had created the Athlete Team dimension with Group Key as the key atrribute and others as dimension attributeS.

***********************
Athlete dimension table
--

Group Key, Athlete Code, Athlete Name,
1 , FTB , Football
1 , TEN , Tennis
2 , TEN , Tennis
2 , VOL , Volleyball

Fact/Measure group

Athlete ID, Year, Group Key
123 , 2001, 1
234 , 2001, 2

************************

When processed and browsed the cube, for the number of students for each type of team, the result is considering only the first team for the count (it is ordered alphabetically).

In the sample data example above, the count of athletes for team Football, Tennis and Volleyball are 1, 2 and 1 respectively.

But in SSAS, the count of athletes for the team Football, Tennis and Volleyball are 1, 1 and 0 respectively.

For the same requirement, the SQL query on the SQLDB, is fetching me the correct result, but this is required for adhoc reporting as well and hence have to be done using the cube itself.

Comments on this would be really helpful.

Thanks,
Vivek C.

Vivek,

I might just be getting stuck on the example, but this sounds like a Type 2 changing dimension and not a many-to-many scenario.

So, you have this Athlete dimension. It's primary key is AthleteID and contains a reference to the TeamID. Here is the structure of the table:

create table Athlete (

AthleteID int not null identity(1,1),

Name varchar(200) not null,

TeamID int not null,

RowStartDate datetime not null default (getdate()),

RowEndDate datetime null,

IsRowCurrent bit not null default (1)

)

alter table Athlete add

constraint PK_Athlete primary key (athleteid),

constraint AK_Athlete unique (name, rowstartdate),

constraint FK_Athlete_TeamID foreign key (teamid) references Team (TeamID)

create table Team (

TeamID int not null identity(1,1),

Name varchar(200) not null

)

alter table Team add

constraint PK_Team primary key (teamid),

constraint AK_Team unique (teamid)

Every time an athlete changes a team, a new record is created for the record. The previous record gets end dated and the IsRowCurrent flag gets properly set. In your cube, you can use these two tables in a single dimension or do two dimensions and create a referenced relationship.

If an athlete could belong to multiple teams simultaneously, you would then have a many-to-many relationship. In this scenario, you would have Athlete and Team dimension tables. The Athlete table would not have a reference to the Team dimension table. Instead, you would have a AthleteTeam table which contains a reference to the Athlete dimension table and another to the Team dimension table. This is your bridge table concept.

Let's say a single record in the fact table pointed to multiple athletes, such as athletes involved in a play (think baseball). This would be the group table you mentioned. You would have an Athlete table like described above that points to Team. You would build a table that assigns a single group ID to the list of players involved, and a Group table that represents that list. The fact table would point to the Group table.

create table AthleteGroup (

AthleteGroupID int not null identity(1,1),

NumberOfAthletes int not null

)

alter table AthleteGroup add constraint PK_AthleteGroup primary key (athletegroupid)

create table AthleteGroupAthlete (

AthleteGroupID int not null,

AthleteID int not null

)

alter table AthleteGroupAthlete add

constraint PK_AthleteGroupAthlete primary key (AthleteGroupID, AthleteID),

constraint FK_AthleteGroupAthlete_AthleteGroupID foreign key (athletegroupid) references AthleteGroup (AthleteGroupID),

constraint FK_AthleteGroupAthlete_AthleteID foreign key (athleteid) references Athlete (AthleteID)

The AthleteGroupAthlete table would then be used to create a measure group with a single measure (using a COUNT aggregation). This becomes the intermediate measure group in the many-to-many relationship.

Good luck,
Bryan

|||

Hi Bryan,

Thanks for the reply.

My requirement is for an athlete belonging to multiple teams simultaneously. And the way you have explained in the second part, would have been the right way to move ahead.

But currently, the way it is implemented is as explained my first post. Is there a way to make it work with some settings in Analysis Services Solution? As said earlier the concept of the grouping works, when we work on the relational database based data warehouse.

Thanks,

Vivek C.

|||

Sorry for not replying sooner. For some reason I'm not getting alerts regularly.

Regarding the Athelete dimension, I'm afraid I don't see a way to implement it the way you describe. Is changing the model an option for you?

Thanks,
Bryan

|||

Changing the data model would be difficult, as we have already done with the ETL work and certain set of reports.

Anyway thanks for the inputs, let me see whether I can talk others into changing the model.

Thanks,

Vivek

|||The simplest representation seems to be a fact table reperesenting membership in a team and then a Team dimension, an Athlete dimension, and a Time dimension (with a granularity of year). Then the fact table simply contains a row for each team an athlete is on for each year the athelete is on that team. Then your measure group can contain a single measure that is a count of rows which you can filter based on any combination of team, athlete, or year. If I recall correctly, Kimball calls this type of design a "factless fact table".|||

Hi Matt,

Thanks for the inputs. The reason for not implementing using the factless fact (this was our first option), was data explosion.

Consider the case where the Athlete can belong to different team simultaneously and this changing every quarter. In this case the number of records would keep increasing periodically (quarterly). So we thought we would go the grouping of athlete teams.

Though the grouping option works with the data warehouse, we are not able to get it work properly using SSAS.

My guess is that SSAS handles many to many relationships using the factless fact table.

Thanks,

Vivek

|||

You'd be surprised at how many records you can handle if you keep them smalll by using int or smallint for your foreign keys. Assuming 3 int foreign keys, each row would cost you 3*4=12 Bytes without any indexes and SSAS doesn't need indexes. 1000 athlethes on an average of 4 teams over 100 years at the quarter level granularity would need 1000*4*100*4=19,2000,000 rows. This sounds like a lot, but at 12 bytes per row this is only 18.3MB which is tiny.

I must admint, I don't fully understand the design you originally described. It sounds like two dimensions and one fact table with no many-to-many relationships in AS terms (that is, both dimensions are regular dimensions for the measure group.) Based on this, I suspect the problem you are currently seeing is caused by dimension keys not being unique for the Athlete Team table since Team Code will occur multiple times in the Ahtlete Team table. I think what you may want to do in this design is to break out Group Key from the Ahtlete Team table and create a GroupToTeam table contain Group Key and Team code. This table would be a fact table in AS which would then be used to create a many-to-many relationship from your other Fact table to the Athlete Team table.

|||

Matt,

The way you have explanied of breaking the Group Key from the Athelete Team table/dimension is the best thing to do.

Anyway, we have gone ahead and changed our data model to the way exactly SSAS handles, viz. by factless fact tables.

Thanks for the inputs.

Vivek

Many to many relationships

Hi,

I have a many-to-many relationship scenario and I had used a group dimension instead of the bridge table concept.

I had referred "Data Warehouse Lifecycle Toolkit" by Kimball and others.

For ex:
An Athlete belonging to multiple athletic teams and with the possiblity of changing every year.

So I had created an Athlete Team dimension, with a group key and the team code.

Athelete Group Key,
Athelete Team Code,
Athelete Team Name

with Athlete Group Key and Athlete Team Code as the key for the table.

And in the fact/measure group, referring to the Group Key.

Now, in SSAS, I had created the Athlete Team dimension with Group Key as the key atrribute and others as dimension attributeS.

***********************
Athlete dimension table
--

Group Key, Athlete Code, Athlete Name,
1 , FTB , Football
1 , TEN , Tennis
2 , TEN , Tennis
2 , VOL , Volleyball

Fact/Measure group

Athlete ID, Year, Group Key
123 , 2001, 1
234 , 2001, 2

************************

When processed and browsed the cube, for the number of students for each type of team, the result is considering only the first team for the count (it is ordered alphabetically).

In the sample data example above, the count of athletes for team Football, Tennis and Volleyball are 1, 2 and 1 respectively.

But in SSAS, the count of athletes for the team Football, Tennis and Volleyball are 1, 1 and 0 respectively.

For the same requirement, the SQL query on the SQLDB, is fetching me the correct result, but this is required for adhoc reporting as well and hence have to be done using the cube itself.

Comments on this would be really helpful.

Thanks,
Vivek C.

Vivek,

I might just be getting stuck on the example, but this sounds like a Type 2 changing dimension and not a many-to-many scenario.

So, you have this Athlete dimension. It's primary key is AthleteID and contains a reference to the TeamID. Here is the structure of the table:

create table Athlete (

AthleteID int not null identity(1,1),

Name varchar(200) not null,

TeamID int not null,

RowStartDate datetime not null default (getdate()),

RowEndDate datetime null,

IsRowCurrent bit not null default (1)

)

alter table Athlete add

constraint PK_Athlete primary key (athleteid),

constraint AK_Athlete unique (name, rowstartdate),

constraint FK_Athlete_TeamID foreign key (teamid) references Team (TeamID)

create table Team (

TeamID int not null identity(1,1),

Name varchar(200) not null

)

alter table Team add

constraint PK_Team primary key (teamid),

constraint AK_Team unique (teamid)

Every time an athlete changes a team, a new record is created for the record. The previous record gets end dated and the IsRowCurrent flag gets properly set. In your cube, you can use these two tables in a single dimension or do two dimensions and create a referenced relationship.

If an athlete could belong to multiple teams simultaneously, you would then have a many-to-many relationship. In this scenario, you would have Athlete and Team dimension tables. The Athlete table would not have a reference to the Team dimension table. Instead, you would have a AthleteTeam table which contains a reference to the Athlete dimension table and another to the Team dimension table. This is your bridge table concept.

Let's say a single record in the fact table pointed to multiple athletes, such as athletes involved in a play (think baseball). This would be the group table you mentioned. You would have an Athlete table like described above that points to Team. You would build a table that assigns a single group ID to the list of players involved, and a Group table that represents that list. The fact table would point to the Group table.

create table AthleteGroup (

AthleteGroupID int not null identity(1,1),

NumberOfAthletes int not null

)

alter table AthleteGroup add constraint PK_AthleteGroup primary key (athletegroupid)

create table AthleteGroupAthlete (

AthleteGroupID int not null,

AthleteID int not null

)

alter table AthleteGroupAthlete add

constraint PK_AthleteGroupAthlete primary key (AthleteGroupID, AthleteID),

constraint FK_AthleteGroupAthlete_AthleteGroupID foreign key (athletegroupid) references AthleteGroup (AthleteGroupID),

constraint FK_AthleteGroupAthlete_AthleteID foreign key (athleteid) references Athlete (AthleteID)

The AthleteGroupAthlete table would then be used to create a measure group with a single measure (using a COUNT aggregation). This becomes the intermediate measure group in the many-to-many relationship.

Good luck,
Bryan

|||

Hi Bryan,

Thanks for the reply.

My requirement is for an athlete belonging to multiple teams simultaneously. And the way you have explained in the second part, would have been the right way to move ahead.

But currently, the way it is implemented is as explained my first post. Is there a way to make it work with some settings in Analysis Services Solution? As said earlier the concept of the grouping works, when we work on the relational database based data warehouse.

Thanks,

Vivek C.

|||

Sorry for not replying sooner. For some reason I'm not getting alerts regularly.

Regarding the Athelete dimension, I'm afraid I don't see a way to implement it the way you describe. Is changing the model an option for you?

Thanks,
Bryan

|||

Changing the data model would be difficult, as we have already done with the ETL work and certain set of reports.

Anyway thanks for the inputs, let me see whether I can talk others into changing the model.

Thanks,

Vivek

|||The simplest representation seems to be a fact table reperesenting membership in a team and then a Team dimension, an Athlete dimension, and a Time dimension (with a granularity of year). Then the fact table simply contains a row for each team an athlete is on for each year the athelete is on that team. Then your measure group can contain a single measure that is a count of rows which you can filter based on any combination of team, athlete, or year. If I recall correctly, Kimball calls this type of design a "factless fact table".|||

Hi Matt,

Thanks for the inputs. The reason for not implementing using the factless fact (this was our first option), was data explosion.

Consider the case where the Athlete can belong to different team simultaneously and this changing every quarter. In this case the number of records would keep increasing periodically (quarterly). So we thought we would go the grouping of athlete teams.

Though the grouping option works with the data warehouse, we are not able to get it work properly using SSAS.

My guess is that SSAS handles many to many relationships using the factless fact table.

Thanks,

Vivek

|||

You'd be surprised at how many records you can handle if you keep them smalll by using int or smallint for your foreign keys. Assuming 3 int foreign keys, each row would cost you 3*4=12 Bytes without any indexes and SSAS doesn't need indexes. 1000 athlethes on an average of 4 teams over 100 years at the quarter level granularity would need 1000*4*100*4=19,2000,000 rows. This sounds like a lot, but at 12 bytes per row this is only 18.3MB which is tiny.

I must admint, I don't fully understand the design you originally described. It sounds like two dimensions and one fact table with no many-to-many relationships in AS terms (that is, both dimensions are regular dimensions for the measure group.) Based on this, I suspect the problem you are currently seeing is caused by dimension keys not being unique for the Athlete Team table since Team Code will occur multiple times in the Ahtlete Team table. I think what you may want to do in this design is to break out Group Key from the Ahtlete Team table and create a GroupToTeam table contain Group Key and Team code. This table would be a fact table in AS which would then be used to create a many-to-many relationship from your other Fact table to the Athlete Team table.

|||

Matt,

The way you have explanied of breaking the Group Key from the Athelete Team table/dimension is the best thing to do.

Anyway, we have gone ahead and changed our data model to the way exactly SSAS handles, viz. by factless fact tables.

Thanks for the inputs.

Vivek

Many to Many Look alike Dimensions

Hi all,

I have this design scenario that I need to validate if this is the best approach and that there are no other alternatives that I have not looked at.

OLTP 3 Tables:

Loans (PK-->LoanID, LoanSequence)

Borrower (PK-->BorrowerID, columns LoanID, LoanSequence are also there)

BorrowerAddress (PK--> BorrowerAddressID, columns BorrowerID, LoanID, LoanSequence are also there)

1. The OLTP system does not do update, every update is inserted as a new record. So we practically have type 2 change on OLTP system side. This is why Primary Key in Loan table is a com

2. One Loan record can have many borrowers (Primary, Co-borrower, Other Borrrower)

3. One Borrower can change his/her address many time during the course of the application process. So this implies many BorrowerAddresses for Each Borrower.

OLAP Tables:

table FactLoanBorrower and FactBorrowerAddress are the middle table for the many-to-many relationship.

FactLoans (TimeKey,FactLoanBorrowerKey)

FactLoanBorrower (FactLoanBorrowerKey, LoanID, LoanSequence, BorrowerID, BorrowerTypeKey)

FactBorrowerAddress (FactLoanAddressKey, BorrowerAddressID, BorrowerID, LoanID, LoanSequence)

DimBorrowerType (BorrowerTypeKey)

DimBorrower (BorrowerKey with Natural Keys (BorrowerID, LoanID, LoanSequence))

DimBorrowerAddress (BorrowerAddressKey, BorrowerID, LoanID, LoanSequence)

Basically the relationships look like this:

FactLoans<--FactLoanBorrower-->DimBorrower<--FactBorrowerAddress-->DimBorrowerAddress.

Is this design valid?

thank ahead.

Yes, this may work, however please measure performance on the predicted data volumes you're likelt to have and for a representative query workload.

Thank you

Manipulation in the front end

Hi

I have a scenario while creating a report where I need to maintain the same number of rows for a column, even if the data grows or shrinks for that column

example

Each Scenario is grouped by ID

Scenario 1

columnA

Row1 X X

Row2 X

Row3 X

Row4 XYZ

Scenario 2

columnA

Row1 XA

Row2 XYZ

In both scenario's I need to control from front end

the number of row should remain 4

I am grouping by row field say Type

This field Type varies for each ID group

This seems to work.

1. Use a table with a group on the ID field.

2. Use four rows in the detail section

3. On the first detail row Hidden property use this expression.

=iif(RowNumber("table1_Group1")<=4,False,True)

4. On the second detail row Hidden property use this expression.

=iif(CountRows()=1,False,True)

5. On the third detail row Hidden property use this expression.

=iif(CountRows()=1 or (CountRows()=2 and RowNumber("table1_Group1")=2) ,False,True)

6. On the third detail row Hidden property use this expression.

=iif(CountRows()=1 or (Countrows()=2 and and RowNumber("table1_Group1")=2) or (Countrows()=3 and and RowNumber("table1_Group1")=3) ,False,True)

Managing reporting services models

Hi everybody,

I have the following scenario: I have web application which is creting new SQL Server database each time when new customer is created in the application.

I would like to give users the possibility of creating ad hoc reports and thus I need to create new connection and report model (and deploy them to my reoport server) each time when the new database is created (report models can not use multiple databases). Does anybody knows how can I do that?

Maybe there is another approach to this kind of problem?

Thank you in advance,

Marek

You can create datasources and autogenerate models from these datasources using the SSRS SOAP API. Check out this article in BOL for information on how to get started using the SOAP API:

http://msdn2.microsoft.com/en-us/library/ms159162.aspx