Question to ytd()

Hi,

Ihave a Time-Dimension with 2 hierarchies: (a) Year - Semester - Quarter - Month and (b) Year -Week.

Than I have a calculated member in my Cube like:

CREATE MEMBER CURRENTCUBE.[MEASURES].[Sales Volume KG (kum.)]
AS Sum(ytd(), [Measures].[Sales Volume KG]),
FORMAT_STRING = "#,#.00",
VISIBLE = 1 ;

When I browse the cube and I use the (a) hierarchy on the colums, all works fine; I get the accumulated sums over Semesters, Quarters, Month. When I use the (b) hierarchy, in each cell I get the normal week-sum and not the accumulated. What's my mistake?

Thanks
Hans

Not sure whether you've read the "Time calculations in UDM: Parallel Period" entry in Mosha's blog, but it discusses approaches to designing time calculations which work with multiple time hierarchies. In your specific case, YTD() may always be using hierarchy (a) by default, since you didn't specify a parameter. And, as explained in the blog entry: "If Time dimension has hierarchies which share some attributes, then it might not be possible to say by which hierarchy user browsed". So, my guess is that, when a week is selected on hierarchy (b), the current member of hierachy (a) becomes the corresponding year, in which case YTD() returns a set with that single member.

If you post more info on the relationships and shared attributes between the 2 time hierarchies, maybe someone here can give a good solution?

|||

Hi Deepak,

Thanks for the reply. My hierarchies have the following relationships (from top down; the indent-part are the attribute relationships):

Name: [Date - Year]
- Year
- Semester
- SemesterDescription
- Year
- Quarter
- QuarterDescription
- Semester
-Month
- MonthDescription
- Quarter

Name: [Date - Week]
- Year
- Week
- WeekDescription
- Year

The only shared attribute between the 2 hierarchies is "Year". The [Date] Dimension is a "hand-made" Table (with fields for FullAlternateDate, Year, Semester, ...) and not the built-in on of AS2005.

Hope this helps for finding an answer.

Thanks
Hans

|||

Hi Hans,

Am I correct in assuming that the key attribute of the dimension is [Date], though it isn't explicitly shown in any hierarchy - and that [Date] is also the granularity attribute for the measure group(s) in question?

|||

Hi Deepak,

The key attribute of the Dimension is [TimeKey] which is a runnung number (1 ... 9497) from 01.01.2000 until 31.12.2025 and the TimeKey ist the granulatiy attribute. You are correct.

The DimTime is handmade (by me with a small Microsoft Access Function) and has the following fields:

TimeKey (PrimaryKey), FullDateAlternateKey, DayNumberOfWeek, GermanDayNameOfWeek, EnglishDayNameOfWeek, SpanishDayNameOfWeek, FrenchDayNameOfWeek, DayNumberOfMonth, DayNumberOfYear, WeekNumberOfYear, GermanMonthName, EnglishMonthName, SpanishMonthName, FrenchMonthName, MonthNumberOfYear, CalendarQuarter, CalendarYear, CalendarSemester, FiscalQuarter, FiscalYear, FiscalSemester

Example 1	01.01.2000 00:00:00	7	Samstag	Saturday	Sábado	Samedi	1	1	52	J?nner	January	Enero	Janvier	1	1	2000	1	1	2000	1
2	02.01.2000 00:00:00	1	Sonntag	Sunday	Domingo	Dimanche	2	2	1	J?nner	January	Enero	Janvier	1	1	2000	1	1	2000	1

Here is the Hierarchy with the original Names and not the abbreviations:

Name: [Date - Year]
- CalendarYear
- CalendarSemester
- SemesterDescription
- CalendarYear
- CalendarQuarter
- QuarterDescription
- CalendarSemester
- MonthNumberOfYear
- MonthDescription
- CalendarQuarter

Name: [Date - Week]
- CalendarYear
- WeekNumberOfYear
- WeekDescription
- CalendarYear

Thanks for your help!

Hans

|||

Hi Hans,

Based on the info for the [Date] dimension above, maybe something like this will work:

CREATE MEMBER CURRENTCUBE.[MEASURES].[Sales Volume KG (kum.)]
AS Sum(YTD([Date].[Date - Year].CurrentMember) *

YTD([Date].[Date - Week].CurrentMember),

[Measures].[Sales Volume KG])

|||

