Dimension Security

Hi,

I have Created Dimension Security by restricting the user to see only his information whoever login to the system by creating a new role. It is working fine by checking the cubes by changing the role through change user option but when the cube was integrate it to any Analysis services reporting tool(Excel, Proclarity) inspite of the roles mentioned it displays the records of all the user.

Can anyone please help me out in this situation?

guessing wildy...

if it can identify the user, they get a restricted view

if it cannot identify the user, they see everything

maybe you need to switch off some kind of anonymous access

maybe it cannot identify the user when they are coming in via one of these tools? is it possible to display the username somewhere so you can at least dis/prove this?

|||

Hi adolf,

Thanks for the reply.....

Hence we are getting the analysis services cubes to any of this tools by accessing the server directly and any of the processing in cube can be done only in server and the tool is used to only show the data. so i hope there is nothing to do with the reporting tool regarding security. something to be done with in the cube

Dimension Question

I have a table that consists of:
companyid
name
industrycd
subindustrycd
regioncd
when creating the company dimension, should I be linking to other dimensions
(i.e. region, subindustry) or include the names of regions/subindustries
within the company dimension?
One of the primary benefits of OLAP is allowing users to drill down and
drill up through fact data using related dimension structures; see...
http://www.intelligententerprise.com/030320/605warehouse1_2.jhtml
So if there is a natural hierarchy of
Industry -> Sub Industry -> Company
then you would generally make use of that in your dimension to allow
this drill down and drill up to occur.
As the relationship between Industry and Region is likely to be
many-to-many; a second Region -> Company dimension would be the
simplest option to include this hierarchy.
A good resource for dimensional modelling concepts is...
http://www.rkimball.com/html/articlesFundamental.html
JP wrote:
> I have a table that consists of:
> companyid
> name
> industrycd
> subindustrycd
> regioncd
> when creating the company dimension, should I be linking to other dimensions
> (i.e. region, subindustry) or include the names of regions/subindustries
> within the company dimension?

Dimension Question

I have a table that consists of:
companyid
name
industrycd
subindustrycd
regioncd
when creating the company dimension, should I be linking to other dimensions
(i.e. region, subindustry) or include the names of regions/subindustries
within the company dimension?One of the primary benefits of OLAP is allowing users to drill down and
drill up through fact data using related dimension structures; see...
http://www.intelligententerprise.co...ehouse1_2.jhtml
So if there is a natural hierarchy of
Industry -> Sub Industry -> Company
then you would generally make use of that in your dimension to allow
this drill down and drill up to occur.
As the relationship between Industry and Region is likely to be
many-to-many; a second Region -> Company dimension would be the
simplest option to include this hierarchy.
A good resource for dimensional modelling concepts is...
http://www.rkimball.com/html/articlesFundamental.html
--
JP wrote:
> I have a table that consists of:
> companyid
> name
> industrycd
> subindustrycd
> regioncd
> when creating the company dimension, should I be linking to other dimensio
ns
> (i.e. region, subindustry) or include the names of regions/subindustries
> within the company dimension?

dimension question

I'm creating a datawarehouse and I have a question on a date dimension. My
users want to see the date formatted as such yyyy-mm-dd so they can sort on
it. How can something like this be possible? I never did this so I'm new to
the datawarehouse - cubes - dimension things.On 15.03.2007 14:00, John wrote:
> I'm creating a datawarehouse and I have a question on a date dimension. My
> users want to see the date formatted as such yyyy-mm-dd so they can sort o
n
> it. How can something like this be possible? I never did this so I'm new t
o
> the datawarehouse - cubes - dimension things.
This is not really DWH specific. Normally output formatting is client
work. You could add a calculated column or create a view that does the
conversion of a timestamp to a particular time format.
robert

Dimension Creation Problem

