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

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

Need multiple distinct counts, have 1 fact and 1 dimension

Posted By:      Posted Date: September 18, 2010    Points: 0   Category :Sql Server
I am using SSAS 2005. I have 1 fact table and 1 dimension. I would like to create multiple distinct counts in 1 Measure group, at least I would like them to appear as in 1 measure group to end-user. I have tried role-playing dimensions, and a roll your own approach that work in limitation but didn't scale. Any help and advice would be great.

View Complete Post

More Related Resource Links

Getting counts by 2nd Date Dimension Attribute with Snapshot Style Fact Table

  I have an MDX question finding hard to solve.  I have a Snapshot Fact Table with a snapshot of the records in the source system for each batch date.  All records in the fact table are assigned the batch date with the batch date key.  There are many records for each day and each batch date is an entire copy of the source records.  So, the grain of the fact table is one record for each batch date that exists in the source system.  These facts rows have another date in them for when the record was entered.  This date is different from the batch date in that the batch date is based on the day the batch was processed and the entered date is based on when the record was entered.  If a record was entered many days before, its batch date will be today but its entered date will be several days ago.  Therefore each day a copy of all the records entered the previous batch date and all the records added on today's batch date are present. Fact Table : FactSnaphshotKey (surrogate for easier administration) BatchDateKey (link to batch date dimension – date dimension, first in dimension list so it is used for semi aggregate measures) EnteredDateKey (link to entered date dimension – date dimension) Facts Count – measure for fact table - default measure from Analysis Services cube 2 Dim

Avoiding a SELECT distinct query generated by SSAS when using dimension derived from fact table

Hi, I am using a dimension derived out ot a fact table and the factt able primary key is dimension key. Issue is, there are large number of rows and so many attributes. SSAS issues distinct query and it takes large amount of time. Without the distinct statement, query takes only 3 min for 4 million rows. With the distinct, it takes 20 min. Becuase the fact primary key is the dimension key there is no need of a distinct statement. I know there is a option in the dimension to say "By Table" to avoid this. But unfortuantely, i breach the 4 GB limit for strings. Any suggestions for optimization? Thanks,  Sambath

Dimension with multiple fields per fact row




I have a dimension which looks like this

Customer      Customer Responsible

Cust1             TMG
Cust1             TOL
Cust2             TMG


Relationship between a dimension - two fact tables

I have 3 fact tables and about 10 dimensions. I want to relate both "Fact_Pop1" and "Fact_pop2" to Dim_AgeGroup. But Fact_pop1 has single age group and Fact_pop2 has no single age group (5 years age group). I spent about a day on this but I could't solve it.   Fact_Pop1 Age        Race_key            Gender_key      Geography_key              Pop_size 10           White                    M                                            CA                          10000    10           White                    F        

A tsql report with multiple counts and pivioted description attributes

Hello,   I have an assignment in which I am to prepare a report displayed in excel to return counts of various attributes in the database. However, the count is a bit challenging because it requires counting certain attributes in a pivot format illustrated below. The counts for each category by recruiter, rolled up by Division, within the specified time frame.   The tables that I would have to deal with are as follows:   User: Has the number of Accounts…user accounts with no Recruiter are unassigned Recruiter: Has the recruiter information LoginHistory: Has a record of all logins EForm: Has the various forms, for example: Release and Authorization, Provider Service Agreement EFormUserXref: You can filter it to return the Physican AppForms                                                                       Surgery                                      &n

Non parent-child dimension with fact data at different levels... How!

Hi all, Quite new to SSAS, wondering if anyone could help with the following... I've got a dimension with attributes that indicate geographical location based upon UK postal boundaries - so ~1.8m Postcodes > ~10000 Postcode Sectors (PCDS) > ~3000 Postcode Districts (PCD) > ~100 Postcode Areas (PCA). The problem I have is that the address information is not of the highest quality. I have matched 50% of my facts to postcodes, of the remaining 50% i've mapped them to PCDS, PCD or PCA, where possible, leaving the lower-levels as NULL in those cases. My attribute hierarchy works fine where I have complete records all the way down the hierarchy, but I only have a single UNKNOWN member at the top (GOR) level. I want an UNKNOWN member at each level. It makes sense to me how to do this using a Parent-Child dimension, but i'm keen to avoid that as the performance is terrible. I'm hoping there's a method of configuring this. Any help massively appreciated.

Cannot get distinct records from a multiple inner join request


Hello everybody,

This is my first post here because this request is giving me serious headache.

What I'm trying to do : This is a small search engine. I ask SQL Server 2008 to return the 20 records I need in a specific page from a larger set of 1000 records. Then I want it to return only distinct records on the key t_ye.numenr (VID).

This returns doubled or tripled records. What am I doing wrong ?