Hello Deepak,

Thanks a lot, this works. But please, can you tell me the background, why this crossjoin work? Is it, because if the current hierarchy is Weeks, the other hierarchy is "Null" and reverse?

Thanks
Hans

|||Hans, on the contrary, from what I can tell, when you select a week in [Date - Week], a year member is selected in [Date - Year], because the year attribute is shared between the 2 hierarchies. However, this works with YTD() calculations, because YTD(year member) returns that year member. You could confirm this by adding calculated measures to the query like [HierYearName]: [Date].[Date - Year].CurrentMember.Name and [HierWeekName]: [Date].[Date - Week].CurrentMember.Name.

question on views

Hello,
A little background...
I have a cube that automatically refreshes every 10 minutes and pulls it's
data from a view ('ViewA') in sql server 2000.
The cube takes around a minute and a half to completely refresh. During
this time, in sql server, one of my other views ('ViewC') starts to return
inconsistent results in the form of varying numbers of records in its result
set. The result set is somewhere in the neighborhood of 1,000 records, and
varies by anywhere from 40 to 50 records each time it is run during the cube
refresh.
A bit more info...
The cube pulls it's data from ViewA with these characteristics (simplified
for relevance):
ViewA selects * from ViewB (nolock) which, in turn, selects * from
MyTable (nolock)
As mentioned earlier, while the cube is refreshing, ViewC returns an
inconsistent number of records. ViewC has these characteristcs (simplified
for relevance):
ViewC selects * from MyTable (nolock)
It seems to me that the problem may have to do with record locking, but as
shown above, i am using (nolock) to prevent the issuing of any shared locks
while ViewA is running. So what is it about this cube refresh that prevents
one of my other views from gaining access to, and completely skipping over,
some of the records?!
One hundred million points for this one ;)
Thanks
Nolock doesn't guarantee consistent data so you can expect
inconsistent results...it's not unusual. You can retrieve
before and after images of rows being updated, can read
uncommitted transactions.
It doesn't look like it's record locking itself that is the
cause the issue but rather using a hint that doesn't care
about other exclusive locks, allows dirty reads. You really
have to weigh out using the nolock hint with the
inconsistent results that you can end up with.
-Sue
On Mon, 17 Oct 2005 15:18:30 -0400, "Jesse Aufiero"
<jaufiero@.moaboil.com> wrote:

>Hello,
>A little background...
>I have a cube that automatically refreshes every 10 minutes and pulls it's
>data from a view ('ViewA') in sql server 2000.
>The cube takes around a minute and a half to completely refresh. During
>this time, in sql server, one of my other views ('ViewC') starts to return
>inconsistent results in the form of varying numbers of records in its result
>set. The result set is somewhere in the neighborhood of 1,000 records, and
>varies by anywhere from 40 to 50 records each time it is run during the cube
>refresh.
>A bit more info...
>The cube pulls it's data from ViewA with these characteristics (simplified
>for relevance):
> ViewA selects * from ViewB (nolock) which, in turn, selects * from
>MyTable (nolock)
>As mentioned earlier, while the cube is refreshing, ViewC returns an
>inconsistent number of records. ViewC has these characteristcs (simplified
>for relevance):
> ViewC selects * from MyTable (nolock)
>It seems to me that the problem may have to do with record locking, but as
>shown above, i am using (nolock) to prevent the issuing of any shared locks
>while ViewA is running. So what is it about this cube refresh that prevents
>one of my other views from gaining access to, and completely skipping over,
>some of the records?!
>One hundred million points for this one ;)
>Thanks
>

question on views

