.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

Distinct query generated to populate a dimension

Posted By:      Posted Date: October 08, 2010    Points: 0   Category :Sql Server
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

View Complete Post

More Related Resource Links

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

GridView Paging + Unsupported overload used for query operator 'Distinct'


The issue is two fold in my ASP.NET Web Application. I need to use a custom Distinct() via LINQ to SQL, and then assign thevar as a DataSource to a GridView. If I use the custom Distinct that implements IEqualityComparer, I get "Unsupported overload used for query operator 'Distinct'" exception. but if i somehow get past this, i get "The data source does not support server-side data paging" exception on GridView.DataBind(). here is the code:

distinct_employees =
    (from emp in wsq.LIT_Active_Employees
    where (String.IsNullOrEmpty(ssFirstName.Text) ? (emp.FIRST_NAME == null || emp.FIRST_NAME.StartsWith(String.Empty)) : emp.FIRST_NAME.Contains(ssFirstName.Text)) &&
            (String.IsNullOrEmpty(ssLastName.Text) ? (emp.LAST_NAME == null || emp.LAST_NAME.StartsWith(String.Empty)) : emp.LAST_NAME.Contains(ssLastName.Text)) &&a

MDX Query - Get (Parent-Child) Dimension member regarding another Parent-Child Dimension, then, get

Hi there, I got the following MDX issue. My cube structure looks like that : DIMENSION CATEGORY (Parent-Child) --> (Many to many) FCTLESS_CategoryNode <-- DIMENSION NODE (Parent-Child) ___ DIMENSION NODE (Parent-Child) --> (Many to many) FCTLESS_NodeVariable <-- DIMENSION VARIABLE ___ DIMENSION VARIABLE <-- FACT (Supposed with only one value called VALUE) I'd like aggregate for N nodes linked to a specific category. For example, with the next content : DIMENSION CATEGORY - COUNTRY - SITE - BUILDING DIMENSION NODE US (Category Country)     - Site US_A (Category Site)     - Building US_A_A  (Category Building) FR (Category Country)     - Site FR_A (Category Site)     - Building FR_A_A  (Category Building)                                       - Site FR_A (Category Site)     - Building FR_A_B  (Category Building)                                       - Site FR_B (Category Site) 

Write select query with a paremetrized where clause, and populate database with the retrieved column

I want to be able to retrieve column values using the dropdownlist values as parameters then populate the tbPO table with the retreived values. Where and how do I do it. ImportsSystem.Data.OleDb ImportsSystem ImportsSystem.IO Imports ?directcostDataSetTableAdapters PartialClass po_header  Inherits System.Web.UI.Page  Dim cn As OleDbConnection  Dim cmd As OleDbCommand  Dim dr As OleDbDataReader  Dim icount As Integer  Dim str As String  Dim vendor_id As Object   ?  Public Sub btn_click(ByVal sender As Object, ByVal e As System.EventArgs) Handles button1.ClickTry   context.Items.Add(     cmd.CommandType = System.Data.   cmd.CommandText = Dim context As HttpContext = HttpContext.Current"Company_Name", DropDownList3.Text)Dim cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Users\Owner\Documents\Visual Studio 2010\WebSites\WebSiteJun25\App_Data\directcost.mdb;")Dim cmd As New OleDbCommand()CommandType.Text"insert into tbPO(ponumber, suppliername, ShipTo, basictype, DateNeeded, Purpose,ShippingInstr, ProjId) values(?,?,?,?,?,?,?,?)" cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.Add

Please help me to find out distinct value from linq to sql query given.

profileuserDataContext db = new profileuserDataContext(); var Q = (from p in db.edufinals join RM in db.edufinals on p.UserName equals RM.UserName where p.name.StartsWith(srcval) select new { p.name, RM.ProfielID, RM.ProfielPic, RM.city }).OrderBy(x => x.name).Distinct().Take(10);//, p.Key.ProfielID, p.Key.ProfielPic, p.Key.city, p.Key.country foreach (var pg in Q.Distinct()) { Response.Write(pg.name); ..................................... .................................... } how to refine this query to get distinct "name" column? Please help me to find out exact value.

How to Create an MDX Query Parameter to Select 30 Values from a Dimension?

Hi, I'm using SSRS 2005 to report on an SSAS cube that contains a Procedure dimension.  I don't need to use members of this dimension in my report, but rather need to select records (patients) where their chart has one or more of the codes.  I've researched this today and cannot locate the best approach.  Thus far, I've attempted to create an MDX query parameter as part of my dataset.  However, I don't know whether this is the correct approach, and how to structure the syntax so that only records with one or more procedures are included in the report?  If so, what is the proper MDX syntax for setting my Procedure code equal to the query parameter? Thanks, Sid

MDX query to filter on specific dimension attributes

Ok, trying to figure out how to filter on specific dimension attributes. Are there any suggestions? I have a measure that needs to be filtered by a dimension that has numerous attributes (20 attributes in total) but I only want to show that measure against 10 of the attributes and once I get that result I need filter that result set against my time dimension (which this part I have figure out)   This is what I have so far: With member [calculated_measure_name] as ( [measures].[count_of_people], { [dimelgiblestatus].[elgible].[elgible1], [dimelgiblestatus].[elgible].[elgible2], [dimelgiblestatus].[elgible].[elgible3], [dimelgiblestatus].[elgible].[elgible4], [dimelgiblestatus].[elgible].[elgible5], [dimelgiblestatus].[elgible].[elgible6], }   )   Select [calculated_measure_name] on columns, [date].[fsicalyear].&[2008], [date].[fsicalyear].&[2009], [date].[fsicalyear].&[2010], [date].[fsicalyear].&[2011] on rows from mycube   When I run it, it gets no errors but in the result set for the numbers I get "#error"; do I need to basically create a subcube in order to get the result like I would like? If so how would I start doing that?   ThanksNetwork Analyst

Trying to build query using DISTINCT or GROUP BY...beginner here

Hi all, I have a table with the following format: instanceID    timeStamp  stepID 28B2D4FB-67F6-40CA-84A2-839BF3CC4B91 2010-09-07 20:36:32.807 1 28B2D4FB-67F6-40CA-84A2-839BF3CC4B91 2010-09-07 20:36:33.807 2 28B2D4FB-67F6-40CA-84A2-839BF3CC4B91 2010-09-07 20:36:34.807 3 ... EADD3AAA-5E93-4311-A844-9A7BE53A9606 2010-09-09 22:18:25.757 1 EADD3AAA-5E93-4311-A844-9A7BE53A9606 2010-09-09 22:18:26.773 2 so I need to build a query which will return 1 instanceID and all its stepIDs in one row. So the results would have to be something like this: instanceID    timeStamp  StepIDs 28B2D4FB-67F6-40CA-84A2-839BF3CC4B91 2010-09-07 20:36:32.807 1,2,3 EADD3AAA-5E93-4311-A844-9A7BE53A9606 2010-09-09 22:18:25.757 1,2 and if possible I would like to specify something like...bring me the data where 'timeStamp' > 2010-09-07 20:35 ps: I tried using DISCTINCT and GROUP BY but could not reach the desired results. Thank you!JCD

Getting Query Time Out Problem in Particular Dimension?

We are getting query time out problem in particular dimension in SSAS. can any one help on this?  

Need multiple distinct counts, have 1 fact and 1 dimension

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.

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

Distinct or Max - T-SQL QUERY


Hi Guys,

I need distinct or max of below two columns. I am using distinct on both columns but still I result are two instead of one. 


FileName                               FilePath

SALES_082753_20100324     D:\SQL01\LandingFiles\SALES_082753_20100324_2010427.csv

SALES_082753_20100324     D:\SQL01\LandingFiles\SALES_082753_20100324_2010427.csv

SALES_082753_20100323     D:\SQL01\LandingFiles\SALES_082753_20100323_2010427.csv

SALES_082753_20100323     D:\SQL01\LandingFiles\PluSALES_082753_20100323_2010427.csv

SALES_082753_20100322     D:\SQL01\LandingFiles\SALES_082753_20100322_2010427.csv

SALES_082753_20100322     D:\SQL01\LandingFiles\SALES_082753_20100322_2010427.csv

SALES_082753_20100317     D:\SQL01\LandingFiles\SALES_082753_20100317_2010427.csv

SALES_082753_20100325     D:\SQL01\LandingFiles\SALES_082753_20100325_2010427.csv

SALES_082753_20100325     D:\SQL01\LandingFiles\SALES_082753_20100325_2010427.csv


Out-Put Required

FileName                       &nbs

How do i create dimension based on this query



I have cube called client distinct cube.. that cube is been driven by table A and has the following columns

Client-id, Active ,mcd Elibigle and time_Id

and there another table called Eligibles which has the following columns

Client_id, Time_Id, Plan_Id, plan_Id can be 1 -5


I have a created a dimension called client insurance with table eligbles as the base table and i have added 2 more tables called ben-plans and insurance to get the names for it...

SO when i build this dimension i get the STate, and medicaid and its subsidiarys

the dimension and fact tables for this cube is matched by Client ID and when i process this cube and i get 260 distinct clients for med C but if i query those two table for the same time frame i should 620 distinct instead of 260.. I am not sure what i am doing wrong...

basically i want the dimension to be build by the following query

Select distinct a.client-id from tbl_a a, tbl_eligibles e where a.client-Id = e.client-Id and a.time-Id = 54121 and e.time-Id =54121 and e.plan_ID = 5

Any help will be appreciated.



A query for selecting on dimension members



Is there a way to write a query for selecting only dimension members without any measures? I'm trying to select the members by using "ON 0", but then I get a result containing 1 row and 0 columns and I want to get a list of the members, like what I get when using Excel and dragging a hierarchy / attribute to the row labels section.


Linq Query Returning Distinct


This is my query 

    Dim s_Person = From Person In db.People Join ProspectStatus In db.ProspectStatus

                       On Person.Id Equals ProspectStatus.Prospect_ID

                       Where (Person.Organization_ID = s_Organization_ID)

                       Select Person

I only want to return DISTINCT values of Person.ID.  Right now, it returns the same person multiple times because there are several entries in the status table.

Any ideas?

    Dim s_Person = From Person In db.People Join ProspectStatus In db.ProspectStatus
                       On Person.Id Equals ProspectStatus.Prospect_ID

Process a SSAS cube's dimension by a T-SQL query



I need to process a SSAS 2008 cube's dimension by a T-SQL query, using a SQL agent job.

How can this be done.

Process SSAS cube's dimension by TSQL Query



I need to process a SSAS 2008 cube's dimension by a T-SQL query, using a SQL agent job.

How can this be done.



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