Interface for Analysis Services 2005

Dear friends,

I have a problem…

1. How can I show the data from Analysis Services 2005? In ASP.Net 2.0 or Sharepoint 2007?
2. I have users and groups in tables from one database, how can control it in the interface in ASP.NET 2.0 or Sharepoint? (I’m cant use the asp.net 2.0 security controls) It’s easy to allow or deny users or groups to view some items as we can do in asp.net 2.0 controls?

Thanks!!

Pedro,

I do not know about ASP.NET, but you can build reports using Excel 2007 and use Sharepoint as report repository.

High level design:

- In Sharepoint you create connection library(s) that point to appropriate cubes/perspectives in SSAS 2005

- In Sharepoint you create report library(s)

- You create reports using connections from Sharepoint connection library and save them in Sharepoint report library

- You can use SharePoint Excel services for users to see reports with some limited interactivity. Users can use these reports just using browser, no need for Excel. And reports/charts look very similar to what you would see in Excel.

In Sharepoint you would use Integrated security to allow/deny access to reports. But of course you would have to setup proper role security in SSAS 2005 first.

Vidas Matelis

|||

Thanks Vidas for your help.

Note, that I'm using excel 2003, and I will allow users to use the report builder... and I'm very confused aboout the previligies for each user...

I have the user and groups in a OLTP database...

regards

|||

In SSAS 2005 you have security roles where you assing permissions to windows users group. So there you will define if specific windows user/group can access that cube.

Another level of security will be for reports. In sharepoint you can put reports to different report libraries or different report folders and assign if user/group can access that library or folder.

Keep in mind that if user have access to cube, he/she can just start Excel and connect to SSAS to query that cube. Report permissions should go hand in hand with cube/dimension permissions.

If you have excel 2003, I do not believe you will be able to use Excel Services in SharePoint 2007.

As you already know, Excel 2003 does not work very well with SSAS 2005. It is more in presentation, than calculation. You see all attributes as dimensions, and measures are not groupped by measure group and/or folder. So for bigger databases this is big problem.

Vidas Matelis

|||

Thanks for your post...

But how can I control the access to the cubes or reports, if I have the users and groups inside tables and not in the active directory?

Thanks

|||

Pedro,

Analysis Services can use just integrated security. So you cannot change SSAS security based on users saved in SQL Server table.

Vidas Matelis

|||

> Analysis Services can use just integrated security. So you cannot change SSAS security based on users saved in SQL Server table.

Unless you control middle tier, which seems to be a scenario here. It would involve some ASP.NET coding, but it is possible to use users from SQL table. For more information, please read about Roles property and for more dynamic scenarios - CustomData property and MDX function.

|||

Pedro, sorry I misguided you. I based my answer on BOL information, but Mosha pointed that there are ways to do it.

Mosha,

Any published papers on this? Any examples on how this could be done?

I googled it, but cannot find much more detail information.

Thank you,

Vidas Matelis

|||

yes... mosha, do you have some example? or links about the subject?

Thanks both!

|||

MOsha,

Supose I use integrated security, where I can have the groups and roles? The groups are in Active Directory as the users?

Regards!

|||In the middle tier (usually ASP.NET app), after you authenticated the user, you can look up in the SQL table or in AD or somewhere else what are the roles he should belong to. Then you create ADOMD.NET connection for that user passing "Roles=Role1,Role2,Role3" connection string parameter. If static role assignment doesn't work for you, you can pass user id through CustomData property, i.e. "CustomData=appuser1", and then inside security definitions you can use CustomData() MDX function which will resolve to the value passed in the property.|||

Mosha,

Could you please confirm if my understanding for Role property is right:

- For this to work middle tier should have full (or at least some) access to SSAS database.

- User does not need to have access to SSAS database, as it is queried by middle tier.

- Including role parameter in connection string will further limit middle tier access to SSAS based on roles specified.

- Will there be any penalty for performance using this? I know that years back when connection string to relational DB did not matched exactly, then you could not reuse cache betten connection.

Using CustomData() function, is my understanding right:

- I would disable access to direct measures to users.

- I would create calculated measures and use CustomData() function to limit what values are available.

- With this approach I cannot hide dimensions, hierarchies, cubes, just calculated measure values.

Did I understand right?

Thank you,

Vidas Matelis

|||

For Roles property your understanding is correct.

> Will there be any penalty for performance using this? I know that years back when connection string to relational DB did not matched exactly, then you could not reuse cache betten connection.

You won't be able to reuse connections with different Roles set on them. The FE caches cannot be reused as well (SE caches can be reused sometimes). For more information check out the SQL Server 2005 Analysis Services book, page 511 - it has good explanation of this subject.

> Using CustomData() function, is my understanding right:

- I would disable access to direct measures to users.