Hello,
A little background...
I have a cube that automatically refreshes every 10 minutes and pulls it's
data from a view ('ViewA') in sql server 2000.
The cube takes around a minute and a half to completely refresh. During
this time, in sql server, one of my other views ('ViewC') starts to return
inconsistent results in the form of varying numbers of records in its result
set. The result set is somewhere in the neighborhood of 1,000 records, and
varies by anywhere from 40 to 50 records each time it is run during the cube
refresh.
A bit more info...
The cube pulls it's data from ViewA with these characteristics (simplified
for relevance):
ViewA selects * from ViewB (nolock) which, in turn, selects * from
MyTable (nolock)
As mentioned earlier, while the cube is refreshing, ViewC returns an
inconsistent number of records. ViewC has these characteristcs (simplified
for relevance):
ViewC selects * from MyTable (nolock)
It seems to me that the problem may have to do with record locking, but as
shown above, i am using (nolock) to prevent the issuing of any shared locks
while ViewA is running. So what is it about this cube refresh that prevents
one of my other views from gaining access to, and completely skipping over,
some of the records?!
One hundred million points for this one ;)
ThanksNolock doesn't guarantee consistent data so you can expect
inconsistent results...it's not unusual. You can retrieve
before and after images of rows being updated, can read
uncommitted transactions.
It doesn't look like it's record locking itself that is the
cause the issue but rather using a hint that doesn't care
about other exclusive locks, allows dirty reads. You really
have to weigh out using the nolock hint with the
inconsistent results that you can end up with.
-Sue
On Mon, 17 Oct 2005 15:18:30 -0400, "Jesse Aufiero"
<jaufiero@.moaboil.com> wrote:

>Hello,
>A little background...
>I have a cube that automatically refreshes every 10 minutes and pulls it's
>data from a view ('ViewA') in sql server 2000.
>The cube takes around a minute and a half to completely refresh. During
>this time, in sql server, one of my other views ('ViewC') starts to return
>inconsistent results in the form of varying numbers of records in its resul
t
>set. The result set is somewhere in the neighborhood of 1,000 records, and
>varies by anywhere from 40 to 50 records each time it is run during the cub
e
>refresh.
>A bit more info...
>The cube pulls it's data from ViewA with these characteristics (simplified
>for relevance):
> ViewA selects * from ViewB (nolock) which, in turn, selects * from
>MyTable (nolock)
>As mentioned earlier, while the cube is refreshing, ViewC returns an
>inconsistent number of records. ViewC has these characteristcs (simplified
>for relevance):
> ViewC selects * from MyTable (nolock)
>It seems to me that the problem may have to do with record locking, but as
>shown above, i am using (nolock) to prevent the issuing of any shared locks
>while ViewA is running. So what is it about this cube refresh that prevent
s
>one of my other views from gaining access to, and completely skipping over,
>some of the records?!
>One hundred million points for this one ;)
>Thanks
>

Question on URL action on cube objects

Hi, all experts here,

Thanks a lot for your kind attention.

I am having a question on the URL action on cube objects. The question is about the URL expression. It seems like we cant jsut pasted the URL there?

I am looking forward to hearing from you for your help.

With best regards,

Yours sincerely,

No, you need to supply an MDX expression which resolves to a url. So if you just want to hard-code a url in your action, you need to enclose it in double quotes, for example

"http://www.google.com"

HTH,

Chris

|||

Thanks a lot.

Best regards,

Question on time dimension and time intelligence

Hi, all experts here,

I am wondering how could we actually check the time dimension and time inteligence within a cube? I mean from what property of the cube or the analysis service project that I would be able to check if there is time dimension or time intelligence implemented accordingly?

Hopy it is clear for your advices and help.

Thank you in advance for your help and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

Helen999888 wrote:

Hi, all experts here,

I am wondering how could we actually check the time dimension and time inteligence within a cube? I mean from what property of the cube or the analysis service project that I would be able to check if there is time dimension or time intelligence implemented accordingly?

Hopy it is clear for your advices and help.

Thank you in advance for your help and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

Helen Please help me to solve the following problem

How can I format a dimesion datetime column . for example while browsing a cube, the value of the dimension column 'DateOrder' shows like that 2002-11-01 00:00:00.

I wan to get 01/11/2002 dd/mm/yyyy.

What I have to do. When I changed their cell value property as dd/mm/yyyy the value is not changed still it is showing as year/Date/Month

..Please to crrect my problem

with regards

Polachah

Question on shared cube dimensions and measure groups

Hi, all experts here,

Thank you very much for your kind attention.

I am wondering is there anyway of sharing dimensions and measure groups between different cubes? Like if we want to create a new cube, we dont need to reselect its data source and data source view, instead we can select dimensions and measure groups of other cubes?

Hope it is clear for your advices and I am looking forward to hearing from you shortly and thanks a lot in advance for your help.

With best regards,

Yours sincerely,

Hi Helen,

Dimensions can be shared between all cubes inside an AS2005 Database. In order to use dimensions and/or measure groups in multiple cubes you only need to create the cubes inside the same AS2005 database.

hth,

