Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
measure group query

已答覆 measure group query

  • Tuesday, January 22, 2013 4:48 PM
     
     
    Sign In to Vote
    0

    ho do i view measure group query definition?? i have measure name Pcount. i went into to the partition section of the cube, expnaded the measure group, right click properties, source--> select query binding and it shows the following query 

    SELECT [Cbe].[col1], [Cbe].[col2],  [Cbe].[col3]

    FROM [Cbe].[Fact_T]
    WHERE

    so, there is nothing after the WHERE? how do i get the query definition? 

     

All Replies

  • Tuesday, January 22, 2013 5:23 PM
    Moderator
     
     
    Sign In to Vote
    0

    Hi,

    You need to check the source for the measures in the measure group. Right click on a measure in the cube project and look at the properties.

    It can be a named query or a named calculations in this case you will se these objects in the data source view of the SSAS cube project.

    If not the source can be a view or a table in the data source outside of the cube.

    BR

    Thomas Ivarsson

     
  • Wednesday, January 23, 2013 3:00 AM
    Moderator
     
     Answered
    Sign In to Vote
    0

    ho do i view measure group query definition?? i have measure name Pcount. i went into to the partition section of the cube, expnaded the measure group, right click properties, source--> select query binding and it shows the following query 

    SELECT [Cbe].[col1], [Cbe].[col2],  [Cbe].[col3]

    FROM [Cbe].[Fact_T]
    WHERE

    so, there is nothing after the WHERE? how do i get the query definition? 

    When you said "select query binding" does that mean that it was originally set to "Table Binding"? If this is the case then there is no WHERE clause. When you change the binding from Table to Query SSMS assumes that you must be going to apply a filter to the partition and adds a blank WHERE in preparation for this.

    http://darren.gosbell.com - please mark correct answers

     
  • Thursday, January 24, 2013 6:03 AM
    Moderator
     
     Answered
    Sign In to Vote
    0

    ho do i view measure group query definition?? i have measure name Pcount. i went into to the partition section of the cube, expnaded the measure group, right click properties, source--> select query binding and it shows the following query 

    SELECT [Cbe].[col1], [Cbe].[col2],  [Cbe].[col3]

    FROM [Cbe].[Fact_T]
    WHERE

    so, there is nothing after the WHERE? how do i get the query definition? 

    Hi msbito,

    When you create a partition with "Query Binding" binding type, we should type Where condition restrict partition rows manually. Please refer to the following Interne_Sales_2001 partition query:
    SELECT [dbo].[FactInternetSales].[ProductKey],[dbo].[FactInternetSales].[OrderDateKey],[dbo].[FactInternetSales].[DueDateKey],[dbo].[FactInternetSales].[ShipDateKey], [dbo].[FactInternetSales].[CustomerKey], [dbo].[FactInternetSales].[PromotionKey],[dbo].[FactInternetSales].[CurrencyKey],[dbo].[FactInternetSales].[SalesTerritoryKey],[dbo].[FactInternetSales].[SalesOrderNumber],[dbo].[FactInternetSales].[SalesOrderLineNumber],[dbo].[FactInternetSales].[RevisionNumber],[dbo].[FactInternetSales].[OrderQuantity],[dbo].[FactInternetSales].[UnitPrice],[dbo].[FactInternetSales].[ExtendedAmount],[dbo].[FactInternetSales].[UnitPriceDiscountPct],[dbo].[FactInternetSales].[DiscountAmount],[dbo].[FactInternetSales].[ProductStandardCost],[dbo].[FactInternetSales].[TotalProductCost],[dbo].[FactInternetSales].[SalesAmount],[dbo].[FactInternetSales].[TaxAmt],[dbo].[FactInternetSales].[Freight],[dbo].[FactInternetSales].[CarrierTrackingNumber],[dbo].[FactInternetSales].[CustomerPONumber]
      FROM [dbo].[FactInternetSales]
                                    WHERE OrderDateKey <= '20011231'

    Note: We can click the "Check" to verify the syntax is correct.

    For more information, please see:
    How To Define Measure Group Partitions in SQL Server Analysis Services SSAS: http://www.mssqltips.com/sqlservertip/1549/how-to-define-measure-group-partitions-in-sql-server-analysis-services-ssas/

    Another article about discussing Dynamic Cube Partitioning in SSAS 2008 for your reference, please see:
    http://sql-bi-dev.blogspot.com/2010/12/dynamic-cube-partitioning-in-ssas-2008.html

    Regards,

    Elvis Long
    TechNet Community Support

     
  • Friday, January 25, 2013 10:19 AM
     
     
    Sign In to Vote
    0

    hi,

    if you need to see the measure group query definition you have to goto that particular measure group and right click on the measure there in the properties see the source there you can find the table ID and column ID. BY this we can know the table id then we have to check the DSV to know if the definition is there in a named calculation or in a named query else check in the DB it may be available in the table or in the VIew.

    s dhwani

     
  • Friday, January 25, 2013 2:44 PM
     
     
    Sign In to Vote
    0

    thank you all for your replies and very much appreciated now i know why is the where clause added in the end. 

    regards,