.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Quering LastChild-measures results in scanning all partitions in measure group

Posted By:      Posted Date: October 13, 2010    Points: 0   Category :Sql Server


I have a large fact table with daily warehouse snapshots. I created a measure group with 2 measures with LastChild aggregation function (balance in pieces and EUR). I built monthly partitions in the measure group. When I run queries like this:

select {[LastChildMeasure1]} on 0,
non empty [Locations].[Location].[Location] on 1
from [Cube]

, SSAS 2008 scans only last partition. But when query both measures:

select {[LastChildMeasure1],[LastChildMeasure2]} on 0,
non empty [Locations].[Location].[Location] on 1
from [Cube]

, SSAS begins to read all partitions. When I remove "non empty" before [Locations].[Location].[Location], then SSAS reads only last partition again, but null-rows appear in query results. My Time dimension has "Type=time" and all internal DATAID's are in right order and all attribute relationships are correct.

Is there any suppositions why SSAS reads all partitions and how to avoid this?

View Complete Post

More Related Resource Links

influence processing order of partitions of measure group



  • a Measoure Group which needs full processing
  • SQL Server 2008 R2
  • the question is regarding performance

The measure group is partitioned by qarter - therefor I have some 30 partitions at the moment. I issue a processFull of the MEasureGroup and let Aanaysis Services Descide the parallelism. In Common it ends up to process between 8 and 12 partitions in parallel (on the 12 Core Machine).

The point is - my partitions growth over time - so older partitions are smaller then newer partitions. Usually Analysis Services ends up starting the newest partition at last - which is the biggest one - and therefore ends up phasing out the processing on a sinle processs (not utilising) the server efficient - because the newest partitions is somehow the biggest and needs the most time - but had nothing to process anymore.

Is there a way to

  • let the server deside the degree of parallelism (like now)
  • but specify the order (lets say start with the newest partitions and end with the oldest)

I have not found anything in the documentation

  • mabe something about processingpriority
  • or just the oder of the process statements in the parallel processing batch - if I would specify them




processing measure group : memory error : the operation cannot be completed because the memory quota

Hi, I'm stucked with this problem. Untill last week, the cube processed without any problem. Since last week, I'm getting this error. I have been searching in different forums, and I tried some suggestions, like changing memory limit properties, ... It is getting worse.. So I reset all properties to default again. I am running SQL-Server + MS-AS 2005 SP2 on server with 4GB of memory. This is a dedicated server, nothing else is running on it. The fact table has +/- 14 million records, several dimensions en 2 measure groups. I don't have problems to process the dimensions, but when I try to process the cube or the measure groups of that cube separately , the error persists. I have changed the datasource view, and replaced the fact table by a Named query. Even when I put a 'WHERE datapart( year , fact_date ) >= 2009 ' clause to reduce the number of records to +/- 5 million, I'm still getting the error. I don't understand what is wrong, the cube always processed since +/- 2 years. As I said, I have found a lot of this kind of Issues on different websites, I have been trying to change some properties. But this still does not solve the problem. Could it be that MS-AS settings are corrupt somewhere ? Is it a good idea to re-install MS-AS 2005 + SP1 + SP2 ? Or is there another reason possible ? I really appreciate any kind of help, because I'm

Group Results By Month, showing each month as the column

I have to list 50 states and the # of population per month. right now my results look something like..   CA, June, 5000 CA, July, 6000 CA, August, 7000 CA, September, 7230 I want it to show.. State, June, July, August, September CA, 5000, 6000, 7000, 8000 NJ, 4013, 5030, 7020, 7310   I was going to use a PIVOT table but.. I want the columns to expand as the coming month  comes upon us.

how to process just one partition along with other measure group and dimension in SSIS package Analy

HI All, i have to process just one partition1  of measure group A ,along with this i suppose to process all the Measure group and dimension with the help of SSIS Package Analysis Services Processing task. Partition1 having a query which fetch data only for previous day only. what i have done i select partition 1 in process data mode,all other measure group in full mode and dimension in process update mode.   i haven't taken measure group of partition1 and also not taken cube in the processing list ,when i run the package ,it runs suceesfully but data not get uplaoded into the Cube.   kindly suggest what other measures should i take to update the data . Amit

SSAS 2008 Measure group Distinct count

Hi all, I have a data of as number of trasactions,DD, SO, BouncedDD, CancelledDD all are in count (number) while adding these measures manullay to a measure group I have selected usage as DistinctCount for one measure and for all the remaining measures as DiscinctCount.While deploying the cube it shows error as "Fact table canot have more than one distinct count"  

SSAS 2008 - I cannot seem to have semi-additive and normal additive measures in the same measure gro

I have a measure group with both conventionally aggregated measures (aggregation type SUM) and semi additive measures (LastNonEmpty). The cube processes them quite happily, and I can view and analyse (slice) them fine, but when I analyse them both together, then the conventionally aggregated measures (aggregation type SUM) always dissapears in favour of the semi additive measures. This is not the case if I analyse conventionally aggregated measures (aggregation type SUM) and semi additive measures (LastNonEmpty) from different measure groups.Why does this happen, and is there a way to overcome this by pref not creating seperate fact sources for the conventionally aggregated measures (aggregation type SUM) and semi additive measures (LastNonEmpty) currently in the same fact source.

Measure Group Bindings

Hi fellows, how are you doing? I'm having a problem trying to associate a Measure Group that haves 2 columns for two different attributes of a dimension with a regular relationship. I've just associated one of the columns in the relationship window, and then in the Advanced window associated the another. While from the configuration point it's fine, when I try to break the values of this dimension group down by the two different attributes, just the one that was associated in the main window works, when I try to break the analysis by the second attribute, it repeat the total of the measure by each attribute. Why this is not working? Thanks, RafaelRafael Veronezi Database Administrator | BI Analyst Twitter: ravero Blog: http://raver0.wordpress.com