- I would create calculated measures and use CustomData() function to limit what values are available.

- With this approach I cannot hide dimensions, hierarchies, cubes, just calculated measure values.

You misunderstood how CustomData can be used for security. Just think about it as a replacement for the UserName() function in dynamic security when your authentication is not Windows Integrated but a custom one.

|||

Mosha,

Thank you! This was very helpful.

Vidas Matelis

Interesting Query Problem!

Dear All,
I have three tables:
teams table
id name
--
1 Team A
2 Team B
3 Team C
4 Team D
fixtures table
id home team id away team id
---
1 1 2
2 1 3
3 1 4
4 2 1
5 2 3
6 2 4
7 3 1
8 3 2
9 3 4
10 4 1
11 4 2
12 4 3
results table
fixture id home team result away team result
----
--
1 1 3
2 4 0
3 2 2
4 2 2
5 0 4
6 1 3
7 3 1
8 2 2
9 4 0
10 2 2
11 0 4
12 1 3
what I want to be able to to is generate a table (list) of teams as the
output from a sql query in an order dependant on two things:
1) number of points won (highest number of points at the top) and 2)
(which is where the interesting bit comes into it) where two or more
teams have the same number of points the position is to be determined
by examining the results between the two or more teams in question.
So the output I am looking for in this example:
output table
team name total team points notes
----
Team C 16 1.
Team B 12 2.
Team A 12 3.
Team D 8 4.
Note: 1. Team C have 16 points therefore are the clear leaders of the
table
Note: 2. AvB = 1-3 BvA = 2-2 therefore Team B [5pts] is above Team A
[3pts]
Note: 3. see note 2
Note: 4. Team D have 8 points therefore are last in the table
My question is how do I write an sql query that will interrogate these
three tables to give my the output as described above.
Many Thanks
Simonsimon.stockton@.baesystems.com wrote:

> [stuff]
Are BAE Systems branching out into Sunday 5-a-side football logistics
now? ;-)|||Bobbo,
This is an attempt at a rather simple example so that I can
explain/understand the theory behind how to solve the problem before
applying it to my particular situation.
Regards
Simon|||simon.stockton@.baesystems.com wrote:
> This is an attempt at a rather simple example so that I can
> explain/understand the theory behind how to solve the problem before
> applying it to my particular situation.
... and that was my attempt at humour, sorry.
This code below seems to work for me, although I'm sure someone else
will be able to suggest how it can be simplified as it looks
ridiculously over-complicated.
Also, the names have changed since your example, but I'm sure you'll
get the idea.
-- Outer query to sum home and away games and order by total points
select [name], sum(points) points
from
(
-- Top half of subquery to get points for home games
select t.[name], sum(s.home) points
from scores s
join fixes f on s.fixid = f.fixid
join teams t on f.homeid = t.teamid
group by t.[name]
union all
-- Second half of subquery to get points for away games
select t.[name], sum(s.away) points
from scores s
join fixes f on s.fixid = f.fixid
join teams t on f.awayid = t.teamid
group by t.[name]
) as sub
group by [name]
order by points desc|||Bobbo,
Thanks for that. I can see how that work.
However, it doesn't cover:
"2) (which is where the interesting bit comes into it) where two or
more
teams have the same number of points the position is to be determined
by examining the results between the two or more teams in question."
So once having used your query to generate a list, this list then needs
to be sorted in the event that two teams share the same number of
points, by looking at the individual points against the opponent team
who share the same number of points.
Given the data originally specified I don't think that your query will
generate the output table as shown earlier.
Getting there though!
Thanks
Simon|||The following code may do it for you:
set nocount on
create table teams
(
id int primary key
, name varchar (20) not null
)
go
insert teams values (1, 'Team A')
insert teams values (2, 'Team B')
insert teams values (3, 'Team C')
insert teams values (4, 'Team D')
go
create table fixtures
(
id int primary key
, home int not null
references teams (id)
, away int not null
references teams (id)
)
insert fixtures values (1, 1, 2)
insert fixtures values (2, 1, 3)
insert fixtures values (3, 1, 4)
insert fixtures values (4, 2, 1)
insert fixtures values (5, 2, 3)
insert fixtures values (6, 2, 4)
insert fixtures values (7, 3, 1)
insert fixtures values (8, 3, 2)
insert fixtures values (9, 3, 4)
insert fixtures values (10, 4, 1)
insert fixtures values (11, 4, 2)
insert fixtures values (12, 4, 3)
go
create table results
(
id int primary key
references fixtures (id)
, home int not null
, away int not null
)
go
insert results values (1, 1, 3)
insert results values (2, 4, 0)
insert results values (3, 2, 2)
insert results values (4, 2, 2)
insert results values (5, 0, 4)
insert results values (6, 1, 3)
insert results values (7, 3, 1)
insert results values (8, 2, 2)
insert results values (9, 4, 0)
insert results values (10, 2, 2)
insert results values (11, 0, 4)
insert results values (12, 1, 3)
go
with totals (teamid, total, wins)
as
(
select
case type
when 1 then f.home
when 2 then f.away
end
, sum (case type
when 1 then r.home
when 2 then r.away
end)
, sum (case
when type = 1 and sign (r.home - r.away) > 0 then 1
when type = 2 and sign (r.away - r.home) > 0 then 1
else 0
end)
from
fixtures f
join
results r on r.id = f.id
cross join
(
select 1 union all
select 2
) as types (type)
group by
case type
when 1 then f.home
when 2 then f.away
end
)
select
t2.name
, t1.total
from
totals t1
join
teams t2 on t2.id = t1.teamid
order by
total desc
, wins desc
go
drop table results
drop table fixtures
drop table teams
The use of the CTE is SQL 2005, but you can do it without it - just use a
derived table instead.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
<simon.stockton@.baesystems.com> wrote in message
news:1155557745.509502.222530@.i42g2000cwa.googlegroups.com...
Dear All,
I have three tables:
teams table
id name
--
1 Team A
2 Team B
3 Team C
4 Team D
fixtures table
id home team id away team id
---
1 1 2
2 1 3
3 1 4
4 2 1
5 2 3
6 2 4
7 3 1
8 3 2
9 3 4
10 4 1
11 4 2
12 4 3
results table
fixture id home team result away team result
----
--
1 1 3
2 4 0
3 2 2
4 2 2
5 0 4
6 1 3
7 3 1
8 2 2
9 4 0
10 2 2
11 0 4
12 1 3
what I want to be able to to is generate a table (list) of teams as the
output from a sql query in an order dependant on two things:
1) number of points won (highest number of points at the top) and 2)
(which is where the interesting bit comes into it) where two or more
teams have the same number of points the position is to be determined
by examining the results between the two or more teams in question.
So the output I am looking for in this example:
output table
team name total team points notes
----
Team C 16 1.
Team B 12 2.
Team A 12 3.
Team D 8 4.
Note: 1. Team C have 16 points therefore are the clear leaders of the
table
Note: 2. AvB = 1-3 BvA = 2-2 therefore Team B [5pts] is above Team A
[3pts]
Note: 3. see note 2
Note: 4. Team D have 8 points therefore are last in the table
My question is how do I write an sql query that will interrogate these
three tables to give my the output as described above.
Many Thanks
Simon