I am currently developing data warehouse design documentation for our compan
y as our first step in creating a data warehouse. During the design I ran i
nto an issue:
Our users want to answer this question: "What are our Export sales?"
Now that is a tricky question and I'll explain why...Export sales is the com
bination of sales for all products with the Product Type of Export and any s
ales (for any product) that are of type Export. I am stumped on how to acco
midate this very necessary
question and many just like it. How do I design my dimensions so that I can
sum based on 2 different dimensions into one answer?
I am sorry if this is a simple question, but for some reason it has me scrat
ching my head.You can either use a view or design your dim tables to accomodate
Ray Higdon MCSE, MCDBA, CCNA
--
"Jason Fischer" <jfischer@.bi-vetmedica.com> wrote in message
news:AC8E6672-7EAB-4DCB-A9AB-38F02E2A4ACC@.microsoft.com...
> I am currently developing data warehouse design documentation for our
company as our first step in creating a data warehouse. During the design I
ran into an issue:
> Our users want to answer this question: "What are our Export sales?"
> Now that is a tricky question and I'll explain why...Export sales is the
combination of sales for all products with the Product Type of Export and
any sales (for any product) that are of type Export. I am stumped on how to
accomidate this very necessary question and many just like it. How do I
design my dimensions so that I can sum based on 2 different dimensions into
one answer?
> I am sorry if this is a simple question, but for some reason it has me
scratching my head.|||I think you need two dimension tables - salestype and product(which contains
product type field).
You also need one fact table - sales with foreign key to product and one to
salestype.
so when you want to ask question "What are the Export Sales", you can query
fact table sales where salestype =
export and product type = export.
-- Jason Fischer wrote: --
I am currently developing data warehouse design documentation for our compan
y as our first step in creating a data warehouse. During the design I ran i
nto an issue:
Our users want to answer this question: "What are our Export sales?"
Now that is a tricky question and I'll explain why...Export sales is the com
bination of sales for all products with the Product Type of Export and any s
ales (for any product) that are of type Export. I am stumped on how to acco
midate this very neces
sary question and many just like it. How do I design my dimensions so that
I can sum based on 2 different dimensions into one answer?
I am sorry if this is a simple question, but for some reason it has me scrat
ching my head.

Dimension Access for Roles in AS 2005

When creating a role in AS 2005 and specifying dimenison access (on the Dimensions tab of the role definition in BI Dev Studio), why do some dimensions have the option of "None" as access level while others only have "Read" and "Read/Write"?

For example, in looking at the Adventure Works sample solution for AS 2005, if you add a role and then go to the Dimensions tab of the role definition, you'll see that Account, Department, Organization, and Scenario only have "Read" and "Read/Write" as access options. All the others have those two as well as "None".

At first, I thought that it was because of some unique setting for those four dimensions. But I haven't found anything just by browsing their definitions. Then I thought it was perhaps due to some unique usage of those four dimensions within the cube design. They are all used with the Financial Reporting measure group, but so is the Date dimension and it has "None" as an access option.

Anyone have any ideas? Probably something simple that I'm just overlooking, but I'd like to know why I can't set any of these to "None".

Thanks!

Dave Fackler

Did you ever find an answer to this? I'm having the same issue.

Dimension Access for Roles in AS 2005

When creating a role in AS 2005 and specifying dimenison access (on the Dimensions tab of the role definition in BI Dev Studio), why do some dimensions have the option of "None" as access level while others only have "Read" and "Read/Write"?

For example, in looking at the Adventure Works sample solution for AS 2005, if you add a role and then go to the Dimensions tab of the role definition, you'll see that Account, Department, Organization, and Scenario only have "Read" and "Read/Write" as access options. All the others have those two as well as "None".

At first, I thought that it was because of some unique setting for those four dimensions. But I haven't found anything just by browsing their definitions. Then I thought it was perhaps due to some unique usage of those four dimensions within the cube design. They are all used with the Financial Reporting measure group, but so is the Date dimension and it has "None" as an access option.

Anyone have any ideas? Probably something simple that I'm just overlooking, but I'd like to know why I can't set any of these to "None".

Thanks!

Dave Fackler

Did you ever find an answer to this? I'm having the same issue.

Difficulty in creating Time Dimension - SSAS

Hello all,

I am familiar with Analysis Services 2000 in which creating a time dimension is very easy.

In SSAS (2005) , i have a table in which there is field for DateTime which i want to use as time dimesion.

In time dimension wizard , it asks for the column for year , month etc (which is created by default in AS 2000) .

Since i have only one column having datetime datatype, how should i proceed

by creating heirarchy (year,month ,day - same as AS 2000).

Thanks,

deepti

i