How to do a <> Select Query, and assign results to a Group 'Other'


How can I use this in a Select Query?
<> "*" & "Internet" & "*" Or <> "*" & "Old Customer" & "*" Or <> "*" & "Reference" & "*" Or <> "*" & "Saw Trucks" & "*" Or <> "*" & "Y/P" & "*" I want to group all the results (named count) and call the result 'Other'

Here’s my SQL now:

SELECT DATABASE.[LEAD FROM], Count(DATABASE.[LEAD FROM]) AS [Count of Leads], DCount("*","[DATABASE]","[Lead From] = " & Chr$(34) & [Lead From] & Chr$(34) & " AND Database.[Appt Date] >= #" & DateAdd("d",-7,Date()) & "#") AS [Last 7-Days], DCount("*","[DATABASE]","[Lead From] = " & Chr$(34) & [Lead From] & Chr$(34) & " AND Database.[Appt Date] >= #" & DateAdd("d",-30,Date()) & "#") AS [Last 30-Days], DCount("*","[DATABASE]","[Lead From] = " & Chr$(34) & [Lead From] & Chr$(34) & " AND Database.[Appt Date] >= #" & DateAdd("d",-365,Date()) & "#") AS [Last 365-Days]



Financial Reporting Measure Group question in Adventure Works 2008 R2


I need some help understanding the Financial Reporting measure group in Adventure Works 2008 R2. I get a value of $12,609.503 when I just drag amount into the query pane. Which accounts is this made up of, and how does it get to this result? Thanks.

Results with out measure


Which measure is the query below referring to? Thanks in advance....

SELECT [Account].[Account] ON 0,
[Date].[Date] ON 1
[Adventure Works]


Alter measure group: Impact on cube processing?

Hi All,

I have a XMLA script to alter source column (<ColumnID>) of a measure. I would like to know
following regarding the script:

1 - Do I have to 'Process Full' the cube or other processing options are applicable?
2 - The cube takes 2 hrs to process. Will there be reduction in process time processing the cube
    after running the script or will it take the whole 2 hrs?
It running cube on production so I would like to minimize the impact of the alter script.

Thanks in advance for any help.

Hide a measure group in excel 2007 'Show Fields Related to'


Hi All,

I know that if a I want to hide a measure group to users I have to set the properties 'Visible' to false for all the measure of the measure group.

But when I open a connection to the cube with Excel 2007 I see again the measure group in the drop down list 'Show Fields Related to' in the 'Pivot Table Fields List'.

I don't see it in the measure group list when I choose 'All' in the 'Show Fields Related to', but i don't want to see it in the list 'Show Fields Related to' too.


How I can do to hide permanently the measure group?


Thank you all,


Incorrect results using multiple partitions


Hello there,

I'm at a loss as to why I have the following problem... any advise or help to point me in the right direction is welcome !

I'm using SQL 2008 Enterprise, 64 bit.

I have a fairly big cube with approximately 15 measure groups, a few of them have > 1 billion transactions, and around 30 dimensions. The biggest measure group has been broken down into 62 partitions, based on the date of the transaction. This specific measure group is linked with a time dimension on a date field.

When browsing the cube (with different tools, the result is the same), I have the following behaviour that I can't explain:


- If I filter on individual dates (the filter is for one specific date), I have a correct result all the time, whatever the date:

Filter = Date1 :

      Date    Sales Amount
      -----    -----
      Date1  aaa

      Total   aaa


Filter = Date2 :

      Date    Sales Amount
      -----    -----
      Date2  bbb

      Total   bbb


Measure Group Shows Empty


I've searched this forum and read a few posts similar to mine, but they either weren't answered, or the answers didn't work.


I have an Analysis Services database with about 8 measure groups and about 25 dimensions.   We just released our new version into our production environment today, and we have one measure group that shows up completely empty.  All functionality worked in development, was tested in stage, but now this one measure group doesn't work.  I can check all connections to db, data source, dsv, to cube, to measure group, and everything is fine.  From within BIDS, I can right click a fact table and explore data, and data is there.  Data is also there in the fact table inside the database engine.  When I try and browse the cube, or query with MDX, just the one measure group shows all null.  Any thoughts?

DistinctCount and incorrect measure group


SSAS 2008.  I have one measure group with regular sum and count aggregations.  I have another measure group for a DistinctCount aggregation from the same source table.  I would like to add a second DistinctCount aggregation.  However, when I add this measure through Visual Studio, it puts it under my sum/count measure group.  Any ideas on how to force it into my DistinctCount measure group or a brand-new measure group?



Link 1 measure group to 2 different role playing date dimensions and browse it by same date dimensio


hello, I'm Using SQL Server Standard 2008.

I have a measure group Sick leave, which holds measures like sick duration, and number of sick registrations.

For sick duration, the measure group is linked to the Date End role playing dimension by Sick End Date.

For Number of Sick registrations, the measure group is linked to the Date Start role playing dimension by Sick Start date.

But now I want to browse both sick duration and number of sick registrations at the same time against Date dimension,

How to do that?


Measure Group Not Found Error - when connecting to OLAP from Excel 2007



I had a measure group in my cube that I have removed.  Now when trying to connect to the cube using Excel for some slicing/analysis, I get an error

ERROR= the 112458261 measure group was not found. and Excel will not connect

How can I get past this error?  I have;

Deleted and reprocessed the spcific cube without luck

Deleted the entire database and rebuilt/redeployed and reprocessed the entire cube.

I have no references in my cube any longer to the old measure group and processing completes fine.

HELP! (please)


ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend