Distinct Count in a new Measure Group

Here is the situation:

I have a cube that has transaction level data and there is a control number that goes with this data, but it isn't one for one. If I have 60 million rows in the fact table, I have 50 million control numbers in a control number dimension.

I would like to be able to slice the data by date (for instance) and then do a distinct count on the control number dimension. I can do a distinct count w.o having to do a join of course because I can count the distinct instances of the key to the control number dimension.

One more note - the control number dimension is not a degenerate dimension...it is not one to one on the fact table.

I would like to use the DISTINCT COUNT measure in SSAS, but it makes me create a new measure group to do so. All of my additive measures are in one group, the distinct count is forced into another group. This forces double the processing time.

Does anyone know why this behavour happens and can someone explain why a DISTINCT COUNT measure has to go in a seperate measure group?

Thanks,

Mark

Mark,

Do you already have a DISTINCT COUNT measure in that measure group? Analysis Services will only allow you to have one DISTINCT COUNT per measure group.

|||Maybe this MDX will overcome the limitation of one unqiue count.

Distinct(Filter([Customer].[Customer Name].Members,Not IsEmpty([Measures].[Sales Amount]))).Count

Timmy|||

Actually, I don't have one in the first measure group. The first measure group is just regular measures, but if I create a new measure that is a distinct count measure, it automatically puts it in another group.

I have now heard from other sources that this is an exptected behavior. So no biggie, just needed to know.

ps - sorry it took so long to respond.

Thanks,

Mark

http://spaces.msn.com/mgarnerbi

|||I noticed this too, does anyone know the underlying reason why this is? Is this going to be fixed in a future version?sql

Distinct Count in a new Measure Group

Here is the situation:

I have a cube that has transaction level data and there is a control number that goes with this data, but it isn't one for one. If I have 60 million rows in the fact table, I have 50 million control numbers in a control number dimension.

I would like to be able to slice the data by date (for instance) and then do a distinct count on the control number dimension. I can do a distinct count w.o having to do a join of course because I can count the distinct instances of the key to the control number dimension.

One more note - the control number dimension is not a degenerate dimension...it is not one to one on the fact table.

I would like to use the DISTINCT COUNT measure in SSAS, but it makes me create a new measure group to do so. All of my additive measures are in one group, the distinct count is forced into another group. This forces double the processing time.

Does anyone know why this behavour happens and can someone explain why a DISTINCT COUNT measure has to go in a seperate measure group?

Thanks,

Mark

Mark,

Do you already have a DISTINCT COUNT measure in that measure group? Analysis Services will only allow you to have one DISTINCT COUNT per measure group.

|||Maybe this MDX will overcome the limitation of one unqiue count.

Distinct(Filter([Customer].[Customer Name].Members,Not IsEmpty([Measures].[Sales Amount]))).Count

Timmy
|||

Actually, I don't have one in the first measure group. The first measure group is just regular measures, but if I create a new measure that is a distinct count measure, it automatically puts it in another group.

I have now heard from other sources that this is an exptected behavior. So no biggie, just needed to know.

ps - sorry it took so long to respond.

Thanks,

Mark

http://spaces.msn.com/mgarnerbi

|||I noticed this too, does anyone know the underlying reason why this is? Is this going to be fixed in a future version?

Distinct Count in a new Measure Group

Here is the situation:

I have a cube that has transaction level data and there is a control number that goes with this data, but it isn't one for one. If I have 60 million rows in the fact table, I have 50 million control numbers in a control number dimension.

I would like to be able to slice the data by date (for instance) and then do a distinct count on the control number dimension. I can do a distinct count w.o having to do a join of course because I can count the distinct instances of the key to the control number dimension.

One more note - the control number dimension is not a degenerate dimension...it is not one to one on the fact table.

I would like to use the DISTINCT COUNT measure in SSAS, but it makes me create a new measure group to do so. All of my additive measures are in one group, the distinct count is forced into another group. This forces double the processing time.

Does anyone know why this behavour happens and can someone explain why a DISTINCT COUNT measure has to go in a seperate measure group?

Thanks,

Mark

Mark,

Do you already have a DISTINCT COUNT measure in that measure group? Analysis Services will only allow you to have one DISTINCT COUNT per measure group.

|||Maybe this MDX will overcome the limitation of one unqiue count.

Distinct(Filter([Customer].[Customer Name].Members,Not IsEmpty([Measures].[Sales Amount]))).Count

Timmy
|||

Actually, I don't have one in the first measure group. The first measure group is just regular measures, but if I create a new measure that is a distinct count measure, it automatically puts it in another group.

I have now heard from other sources that this is an exptected behavior. So no biggie, just needed to know.

ps - sorry it took so long to respond.

Thanks,

Mark

http://spaces.msn.com/mgarnerbi

|||I noticed this too, does anyone know the underlying reason why this is? Is this going to be fixed in a future version?

Displaying huge heirarchial data in reports

I need to display huge data around 1 million records in report. Data needs to be represented as heirarchial. So there is somewhere around 6 level of grouping of data. Can somebody suggest a solution to get the data and display the data in a faster way.

Thanks in advance.

SSRS

Hi,

I don't knw if I understand your problem but you could use the document map functionnality for every level of grouping you have.

HTH,

Eric

|||

Hi Eric,