Interesting Query Problem!

Dear All,
I have three tables:
teams table
id name
--
1 Team A
2 Team B
3 Team C
4 Team D
fixtures table
id home team id away team id
---
1 1 2
2 1 3
3 1 4
4 2 1
5 2 3
6 2 4
7 3 1
8 3 2
9 3 4
10 4 1
11 4 2
12 4 3
results table
fixture id home team result away team result
-----
1 1 3
2 4 0
3 2 2
4 2 2
5 0 4
6 1 3
7 3 1
8 2 2
9 4 0
10 2 2
11 0 4
12 1 3
what I want to be able to to is generate a table (list) of teams as the
output from a sql query in an order dependant on two things:
1) number of points won (highest number of points at the top) and 2)
(which is where the interesting bit comes into it) where two or more
teams have the same number of points the position is to be determined
by examining the results between the two or more teams in question.
So the output I am looking for in this example:
output table
team name total team points notes
----
Team C 16 1.
Team B 12 2.
Team A 12 3.
Team D 8 4.
Note: 1. Team C have 16 points therefore are the clear leaders of the
table
Note: 2. AvB = 1-3 BvA = 2-2 therefore Team B [5pts] is above Team A
[3pts]
Note: 3. see note 2
Note: 4. Team D have 8 points therefore are last in the table
My question is how do I write an sql query that will interrogate these
three tables to give my the output as described above.
Many Thanks
Simonsimon.stockton@.baesystems.com wrote:
> [stuff]
Are BAE Systems branching out into Sunday 5-a-side football logistics
now? ;-)|||Bobbo,
This is an attempt at a rather simple example so that I can
explain/understand the theory behind how to solve the problem before
applying it to my particular situation.
Regards
Simon|||simon.stockton@.baesystems.com wrote:
> This is an attempt at a rather simple example so that I can
> explain/understand the theory behind how to solve the problem before
> applying it to my particular situation.
... and that was my attempt at humour, sorry.
This code below seems to work for me, although I'm sure someone else
will be able to suggest how it can be simplified as it looks
ridiculously over-complicated.
Also, the names have changed since your example, but I'm sure you'll
get the idea.
-- Outer query to sum home and away games and order by total points
select [name], sum(points) points
from
(
-- Top half of subquery to get points for home games
select t.[name], sum(s.home) points
from scores s
join fixes f on s.fixid = f.fixid
join teams t on f.homeid = t.teamid
group by t.[name]
union all
-- Second half of subquery to get points for away games
select t.[name], sum(s.away) points
from scores s
join fixes f on s.fixid = f.fixid
join teams t on f.awayid = t.teamid
group by t.[name]
) as sub
group by [name]
order by points desc|||Bobbo,
Thanks for that. I can see how that work.
However, it doesn't cover:
"2) (which is where the interesting bit comes into it) where two or
more
teams have the same number of points the position is to be determined
by examining the results between the two or more teams in question."
So once having used your query to generate a list, this list then needs
to be sorted in the event that two teams share the same number of
points, by looking at the individual points against the opponent team
who share the same number of points.
Given the data originally specified I don't think that your query will
generate the output table as shown earlier.
Getting there though!
Thanks
Simon|||The following code may do it for you:
set nocount on
create table teams
(
id int primary key
, name varchar (20) not null
)
go
insert teams values (1, 'Team A')
insert teams values (2, 'Team B')
insert teams values (3, 'Team C')
insert teams values (4, 'Team D')
go
create table fixtures
(
id int primary key
, home int not null
references teams (id)
, away int not null
references teams (id)
)
insert fixtures values (1, 1, 2)
insert fixtures values (2, 1, 3)
insert fixtures values (3, 1, 4)
insert fixtures values (4, 2, 1)
insert fixtures values (5, 2, 3)
insert fixtures values (6, 2, 4)
insert fixtures values (7, 3, 1)
insert fixtures values (8, 3, 2)
insert fixtures values (9, 3, 4)
insert fixtures values (10, 4, 1)
insert fixtures values (11, 4, 2)
insert fixtures values (12, 4, 3)
go
create table results
(
id int primary key
references fixtures (id)
, home int not null
, away int not null
)
go
insert results values (1, 1, 3)
insert results values (2, 4, 0)
insert results values (3, 2, 2)
insert results values (4, 2, 2)
insert results values (5, 0, 4)
insert results values (6, 1, 3)
insert results values (7, 3, 1)
insert results values (8, 2, 2)
insert results values (9, 4, 0)
insert results values (10, 2, 2)
insert results values (11, 0, 4)
insert results values (12, 1, 3)
go
with totals (teamid, total, wins)
as
(
select
case type
when 1 then f.home
when 2 then f.away
end
, sum (case type
when 1 then r.home
when 2 then r.away
end)
, sum (case
when type = 1 and sign (r.home - r.away) > 0 then 1
when type = 2 and sign (r.away - r.home) > 0 then 1
else 0
end)
from
fixtures f
join
results r on r.id = f.id
cross join
(
select 1 union all
select 2
) as types (type)
group by
case type
when 1 then f.home
when 2 then f.away
end
)
select
t2.name
, t1.total
from
totals t1
join
teams t2 on t2.id = t1.teamid
order by
total desc
, wins desc
go
drop table results
drop table fixtures
drop table teams
The use of the CTE is SQL 2005, but you can do it without it - just use a
derived table instead.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
<simon.stockton@.baesystems.com> wrote in message
news:1155557745.509502.222530@.i42g2000cwa.googlegroups.com...
Dear All,
I have three tables:
teams table
id name
--
1 Team A
2 Team B
3 Team C
4 Team D
fixtures table
id home team id away team id
---
1 1 2
2 1 3
3 1 4
4 2 1
5 2 3
6 2 4
7 3 1
8 3 2
9 3 4
10 4 1
11 4 2
12 4 3
results table
fixture id home team result away team result
-----
1 1 3
2 4 0
3 2 2
4 2 2
5 0 4
6 1 3
7 3 1
8 2 2
9 4 0
10 2 2
11 0 4
12 1 3
what I want to be able to to is generate a table (list) of teams as the
output from a sql query in an order dependant on two things:
1) number of points won (highest number of points at the top) and 2)
(which is where the interesting bit comes into it) where two or more
teams have the same number of points the position is to be determined
by examining the results between the two or more teams in question.
So the output I am looking for in this example:
output table
team name total team points notes
----
Team C 16 1.
Team B 12 2.
Team A 12 3.
Team D 8 4.
Note: 1. Team C have 16 points therefore are the clear leaders of the
table
Note: 2. AvB = 1-3 BvA = 2-2 therefore Team B [5pts] is above Team A
[3pts]
Note: 3. see note 2
Note: 4. Team D have 8 points therefore are last in the table
My question is how do I write an sql query that will interrogate these
three tables to give my the output as described above.
Many Thanks
Simon