Dimensions with Startdate / Enddate fields

Hi,

I would like to know what is the best practice to manage Dimensions with StartDate / EndDate fields, in SSAS.

Regards

Ayzan

Well first, is it possible to build dimensions with time dependance ?

Regards

Ayzan

Dimensional Modelling and Userbased Reporting

We are using SSAS 2005 for the cubes and reports will be created in Reporting Services as well as Proclarity Desktop Professional (all using windows integrated authentication). The reports will be displayed using a Sharepoint portal, again using the windows integrated authentication.

We have a Fact Table like FactRevenues which has revenueamount and a few other measures.

It is linked to 2 Dimension tables - DimProjects and DimCustomers through ProjectCode and CustomerCode.

DimProjects contains 2 dimension fields (apart from many others) - ProjectMgr and ProjectDir.

Similarly DimCustomers contains 3 fields - AccountMgr,AccountDir and EngagementDir.

We have an User Dimension which is the referenced dimension. DimUser contains UserID and other details of the user. We store the NT login(or at least something from which NT login can be obtained) in the UserID.

The five fields mentioned above - ProjectMgr, ProjectDir,AccountMgr, AccountDir and EngagementDir stores the UserID and are linked to the UserID in the User Dimension.

We have created hierarchies/pseudo dimensions which link like RevenueFact-ProjectMgr-User, RevenueFact-ProjectDir-User, RevenueFact-AccountMgr-User,RevenueFact-AccountDir-User and RevenueFact-EngagementDir-User. So, for the MDX expression we can use these to check against the logged in User.

The Problem:

Our requirement is, the person logged in should be able to see only the details (measure values) that are relevant to him/her. Basically, the aggregate of RevenueAmount should be done based on the following conditions:

If the logged in user is a Project Manager for some projects, then the person should only be able to see the aggregated measure for the projects for which he/she is the manager (checked from the ProjectMgr field). Similarly for ProjectDir.

If the logged in user is an Account Manager for some clients, then the person should only be able to see the aggregated measure for the clients that he/she handles (checked from the AccountMgrfield). Similarly for AccountDir and EngagementDir.

There are possibilities, though remote, that a single person can be AccountDir as well as ProjectDir. Basically, the system should support the possiblity for a person to be in any combination of the five fields. This shouldn't be an issue as this will be taken care of automatically once we set up the dynamic dimension security for each of these dimensions.

There will a different set of users - SuperUser/Admins - who will be able to view all the details without any restrictions. We HAVE NO ISSUES WITH THIS AS we created a role specifically for Admins without any restrictions and a role for others (everyone) which will be applied with the dynamic dimension security.

What we tried:

We have actually gone through the links that you have sent earlier, when we were trying to solve the issue. But it seems like we are missing some small thing.

Our MDX for dimension security

Filter( [DIMIRLINE -IR FORM REF NO - IR H CUST - ACCOUNT MGR].[DIMUSERS].[DIMUSERS] = UserName)

UserName supposedly being the function for obtaining the currently logged in user.

But this didn’t seem to restrict the users.

Your syntax for the filter function does not look correct. You need to pass it a set and then the criteria with which to filter that set. I would expect to see something more like the following:

Filter(

[DIMIRLINE -IR FORM REF NO - IR H CUST - ACCOUNT MGR].[DIMUSERS].[DIMUSERS].members

, [DIMIRLINE -IR FORM REF NO - IR H CUST - ACCOUNT MGR].[DIMUSERS].[DIMUSERS].CurrentMember.Name = UserName()

)

|||

This following is the table I created through SSAS browser having UserID,EGName and Mothwisesales Column

where USerID is the member of [Account Mgr].[UsersID] Attribute

I want to show the only row when perticalur User LogsIn(NTlogin) insted of showing the details of all User.He should see only his details(I want to restrict this in Cube level rather on SSRS/Proclarity).

I created a Hierachy i.e UserHierachy -->Role->UserID