You can create a stored procedure that populate a table with "time elements". See this example where I created a procedure that add rows into a table named CALSTD:

create procedure AddCalendar @.nYear int as

if not exists(select * from sysobjects where name='calstd')

CREATE TABLE [dbo].[CalStd](

[Data] [datetime],

[Data_lunii] [datetime],

[An] [smallint],

[Luna] [smallint],

[LunaAlfa] [char](15) ,

[Zi] [smallint],

[Saptamana] [smallint],

[Trimestru] [smallint],

[Zi_alfa] [char](10),

[Camp1] [char](10),

[Camp2] [char](10),

[Camp3] [char](10),

[Fel_zi] [char](1)

) ON [PRIMARY]

DECLARE @.I INT,@.DDATA DATETIME,@.DDATAL DATETIME,@.DDATAST DATETIME,@.NAN INT,@.nZi int,@.cZiChar char(10),@.cLunaAlfa char(15),@.nZileAn int

DELETE FROM CALSTD WHERE YEAR(DATA)=@.NAN

set @.nan=@.nyear

SET @.I=0

SET @.DDATAST=CONVERT(DATETIME,'01/01/'+RTRIM(STR(@.NAN)))

if (@.NAN%4)=0 and (@.nAn%400)<>0

SET @.nZileAn=366

else

set @.nZileAn=365

WHILE @.I<@.nZileAn

BEGIN

SET @.DDATA=DATEADD(DAY,@.i,@.DDATAST)

SET @.DDATAL=dateadd(day, -day(dateadd(month, 1, @.dData)), dateadd(month, 1, @.dData))

Set @.nZi=datepart(weekday,@.dData)

set @.cZiChar=(case when @.nZi=1 then 'Sunday'

when @.nZi=2 then 'Monday'

when @.nZi=3 then 'Tuesday'

when @.nZi=4 then 'Wednesday'

when @.nZi=5 then 'Thursday'

when @.nZi=6 then 'Friday'

else 'Saturday' end)

set @.cLunaAlfa=(case when datepart(month,@.dData)=1 then 'January'

when datepart(month,@.dData)=2 then 'February'

when datepart(month,@.dData)=3 then 'March'

when datepart(month,@.dData)=4 then 'April'

when datepart(month,@.dData)=5 then 'May'

when datepart(month,@.dData)=6 then 'June'

when datepart(month,@.dData)=7 then 'July'

when datepart(month,@.dData)=8 then 'August'

when datepart(month,@.dData)=9 then 'September'

when datepart(month,@.dData)=10 then 'Octomber'

when datepart(month,@.dData)=11 then 'November'

else 'December'

end)

INSERT INTO CALSTD(Data,Data_lunii,An,Luna,LunaAlfa,Zi,Saptamana,Trimestru,Zi_alfa,Camp1,Camp2,Camp3,Fel_zi)

VALUES(@.dData,@.dDatal,datepart(year,@.dData),datepart(month,@.dData),@.cLunaAlfa,datepart(day,@.dData),datepart(week,@.dData),datepart(quarter,@.dData),@.cZiChar,'','','','L')

SET @.I=@.I+1

END

|||

Thanks crysty that was really helpful.

regards,

deepti.

|||

Hi Deepti, I was in the same situation, but I found a simple way to achieve the Time Dimension Generation, please visit the following url, to know something about it. http://www.ssw.com.au/ssw/Standards/Rules/CreatingATimeDimensionIn10EasySteps.aspx

So for more information about sqlserver 2005, i'm building a blog with some resources at: http://grupohix.blogspot.com

Regards

FraGar

Difficulty in creating Time Dimension - SSAS

Hello all,

I am familiar with Analysis Services 2000 in which creating a time dimension is very easy.

In SSAS (2005) , i have a table in which there is field for DateTime which i want to use as time dimesion.

In time dimension wizard , it asks for the column for year , month etc (which is created by default in AS 2000) .

Since i have only one column having datetime datatype, how should i proceed

by creating heirarchy (year,month ,day - same as AS 2000).

Thanks,

deepti

i

You can create a stored procedure that populate a table with "time elements". See this example where I created a procedure that add rows into a table named CALSTD:

create procedure AddCalendar @.nYear int as

if not exists(select * from sysobjects where name='calstd')

