If you start by creating all measures and then change some of them to be distinct count measures, no new measure group will be created. This will automatically create a new measure group for the same fact table. Be sure to change their aggregation type accordingly, as for numeric measures the default aggregation type is the “sum” function.Ģ) Then, drag into the pane all the other measures of the fact table. Then drag and drop the fields from the table that you want to become distinct count measures into the measure groups metadata pane. To create distinct count measure groups, you need to proceed in two simple steps :ġ) First, start with a fresh fact table that has still no measure group associated. This allow to manage partitioning and aggregations more accurately in a manner that suites better those kind of measures.
In fact, you have to perform the steps in the correct order or you ll find yourself stuck and need to redo the thing from the beginning…Īccording to the msdn, a SSAS fact table can only have one measure group associated except for the distinct count measures that can be separated from other measures and have their own measure group.
Ssas tabular distinct count how to#
Some times ago (who says years ?), I found myself stuck with this one, as it is not really straightforward into the SSAS BIDS cube designer how to achieve it. I even think that most of you may already know about it…. This is no rocket science, just a little tip that every one would solve after spending 5 minutes on it. They only wants 1 new measure, named "Nb of Special Invoice".Īs you may have noticed, my SSAS capabilities is not that good, as I dont touched to that since 3-4 years, and my client want me to do that.Having blogged last week here about the advantages of the new storage file format found in the Denali’s CTP3 for those kind of measures when they are of the string data type, remember me that maybe this little feature may need a post. My customer dont want to split every of their measure into 2 members : Special invoice, and normal invoice. My problem, is that there is no dimension to compare to. SECOND ATTEMPT : creating a MDX query that will DISTINCT count the IsSpecial 'measure' I tried modifying the base measure source to Preserve Null, or ZeroOrBlank, but it will still shows me the value '1' everywhere (except when there is really a IsSpecial). So I always see '1' as the minimal value (finding a lot of "NULL" occurence, so having a count of 1). However, SSAS is not that bright, and count the NULL as an occurence. In My first attempt, I put the InvoiceID or NULL in the IsSpecial field (that was not a byte), then do a DISTINCT COUNT, directly by adding my measure to a new measure group (so no MDX here). Now, my SSAS2005 cube is good and works very well.įirst attempt: placing the InvoiceID or NULL in the FLAG field, and DISTINCT COUNT it (forget the sample, I cant post a table, nor do an HTML table, nor formating text as a table with space).just imagine it. It is a TRUE/FALSE flag (so I would like using a Bit, in my fact table). For example, you can retrieve a table and then count the distinct values in it, or calculate dynamic sums across filtered tables or columns.
It is a columnar database capable of incredible performance and compression ratio. Tabular mode is a new enhancement in SQL Server 2012 analysis service database structure.
So the field IsSpecial is TRUE every invoice line of a particular invoice. Analysis services in SQL Server 2012 can be either deployed in multi-dimensional mode or tabular mode or power pivot for SharePoint as well. Sometime, some sales are FLAGGED as SPECIAL (the whole invoice, not only a invoice line).
0 kommentar(er)