Role attribute always contains{User,Manager}.If UserID is belongs to User Role He ll be see only his details from NTLogin and if UserID is belongs to ManagerRole He ll be see all the userdetails.

Please help me regarding this.

Thank u

with regards

Saroj

userID

Mar

chandrashekar.cs

EG11

$700.00

EG12

$495.63

EG14

$1,500.00

EG21

$15,600.83

EG24

$0.83

EG26

$8,726.37

EG34

$2,103.34

EG39

$300.00

EG3P

$197.55

EG41

$5,866.68

EG42

$16,900.00

EG51

$2,200.00

EG52

$1,400.83

EG53

$0.00

EG61

$11,757.92

EG62

$199.96

EG65

$363.40

EG72

$3,400.00

EG74

$2,400.00

madhankumar.s

$228,385.98

ramaprasath.mss

$6,402.35

sarojkumar.nishanka

$6,308.43

sethumadhavan.sb

$284,112.19

sql

dimension records exceeds 64000

I am facing a problem in SSAS, unlike Analysis service 2000 , I cannot find any grouping under level properties for a dimension. Actually one of the dimension is too big, some 1600000 records are there and no hierarchy as such. only way is to club by first alphabet,which we were using in 2000. i.e. by using the grouping property. but in SSAS 2005 I cannot find any, how do i overcome this, otherwise beyond 64000 records its not showing

There has been a change in terminology in AS2005. Instead of grouping this functionality is refered as Discretization.

You need to create a new attribute based on the same column as your main attribute.

Set the DiscretizationMethod to Automatic. ( you can try any other method find if you like it better).

Also take a look at the NamingTemplate property of the attribute. You should be able to use it to get Analysis Server to generate custom names for your grouping (discretized) attribute.

Here is BOL topic on the matter: http://msdn2.microsoft.com/en-us/library/ms174810.aspx

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

Dimension Properties

I am using SSAS 2005. I have a Dimension table that is based on the entire data source and then 2 Fact tables that set the data into two groups. When I browse the data for Fact Table 1 in SSAS 2005, I am getting missing dimension values for all the data in Fact table 2. Is there a join property that will tell SSAS to only use data from the Dimension table that is also in the Fact table? I have a primary key on the Dimension table that relates the dimension table to the Fact tables.

S

You will probably need to create a view in relational database or named query in DSV to build a join that only brings data you would like to see in Analysis Services.

You should not rely on Analysis Services to solve data integrity problems.

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

Dimension Name Column Format Property SSAS 2005

I have tried entering different forms of syntax into the 'Format' property underneath the 'Name Column' property for a specific attribute within a dimension and it never seems to change the output when I view the attribute within the 'Browser' tab of the dimension. I tested this on the Adventure Works DW and I am unable to change the attribute format. Here is an example:

1. With the Adventure Works DW, open the 'Employee' dimension and modify the 'Birth Date' attribute's format property underneath name column. I have entered "d", format("DimEmployee"."BirthDate", 'mm/dd/yyyy'), and convert(varchar, "DimEmployee"."BirthDate", 101).

2. Process the dimension and click on the browser tab and view the 'Birth Date' hierarchy.

Has anyone had any luck using this 'Format' property for the 'Name Column' of an attribute? I believe you could easily do this in AS 2000, so I am wondering what the trick is in SSAS 2005. I would think that you could use this property, but I guess I need to know what the proper syntax is. I know that I could easily modify the data source view, but I want to know how to be able to do this in the future if needed.

I have checked on the web and in BOL and haven't found any reference information for this property and how to use it. If anyone knows of any documentation please let me know. I will take a look at the SQL 2008 BOL and see if that has anything new.

Thanks.

I just got a response back from Microsoft and this is what I had kind of figured because no matter what you type in this property it never would produce an error or change the results of the text.

The "Format" string for Attribute names is a stub for a later addon and is not implemented. Attribute names will only accept WChar types. Any formatting should be done either in the data source view as a "Named Calculation" or in the source table/view on the relational source.