Eric

|||

Hi, Eric,

Thanks a lot for your kind attention.

But in BI studio, yes, I did create a new cube within the same analysis services project which no dount will deploy the cube to the same SSAS2005 database, but whenever I create a new cube with the cube creation wizard, I have to select the new data source view for new dimensions and measure groups again? Could you please shed me any light which UI is available to create new cube with dimensions and measure groups from existing cube?

Hope it is clear for your further advices and I am looking forward to hearing from you shortly and thanks a lot in advance for your help.

With best regards,

Yours sincerely,

|||

Hi,

The cube wizard allows you to use existing dimensions. In the "Identify Fact and Dimension Tables" page, select no dimension tables and in the next page, "Review Shared Dimensions", you can pick the existing dimensions to include in the new cube.

Adrian Dumitrascu

|||

Hi, Adrian,

Thanks a lot for your help.

With best regards,

Yours sincerely,

sql

Question on relationship between Cube and Fact tables?

Hi, all,

Sorry for this title which may be a bit confused. But what I am trying to ask is: Any best practices on fact tables to be included in a cube? Is it better to create a separate cube for each fact table and its related dimension tables or any other ideas to put several fact tables and their related dimension tables into one cube?

Thanks in advance for your kind advices.

With best regards,

Yours sincerely,

Helen,

First Analysis your requirement, is there any clubed information you desired from multiple Fact Tables, How may shared dimensions are among them.

How frequently U require that aggregated [from both Fact Table] information etc.

Simply analysis what can have U benifit when make a cube having multiple fact tables.

Or other way when there is no share facts between to fact table donot make Cube having multiple Fact Tables.

Reference is below:

Cube Design Best Practices

Well-designed cubes will better match the needs of your end-users. The following best practices can improve usability and also performance of the cube.

Avoid including unrelated measure groups in the same cube

Having many measure groups in a cube can adversely affect the query performance of the cube, even for queries that only use a single measure group. If your DSV contains many potential fact tables and they will not usually be used together, consider creating multiple smaller cubes containing topic areas that are used together. Then, you can enable occasional cross-topic querying by creating linked measure groups that join together all measure groups in a special cross-topic cube.

Bhudev

|||

Dear Helen,

Depends on your system requirements.

But probably you only need one cube, and I think it because in the SQL 2005 you can process parts of the cube separately, in spite of all the cube...

For example, one thing I usually do, is in the datasource view, in spite of use a table from database, I use a view based on this table with only the field I will need in the cube. Only in case we have a lot of fields that will not used...

Other thing is used named calculations, or the compute calculation in database...

Regards!

|||

Hi, Bhudeve,

Thanks for you advices.

Yes, as there are shared dimensions for different measure groups and you never know when and what the users would like to see on their side. Thus, to optimally meet the querying needs, always put them together in a cube? But we can always create partitions for different measure groups if we want?

Does it make any sense? Looking forward to hearing from more of your advices.

With best regards,

Yours sincerely,

|||

Hi, PedroCGD,

Thanks for your suggestions.

Are you saying you put all data together in a single cube? And then partition the cube on different measure groups? Thanks a lot and would like to hear more from you for that.

With best regards,

Yours sincerely,

|||

Yes!

And I will try to explain in my blog, how I'm doing in my project!

Each FactTable will be your measure group... with only the measures you need for your cube!

regards!!

|||

Hi, PedroCGD,

Thanks a lot for your kind advices.

With best regards,

Yours sincerely,

|||

Helen,

I'm not against to keep separate Cubes when I do not know the actual requirement. But still there is need to consider all Pros and Cons before completing Design Document for your data Warehouse.

OLAP Design Best Practices for Analysis Services 2005

http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/olapdbpssas2005.mspx#ELGAC

document states Having many measure groups in a cube can adversely affect the query performance of the cube, even for queries that only use a single measure group. If your DSV contains many potential fact tables and they will not usually be used together, consider creating multiple smaller cubes containing topic areas that are used together. Then, you can enable occasional cross-topic querying by creating linked measure groups that join together all measure groups in a special cross-topic cube.

Bhudev

|||

Hi, Bhudeve,

Thanks for that. Very helpful.

With best regards,

Yours sincerely,

Question on merging partitions

Hi, all experts here,

Now I am confusing about the need to merge partitions and the significant benefits of merging them.

