.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

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

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

View Complete Post

More Related Resource Links

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

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

Tricky SELECT query from a Single Table

Hi I have a 2 rows of data in a table as mentioned below   Table1             ElementID Month Year Planned Cost UnplannedCost PlannedExpense UnPlannedExpense 4 9 2010 NULL 40 NULL 20 4 9 2010 400 NULL 200 NULL  I need a SELECT query to get the output in a single row as ElementID Month Year Planned Cost UnplannedCost PlannedExpense UnPlannedExpense 4 9 2010 400 40 200 20 Could anybody help me in writing a query for this? Thanks

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

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.

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


SQL gets corrupted with 'WITH' command and rubbish when editing query containing derived table


When my colleague makes any change to a query containing a derived table, the word 'WITH' followed by a lot of graphical characters appears after the alias, rendering the query unusable.  We have tried this with various existing and new, simple queries, all to no avail.  We are editing the queries in Visual Studio 2003, and he does not get this effect when using Visual Studio 2005.  I can edit the query in 2003 on my laptop with no ill effects.





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




How to create Fact Table and Dimention Table in SSAS 2005 ?


Hi friends,

I am very new to SSAS world.

My question, can any one guide me , how to create Fact Table and Dimention Table ?


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?



CTE Query as Derived Table


Can a query with CTE be used as derived table? The following attempt fails. Thanks.

WITH CTE AS (SELECT ProductName=Name, Price=ListPrice, ProductID
       FROM AdventureWorks2008.Production.Product)

Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM

convert query to derived table


I have the query like that:

SELECT orderid, orderdate, custid, empid,
		  ROW_NUMBER() OVER (PARTITION BY orderdate ORDER BY orderid) rownum
FROM [TestSQL].[Sales].[Orders]

How can I do it with derived table?



How to specify ForeignKeyName for the relationship between fact table and dimension table?


The ForeignKeyName attribtute of a relationship is greyed and empty when I edit a relationship of a data source view in SQL Server 2008 BIDS.  How can I edit it?

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.

Is there a work-around to create indexes for a view defined by a sub-query, derived table, or CTE?


I have three versions of an indexed view I'd like to create. I can create the view, but cannot create a clustered index on (CollectionID, ElementID, TimeID) because the definitions use variously a sub-query, CTE, or derived table, which SQL will not allow.

Has anyone figured a workaround for this? Any suggestions much appreciated.



VIEW [dbo].[aonCompositeFactView] WITH

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.



Query table from within CLR function where the db user has not select right on the table


I have written a CLR scalar valued function that queries a table using the context connection. This is working fine as long as the db user has select right on the table that is queried from within the CLR function. If the db user has no select right on the table I am receiving the following error:

The SELECT permission was denied on the object '...', database '...', schema 'StD'.

I do not want to give the user select right on the table. How can I allow the CLR function to query the table without granting select right to the db user?

I have created the assembly with PERMISSION_SET = SAFE. Do I have to apply PERMISSION_SET = UNSAFE or is there any other way?

Thanks and kind regards
Sebastian Daser
Softfount IT Solutions

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