Dimension Name Column Format Property SSAS 2005

I have tried entering different forms of syntax into the 'Format' property underneath the 'Name Column' property for a specific attribute within a dimension and it never seems to change the output when I view the attribute within the 'Browser' tab of the dimension. I tested this on the Adventure Works DW and I am unable to change the attribute format. Here is an example:

1. With the Adventure Works DW, open the 'Employee' dimension and modify the 'Birth Date' attribute's format property underneath name column. I have entered "d", format("DimEmployee"."BirthDate", 'mm/dd/yyyy'), and convert(varchar, "DimEmployee"."BirthDate", 101).

2. Process the dimension and click on the browser tab and view the 'Birth Date' hierarchy.

Has anyone had any luck using this 'Format' property for the 'Name Column' of an attribute? I believe you could easily do this in AS 2000, so I am wondering what the trick is in SSAS 2005. I would think that you could use this property, but I guess I need to know what the proper syntax is. I know that I could easily modify the data source view, but I want to know how to be able to do this in the future if needed.

I have checked on the web and in BOL and haven't found any reference information for this property and how to use it. If anyone knows of any documentation please let me know. I will take a look at the SQL 2008 BOL and see if that has anything new.

Thanks.

I just got a response back from Microsoft and this is what I had kind of figured because no matter what you type in this property it never would produce an error or change the results of the text.

The "Format" string for Attribute names is a stub for a later addon and is not implemented. Attribute names will only accept WChar types. Any formatting should be done either in the data source view as a "Named Calculation" or in the source table/view on the relational source.

Dimension [] and Measure [] have no relation in Excel Addin

Hi,

I am kind of new to SSAS 2005 but done some work on sql 2000 MSOLAP. There is a difference I spotted between the product which is annoying me and not sure if there is a solution for the same.

In SQL 2000 MSOLAP i created one cube which had only is related to active outlets listing of company only related to one dimension.

I added this to virtual cube which had many other cubes and many other dimensions. Based on virtual cubes I created reports and caculated measures using the active outlet listing without having to worry about it being linked to other dimensions. It always used to generate data in excel addin where many dimensions where put in page and row sections.

Now in SSAS I created it as part of standard cube related only to one dimension but if use it excel addin with other dimensions it gives me an error saying the Dimension [Year] and Measure [Active outlet listing] has no relationship.

How do I handle the above. Any quick help will be appreciated.

Thanks & Regards

Ramnath

This issue was discussed some time back in Chris Webb's blog:

"I did spot a problem that I've seen in other AS2005-enabled clients and which I hope won't turn into a trend. I found it by creating a report using Adventure Works, putting the measure [Internet Sales Amount] on columns and trying to put something from the Geography dimension on pages, which resulted in a dialog box informing me that the Geography dimension was unrelated to the [Internet Sales] measure group and stopping me from completing the operation. Now in 99% of cubes this would be a good thing to do, but I've already built a few cubes where I have dimensions that have no relation to any measure group but where selections on them do impact calculations (think solving the start/end date problem, where you might want to create an end date dimension with no relation to a measure group); of course, this client feature stops you from being able to design cubes in this way."

And there was this comment suggesting a work-around, if it helps you:

Mosha

"In the meantime, the workaround would be to create calculated measure which redirects to the physical measure ?"

December 29 12:10 AM
(http://www.mosha.com/msolap)

|||

Thanks Deepak,

I actually tried the work around before reporting this as a problem. The work around becomes very cumbersome when we have large number for hierarchies for one dimension.

I found the easier way by creating associations for all measures groups to all dimensions. The association is at the highlest level in all dimension structures.

How can we report this issue to Microsoft and get help in fixing it in the Excel addin. Does any body know if there is going to be maintenance release for this Excel addin.

The Excel addin link page also does not work so people are only able to see the FAQ section and download section of this addin. Other details of this addin are not available.

Thanks For all the support.

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