As partitioning cube obviously helps improving cube processing time and improve query performance. But then what is the reason to merge partitions back to one single partition of a cube?

I am looking forward to hearing from you for your kind advices and thanks a lot in advance.

With best regards,

Yours sincerely,

(this is not the answer for the existence of the merging partitions feature, just a scenario)

There is a best practice for AS2005 to not have more than 5 small partitions in a measure group (a small partition having less than 50 MB or 2,000,000 rows). If testing on the particular configuration (depending on the hardware, the queries that needs to be optimized, ...) shows that indeed it's better to consolidate these small partitions, then merging partitions helps.

Adrian Dumitrascu

|||

Hi, Adrian,

Thank you for your advices.

With best regards,

Yours sincerely,

Question on large cube update processing

Hi, all experts,

Is there any way for us to process the updated cube partly ? In my case I only want to add KPIs to my cube which are related to a few measure groups. In this case, it seems like we have to process the whole partitions related to these measure groups which the new added KPIs are related to. But as to large cube with miliions of records, this could take a reasonable time. Therefore, I am wondering if there is any way that we are able to update and process only the new added properties such as these new added KPIs to the cube instead of having to process the large chunk of the cube data? I personally think it will be really great to have this feature.

Is there any way to achieve this in SQL Server 2005 Analysis Services? I am looking forward to hearing from you shortly and thanks a lot for your advices.

With best regards,

Yours sincerely,

Hi,

I think you just need to deploy your project.

Regards

|||

Hi,

Thanks. But as what I have always seen in my BIDS , whenever I deploy a project, the system process it first and then deploy it.

I am not quite sure what you really mean by deploy here if that is not the 'deploy' I understand.

Regards,

|||

Lucas is right, you just need to deploy without processing.

There is an option under the deployment settings of the project that sets whether the project is processed after it is deployed. I think the default setting is "Always Process", but you can change this to "Never" and the project will be deployed without initiating a processing operation.

For changes to things like the calc script and KPI's, you do not need to reprocess your data, deploying your changes is enough.

Question on dimension usage of cube

Hi, all experts here,

Thank you very much for your kind attention.

I am having a question on the dimension usage of cube. When we relate the dimensions to measure groups, is it a must we have to use the key of the dimension to relate to the measure groups? Otherwise the analysis services server will not be able to correctly aggregate the cube data? Thank you very much and I am looking foward to hearing from you shortly.

With best regards,

Yours sincerely,

Hello. Yes, you use the logical dimension key that you have in the datasource view. One exception is when you have a fact table with a month dimension and your time dimension have day/date as the lowest level. In the Adventure Works cube, that is part of the installation of SQL Server 2005, you have a relation like that between the date dimension and the Sales Targets measure group. That relation is defined between the Calendar Year and Calendar Quarter columns in the dimension usage tab.

HTH

Thomas Ivarsson

|||

Hi, Thomas,

Thanks a lot.

With best regards,

Yours sincerely,

Question on dimension types in a cube

Hi, all experts here,

Thanks a lot for your kind attention.

I am wondering dose it really matter if the dimension types we set for each dimension will really affect anything wihin a cube?(like normally the default dimension type is regular, but there are many other dimension types like promotion, product etc. ), so do these dimension types will really affect anything for the cube data? If so, like what would be affected and changed?

I am looking forward to hearing from you shortly and thank you very much in advance for your help and advances.

With best regards,

Yours sincerely,

The only two types which affect semantics are:

1. Time - for MDX functions such as YTD and for semiadditive measures

2. Account - for Account Intelligence

Everything else are metadata that client applications can use for UI and other purposes.

|||

Hi,

Thanks a lot.

With best regards,

Yours sincerely,

|||

Dear Helen

Pleasae help me.. How can I create an attribute dateoforder in Sales dimension ...

Please help ..

Question on data source and data source view of MOLAP cube

Hi, all experts here,

Thank you very much for your kind attention.

I am having a question on the data source and data source view of MOLAP Cube. As we know MOLAP Cube stores all data in MOLAP format, it does not have any thing to do with its source data base. As a result, I am wondering if we could actually remove the data source and data source view of the MOLAP cube? (in my case, no update is necessary to be informed from the underlying data source to the MOLAP cube)

Hope it is clear for your help.

I am looking forward to hearing from you for your advices and help.

With best regards,