CREATE TABLE [dbo].[CalStd](

[Data] [datetime],

[Data_lunii] [datetime],

[An] [smallint],

[Luna] [smallint],

[LunaAlfa] [char](15) ,

[Zi] [smallint],

[Saptamana] [smallint],

[Trimestru] [smallint],

[Zi_alfa] [char](10),

[Camp1] [char](10),

[Camp2] [char](10),

[Camp3] [char](10),

[Fel_zi] [char](1)

) ON [PRIMARY]

DECLARE @.I INT,@.DDATA DATETIME,@.DDATAL DATETIME,@.DDATAST DATETIME,@.NAN INT,@.nZi int,@.cZiChar char(10),@.cLunaAlfa char(15),@.nZileAn int

DELETE FROM CALSTD WHERE YEAR(DATA)=@.NAN

set @.nan=@.nyear

SET @.I=0

SET @.DDATAST=CONVERT(DATETIME,'01/01/'+RTRIM(STR(@.NAN)))

if (@.NAN%4)=0 and (@.nAn%400)<>0

SET @.nZileAn=366

else

set @.nZileAn=365

WHILE @.I<@.nZileAn

BEGIN

SET @.DDATA=DATEADD(DAY,@.i,@.DDATAST)

SET @.DDATAL=dateadd(day, -day(dateadd(month, 1, @.dData)), dateadd(month, 1, @.dData))

Set @.nZi=datepart(weekday,@.dData)

set @.cZiChar=(case when @.nZi=1 then 'Sunday'

when @.nZi=2 then 'Monday'

when @.nZi=3 then 'Tuesday'

when @.nZi=4 then 'Wednesday'

when @.nZi=5 then 'Thursday'

when @.nZi=6 then 'Friday'

else 'Saturday' end)

set @.cLunaAlfa=(case when datepart(month,@.dData)=1 then 'January'

when datepart(month,@.dData)=2 then 'February'

when datepart(month,@.dData)=3 then 'March'

when datepart(month,@.dData)=4 then 'April'

when datepart(month,@.dData)=5 then 'May'

when datepart(month,@.dData)=6 then 'June'

when datepart(month,@.dData)=7 then 'July'

when datepart(month,@.dData)=8 then 'August'

when datepart(month,@.dData)=9 then 'September'

when datepart(month,@.dData)=10 then 'Octomber'

when datepart(month,@.dData)=11 then 'November'

else 'December'

end)

INSERT INTO CALSTD(Data,Data_lunii,An,Luna,LunaAlfa,Zi,Saptamana,Trimestru,Zi_alfa,Camp1,Camp2,Camp3,Fel_zi)

VALUES(@.dData,@.dDatal,datepart(year,@.dData),datepart(month,@.dData),@.cLunaAlfa,datepart(day,@.dData),datepart(week,@.dData),datepart(quarter,@.dData),@.cZiChar,'','','','L')

SET @.I=@.I+1

END

|||

Thanks crysty that was really helpful.

regards,

deepti.

|||

Hi Deepti, I was in the same situation, but I found a simple way to achieve the Time Dimension Generation, please visit the following url, to know something about it. http://www.ssw.com.au/ssw/Standards/Rules/CreatingATimeDimensionIn10EasySteps.aspx

So for more information about sqlserver 2005, i'm building a blog with some resources at: http://grupohix.blogspot.com

Regards

FraGar

Different Header on Second Page

I have a report that requires a different header starting on the second page. I tried creating the header for page one in the body of the report, then creating the header for page two in the actual report header and setting PrintOnFirstPage to false. Unfortunately, this leaves a large space at the top of the first page, which is not acceptable to my users.

If there was a way to create a list box header that repeats at the top of each page, that would solve this problem as well, but I can't seem to find a way to do that either.

Any suggestions would be GREATLY appreciated.

You can use an iif statement in the testboxes of your header to change the contents depending on the global variable for page. If you need totally different header then use the hidden property to hide one header and display the other according to which page it is on.|||

Thank you. I actually tried something similar with a textbox in the body of the report and got the following error:

The Hidden expression for the textbox ‘textbox31’ refers to the global variable PageNumber or TotalPages. These global variables can be used only in the page header and page footer.

It didn't occur to me to try putting the textbox in the report header, but that works!

Thanks again!