Thanks for your reply. But, my actual problem is the huge data, fetching that data and displaying such huge data is taking lot of time. So I was looking for some solution where on demand if I can access the required data and display in the report. Like first I can get all the parents and once I click on any of the parent it fetches the child records for that parent and display it on the same report. Please help on the same. If you've still some doubts then do let me know. Because I do need some kind of solution for this problem

Thanks & Regards,

SSRS

Deepak

|||What about using sub report ?|||

Subreports won't help much in this case, as they are processed at the same time as the main report.

You can try creating a master report that displays only aggreggate information per groups (the report query should use T-SQL group by, count and friends...)

Under each aggregate you can create a link (jump to report, aka drillthrough report) to a report containing the details for that particular group. And so on for all the grouping levels until you get to the details rows, which will hopefully be filtered out to a small enough number.

Hope that helps

Tudor Trufinescu

|||

I am having similar problem with huge data. I have linked a detailed report using jump to report. When the data is less it is running fine. But when my parameter selection retrieves huge data in the main report the problem is coming. It seems like it is executing the linked report along with the main report. It is not waiting till the cell is clicked to link. Why is it happening that way? The reason that I feel the linked report is also being executed along with the main report is, for the same parameter selection if I don't link the detailed report it is working fine. Any advice in this regard is much appreciated.

Thank you.

Displaying huge heirarchial data in reports

I need to display huge data around 1 million records in report. Data needs to be represented as heirarchial. So there is somewhere around 6 level of grouping of data. Can somebody suggest a solution to get the data and display the data in a faster way.

Thanks in advance.

SSRS

Hi,

I don't knw if I understand your problem but you could use the document map functionnality for every level of grouping you have.

HTH,

Eric

|||

Hi Eric,

Thanks for your reply. But, my actual problem is the huge data, fetching that data and displaying such huge data is taking lot of time. So I was looking for some solution where on demand if I can access the required data and display in the report. Like first I can get all the parents and once I click on any of the parent it fetches the child records for that parent and display it on the same report. Please help on the same. If you've still some doubts then do let me know. Because I do need some kind of solution for this problem

Thanks & Regards,

SSRS

Deepak

|||What about using sub report ?|||

Subreports won't help much in this case, as they are processed at the same time as the main report.

You can try creating a master report that displays only aggreggate information per groups (the report query should use T-SQL group by, count and friends...)

Under each aggregate you can create a link (jump to report, aka drillthrough report) to a report containing the details for that particular group. And so on for all the grouping levels until you get to the details rows, which will hopefully be filtered out to a small enough number.

Hope that helps

Tudor Trufinescu

|||

I am having similar problem with huge data. I have linked a detailed report using jump to report. When the data is less it is running fine. But when my parameter selection retrieves huge data in the main report the problem is coming. It seems like it is executing the linked report along with the main report. It is not waiting till the cell is clicked to link. Why is it happening that way? The reason that I feel the linked report is also being executed along with the main report is, for the same parameter selection if I don't link the detailed report it is working fine. Any advice in this regard is much appreciated.

Thank you.

Display hirearchy data using data region

I am trying to display hirearchical data using data region. For eg. the first level will be a list of customer names. The second level will be a list of Invoices under each customer. The third level will be a list of product names that were sold under each invoice.

I used a list for the data region and in each hireachy, there is a textbox in each list to display the data. I am trying to associate each data region with a datatable (I will provide the logic to populate the datatable with appropiate data). I do not want the ReportViewer to interact directly with a database because this is a 3 tier design and I am using the ReportViewer in local mode. I couldn't find any example of these. Can anybody help? Also I would like to see an example of the .rdlc file in these situation. Thanks very much.

Kam

Take a look at this article and post any follow-up questions to this post.|||

Hi Teo,

What my situation in is that I have a nested list data region. Specifically I have two list data region, one stack on top of each other. The list data region on the bottom has a textbox and another list data region. The textbox will display a list of customer name. The list data region on top also has a textbox. This textbox will display a list of invoice number with respect to each individual customer.

I read that not only can list data region be nested, each can associate with its own data table. So in my code I have two data table, the first one contain a list of customer name and its corresponding customer ID. The second one contain a list of invoice number and its corresponding customer ID that relates to the customer ID of the first table. I have trouble making this work because for some reason the textbox on the top list data region refuses to accept fields in the invoice data table. It only accepts fields in the customer data table.

I have seen example of nested list data region but it uses SQL statement and talks directly to a database. I am using this in local mode in a 3 tier situation so I have no access to a database.

Thanks very much on your last reply and I am looking forward on this one.

Thanks. Kam

|||

Take a look at the Sales Order sample report which ships with Reporting Services. Yes, data regions can be nested and each one can be bound to its own dataset.

Currently, SSRS doesn't support joining datasets at the report level. However, if all datasets will filter on a single customer you can synchronize them by using a customer query parameter. The Sales Order report demonstrates this technique although the list and table regions are not nested (they don't have to be because the datasets are pre-filtered already). An outer list can only reference the first or last record from an inner dataset as you can see by dragging a field onto it. That's because the inner list may have many records, e.g.; one customer has many invoices.

The report will work the same way in both remote and local modes. The only difference in local mode is that you have to bind the three application datasets to the report. The article I mentioned in my first reply demonstrates how dataset binding works in local mode.

|||Thanks for the tip. I haven't have a chance to look into it yet. I will let you know when I come back to this problem. Thanks.