Yours sincerely,

Hello again Helen.

You will always need a data source and a data source view for the cube.

What you can do, in order to create an off-line cube is to use local cubes, if this is the scenario you are thinking about?

With local cubes you create a local cube file that you can put on a laptop and analyze data without being connected to SSAS2005.

Both ProClarity and Excel2007 have this option.

HTH

Thomas Ivarsson

|||

Hi, Thomas,

Thank you for your very kind advices.

But I found that change of the data source view does not really reflect on the data source view of the cube though? I mean after I created the cube, and I edited the data source view, but when I clicked on the cube, its data source view remained as the one before I changed to the new data source view?

Also, I am a bit confused, as all the data for the cube has already stored in SSAS2005 Data base (MOLAP), so why we still need to care about the data source and data source view if we dont need to have any update from the underlying data source?

Thank you for your kind attention and I am looking forward to hearing from you shortly for your further advices.

With best regards,

Yours sincerely,

|||

Hi, Helen.

I am not sure about the scenario here? Do you mean that you will create the cube once and never process it again?

If new dimension records and new fact records arrives from the data source you will always need to process the cube. If you need to process you will need to have the data source view.

During a days work new transactions will be registered in the source system. You will have to do a new process of the cube in order to get them from the source system into the cube.

Are you making cubes for laptop users that they will use off-line? Or are you building cubes using real time OLAP?

Regards

Thomas Ivarsson

|||

Hi, Thomas,

Thank you for your reply. In my case, well, I do deploy the whole project containing the cube after I changed the data source and data source view, but the cube remains the same as before I did any change. Therefore, I thought data source and data source view does not have anything to do with the MOLAP cube after its first deployment to the analysis services server.

Does it work in this way? Or I miss out anything of it? Thank you and your advices are most welcome.

With best regards,

Yours sincerely,

|||

Hello Helen. If you make a change like adding an attribute in the data source (source table/view) or the data source view(=named calculation) this change will not be a part of the cube until you add the attribute in the dimension editor.

In order to make a change in a cube you must mapp new columns to the measure group or to the dimension in the cube/dimension editor.

The data source view do not automatically update the cube or the dimensions. If you do a refresh in the data source view(right click) all changes in the source database will exported to the data source view.

HTH

Thomas Ivarsson

|||

Hi, Thomas,

Thanks a lot for your kind advices.

It cleared my confusion over the above question.

Thanks again.

With best regards,

Yours sincerely,

Question on data source and data source view of MOLAP cube

Hi, all experts here,

Thank you very much for your kind attention.

I am having a question on the data source and data source view of MOLAP Cube. As we know MOLAP Cube stores all data in MOLAP format, it does not have any thing to do with its source data base. As a result, I am wondering if we could actually remove the data source and data source view of the MOLAP cube? (in my case, no update is necessary to be informed from the underlying data source to the MOLAP cube)

Hope it is clear for your help.

I am looking forward to hearing from you for your advices and help.

With best regards,

Yours sincerely,

Hello again Helen.

You will always need a data source and a data source view for the cube.

What you can do, in order to create an off-line cube is to use local cubes, if this is the scenario you are thinking about?

With local cubes you create a local cube file that you can put on a laptop and analyze data without being connected to SSAS2005.

Both ProClarity and Excel2007 have this option.

HTH

Thomas Ivarsson

|||

Hi, Thomas,

Thank you for your very kind advices.

But I found that change of the data source view does not really reflect on the data source view of the cube though? I mean after I created the cube, and I edited the data source view, but when I clicked on the cube, its data source view remained as the one before I changed to the new data source view?

Also, I am a bit confused, as all the data for the cube has already stored in SSAS2005 Data base (MOLAP), so why we still need to care about the data source and data source view if we dont need to have any update from the underlying data source?

Thank you for your kind attention and I am looking forward to hearing from you shortly for your further advices.

With best regards,

Yours sincerely,

|||

Hi, Helen.

I am not sure about the scenario here? Do you mean that you will create the cube once and never process it again?

If new dimension records and new fact records arrives from the data source you will always need to process the cube. If you need to process you will need to have the data source view.

During a days work new transactions will be registered in the source system. You will have to do a new process of the cube in order to get them from the source system into the cube.

Are you making cubes for laptop users that they will use off-line? Or are you building cubes using real time OLAP?

Regards