declare @explicitMaximum int <br/>
declare @chosenPage int <br/>
declare @recordsPerPage int <br/>
set @explicitMaximum = 1000 <br/>
set @chosenPage = 1 <br/>
set @recordsPerPage =20; <br/>
WITH X AS (<br/>
SELECT DISTINCT TOP(@explicitMaximum) t_ye.numenr AS VID, <br/>

Bridge Table dimension or fact? updating from snapshots



Scenario: Bank Accounts and Customers. One Account can have many customers and many customers can have one joint Account. so its Many to Many relationship.

Special Scenaio: Bank provide us daily snapshot of all thier dimensions and facts, every night thier ETL run, and newsnapshot is available, previous is gone.

I am using SCD Transformation to update the dimensions, Type 1 for all the columns.

Tables1: DimAccounts (AccountsID(PK))

Table2:DimCustomers (CustomerID(PK))

Table3:DimBridge (AccountID (FK), CustomerID(FK), RelationShip (varchar10))

Question1: Are we supposed to treat bridge tables as Dimensions or Facts?

Question2:If it is to be treated as Dimension, How would I apply SCD Wizard to it, Since there are two business keys involved?

Question3: Do i need surrogate key in Bridge Table, like i have in other dimensions?

Thank You


Getting multiple counts ordered by Groups with a TOP X thrown in as well!


Hi, I have a result set (it's from multiple tables), that outputs the following values thus:

Department Statement Rating

IT Factor X 5

IT Factor Y 4

IT Factor X 3

SALES Factor Y 5

IT Factor X 4


Now, what I want to get out of this is a dataset detailing per department which had the highest number of records for a given statement (e.g. factor x) with the avergage.  However I don;t want to output all of them, just the highest.


So, the output would be something like:

Department Top Statement TotalCount  AvgRating

IT Factor X 3 4

SALES Factor Y 

Query to return conditional counts both distinct and non distinct.


I have the following table structure:

Table EmailTracking



Table EmailTrackingDetail


The following query returns the default recordset:


SELECT     TOP (200) EmailTracking.EmailAddress, EmailTrackingDetail.TranType, EmailTrackingDetail.TranDate
FROM         EmailTracking INNER JOIN
                      EmailTrackingDetail ON EmailTracking.RecordID = EmailTrackingDetail.EmailTrackingRecordID







2010-09-21 16:59:29.980



2010-09-21 17:19:58.790



2010-09-27 12:00:30.940

Save Dimension member only when this have lines in Fact Table


I have one dimension table with over 600 item, but in my fact table only 40 of this member have lines (my fact have a filter for the 2 laste year)


How save on the dimension only this 40 members?


Best regards




SSAS 2008 Dimension: Multiple FK Relationships to the same Dimension



I have kind of a fundamental design question: In my DWH are 2 tables TICKET and EMPLOYEE. EMPLOYEE is referenced by TICKET 2 times: TICKET_OWNER_ID and TICKET_ASSIGNED_TO_ID.

I have created a Ticket and a Employee dimesion in my SSAS Project. The Ticket Dimension should no have two atrributes: Owner and Assigned To. I can create the attributes by dragging the EMPLOYEE.NAME column from the DSV window into the attributes list but there is now to specify which relation will be used.

And of course I get pretty intresting results when I process the dimension.

I could create a named query in the DSV selecting * from EMPLOYEE and create a second Employee dimension but is that the only way I can get this to work ?

Thanks, ITD

Multiple companies and fiscal calendars in date dimension table

We are creating an application where different companies can have their own fiscal calendar starting on different dates. For example one company’s fiscal year may start in April and some others in Sep. Also fiscal calendars may start at any date. (E.g. 29 Sept).

These fiscal calendars would be created for parent companies. We have added the parent company ids to the date dimension table. Please suggest the correct way of setting the attribute relationship in the above scenario. If this is not the right method to do this, please suggest the correct design approach for achieving this.


Thanks in advance,


Hamlin Stephen

Loading Dimension & Fact tables


Hello Experts,

I am new to SSAS & I need to load Dim & Fact tables for a data warehouse. I've the basic idea to load them, but i dont have the exact picture to load a fact table. For eg. if we have 3 dimension tables like Dim_Time / Dim_Geography / Dim_Product and one Fact Table Fact_Product as described in most of the online examples. Then how to load them. I know for fact Tables we need to do aggregations. But how to apply those aggregations on what criteria.  In the said example if we need to load Fact_Product then we should be able to see sales by product, sales for a given point in time & sales for a given geographic location. Then how to do that. Do we need to apply aggregate for all the dimension tables

I know i am not much clear, but i hope you guys can understand. If not please let me know i will try to explain it more. Please clear my doubts.

Thanks & Regards,


Dimension key attribute changes in fact table



How do I need to handle a case in which the fields in a fact table that represent the foreign keys to the dimension tables might change? What kind of process do I have to do to the cube?



Distinct query generated to populate a dimension

When processing a dimension attribute, what elements qualify as necessary for the auto generated distinct count in SQL?    I am assuming is based on the attribute relationships, but how can I control what goes into the distinct count?  (we have an issue in which we only want a distinct could to include certain attributes and not others)

Javier Guillen

Map the start and end datetime fields of an historical dimension to the time dimension and the fact



I have a dimension with some historical attributes and I use start and end datetime fields to save the outdated records. I assume that when the cube is processed, each record in the fact tables is mapped to the matching record in the dimension by using the combination of the key attribute with the start and end datetime fields. My question is how to configure the mechanism so that it will know which field is the datetime field and use it to do the comparisons with the start and end fields?


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