Thomas Ivarsson

|||

Hi, Thomas,

Thank you for your reply. In my case, well, I do deploy the whole project containing the cube after I changed the data source and data source view, but the cube remains the same as before I did any change. Therefore, I thought data source and data source view does not have anything to do with the MOLAP cube after its first deployment to the analysis services server.

Does it work in this way? Or I miss out anything of it? Thank you and your advices are most welcome.

With best regards,

Yours sincerely,

|||

Hello Helen. If you make a change like adding an attribute in the data source (source table/view) or the data source view(=named calculation) this change will not be a part of the cube until you add the attribute in the dimension editor.

In order to make a change in a cube you must mapp new columns to the measure group or to the dimension in the cube/dimension editor.

The data source view do not automatically update the cube or the dimensions. If you do a refresh in the data source view(right click) all changes in the source database will exported to the data source view.

HTH

Thomas Ivarsson

|||

Hi, Thomas,

Thanks a lot for your kind advices.

It cleared my confusion over the above question.

Thanks again.

With best regards,

Yours sincerely,

Question on cube write back

Hi, all experts here,

I encountered a problem with cube writing back which contains measures with count aggregation rather than sum which said write back can not be allowed on measure groups with other aggregations rather than sum? Is it saying we can only enable write back on cubes with measure groups all based on sum aggregations?

But in my case, the measures needed to be counted rather than sum. What can I try to enable write back on this cube?

I am looking forward to hearing from you for your advices and thank you very much in advance.

With best regards,

Yours sincerely,

I'm having trouble thinking of a situation where you would need to writeback to a count measure, but that is beside the point.

One work around might be to create a new column in your fact table for this measure and pre-populate it with a value of 1 during ETL. Then you would be able to use a sum aggregation and get the same value as you would with a count measure. It would then also be possible to write back to this measure.

|||

Hi,

In my data, the facts are bit data, which only with values of 1 and 0 meaning different things respectively. Therefore I need to count these different situations happened in the fact table across different levels. There are no numeric data types at the moment which are for sum.

Thanks for your advices and I am looking forward to hearing from any of you here more ideas in this situation. Maybe you would give me some better ideas on how to deal with it.

With best regards,

Yours sincerely,

|||

Helen,

Explain me better what is the goal, I didnt understood... Explain me as I a dummy!

:-)

Regards!

|||

HI, PedroCGD,

The facts in the facts tables are with 0 or 1 values only. (0 and 1 therefore represent different meanings), therefore I dont see any point to sum these facts in any cube. Instead, count aggregation makes more sense which can count the numbers for the fact being 0 or 1.

Hope my explanation is clear for your help.

With best regards,

Yours sincerely,

|||It sounds like you have some flags which should modelled as separate attributes, not as measures. You would have one count measure and set either a single dimension or a combined dimension (also known as a 'junk' dimension). In this way you should be able to get the count by any combination of attributes.|||

Hi, Darren,

Thanks for your kind advices.

As you kindly advised, I will then need to update the underlying relational database (as the underlying data warehouse is designed by others)?Since at the moment, the data residing in the data warehouse does not have any junk dimensions at all. All those flags attributes are residing in the fact tables.

I would need to create single dimension for each flag attribute or combine some of the flag attributes together into one single dimension? But only one count measure for all these dimension attributes?

Will it be any good to complete these in data source view of the cube? Thanks again. And hope you can give me more further advices on it.

With best regards,

Yours sincerely,

Question on Cube storage location

Hi, all experts here,

Thank you for your kind attention.

Why when I selected the storage location for any partition of a cube, only the local system drive is available for options? Is there any way to choose other storage locations rather than the local system drive as the storage location of the cube?

Thanks a lot in advance for your advices and I am looking forwad to hearing from you shortly.

With best regards,

Yours sincerely,

Hi,

The server property AllowedBrowsingFolders specifies the list of folders (separated by the '|' character) available to the browsing dialog used to enter the storage location of a partition (you can edit that property in the server's properties dialog from SQL Management Studio - you need to check the 'Show Advanced Properties' checkbox).

You can only put local folders in the AllowedBrowsingFolders property (so no shares, even from the same machine).

Adrian Dumitrascu

|||

Hi, Adrian,

Thank you very much for your kind advices.

With best regards,

Yours sincerely,

Question on cube processing and deployment

Hi, all experts here,

Thank you very much for your kind attention.

I encountered a very strange problem processing and deploying the cube to the analysis services server. The fact table where the measure group coming from only got several hundred rows, while at the same time I have other 6 dimensions with many attributes. My system drive currently still has nearly 1 GB space left, but when I deployed the cube, it took ages to run and ended up with process failure because of running out of space on my system drive.

This problem never happened before and it is really frustrated and I dont know what is going on.

Would please any experts here give me any advices. (I have tried ages, but still nothing really changes).

I am looking forward to hearing from you and thanks a lot in advance.

With best regards,

Yours sincerely,

Any expers know what is the cause of that problem?

Thanks.

With best regards,

Yours sincerely,

question on cube hierarchies

Hi, all experts here,

Thank you for your kind attention.

I am having a question on cube hierarchies creation. Is it necessary to create two different hierarchies for the hierarchies as below?

.................................................................................

Year-Quarter-Month-Day

Year-Month-Day

..................................................................................

Should we create two different user-defined hierarchies for the above hierarchies? As one of the hierarchy is actually included in another one. Therefore I am wondering if we need to create two different ones for them or just create the one with more levels.

Hope my question is clear for your help and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

Hello Helen. I recommend to create the one with all levels.

Do not forget the attribute relations between the levels in the user hierarchy.

HTH

Thomas Ivarsson

|||

Hi, Thomas,

Thanks a lot for your kind advices.

With best regards,

Yours sincerely,

Question on cube granularity again

Hi, all,

Again, I wonder is it a good idea to design the granularity level of the cube as the same as in its underlying relational database e.g the most detailed level as it is in its fact table and its dimension table.

I am looking forward to hearing from you for any advices.

Thanks a lot in advance.

With best regards,

Yours sincerely,

It really depends on your requirements. I would say you probably should make the granularities the same unless it's going to cause serious performance problems with the processing and/or querying of your cube - it just means you have all the data your users might want available. Changing the granularity of your cube once you've gone into production would also be a bit messy.

Chris

|||

Hi, Chris,

Thanks a lot for your advices. Very good point.

With best regards,

Yours sincerely,

Question on cube and dimension deployment in production

Hi, all experts here,

Would any of you here shed me any light on how could we deploy cubes and dimensions to other server rather than the one where they are created? And also what permissions at least are needed to be set up for them to be accessed in production enviornment?

Hope it is clear for your help.

Thanks a lot and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

Hi Helen,

Take a look at the functionality of the Deployment Wizard: http://msdn2.microsoft.com/en-us/library/ms176121.aspx

Regarding permissions, do you mean administrative access or the ability to query data? In either case BOL is again the best place to start: http://msdn2.microsoft.com/en-us/library/ms174517.aspx. Or do you mean the ability just to connect to the cube at all? In which case this new paper on connectivity problems might help - it discusses everything might might stop you from connecting:

http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/CISQL2005ASCS.mspx

Regards,

Chris

|||

Hi, Chris,

Thanks a lot for your very kind help. Definitely they've been very helpful to clear up my confusions and questions on that.

Yes, I am concerning about being able to access the cubes and dimensions after they are deployed to other production environment.

Thanks a lot again.

With best regards,

Yours sincerely,

Question on Cube aggregation percentage?

Hi, all experts here,

As I am a bit confused about the aggregation percentage of a cube, when we partition the cube, we need to set the aggregation percentage. But how does the analysis services really work here with the pre-defined cube aggregation percentage? Does not the analysis services engine only aggregates data on cube hierarchies and user-defined aggregations? (assume we have defined both the natural and user-defined hierarchies and user-defined aggregations), how can we determine how much percentage of aggregations it will be for that?

Hope it is clear for your advices and help and I am looking forward to hearing from you shortly and thanks a lot in advance.

With best regards,

Yours sincerely,

The aggregation percent number is only an approximate representation of the number of aggregations and estimated performance benifit of aggregations designed by the aggregation wizard. Note that it's a bit misleading in that you don't want 100% but something more like 30%. I recommend reading http://download.microsoft.com/download/8/5/e/85eea4fa-b3bb-4426-97d0-7f7151b2011c/SSAS2005PerfGuide.doc for more information on this subject.|||

Hi, Matt,

Thanks a lot for your kind advices and the very helpful imformation.

With best regards,

Yours sincerely,