none
Please need help adding new measure creates a new measure group RRS feed

  • Question

  • Hello Gurus,

    In an existing cube when i added a new measure it got created under a new measure group. This is causing lots of issues. Its taking forever to process the cube. Since i added four measures, it created four different measure groups. My other issue is i do not see an option to partition by date and make the processing faster and simpler. Please advice.

    Thanks

    Monday, November 12, 2018 3:42 PM

Answers

  • As Darren said, Distinct Count measures are implemented with every distinct value stored within every cell. This helps querying, but with a large number of distinct values, it can make processing arduous. There is an alternative to using Distinct Count measures, which costs zero on processing. The MDX query will dynamically count the distinct members of a dimension. Note, using this approach, there will be no processing overhead, but your "Distinct Count" queries might be slower. 

    See http://richardlees.blogspot.com/2008/10/alternative-to-physical-distinct-count.html for more info.

    Note, for this technique, there must be a dimension using a key of the "Distinct Count" value.

    Hope that helps,


    Richard

    Tuesday, November 13, 2018 12:39 AM
  • Hi LisaKruger,

    Based on my research,   change something when the updated solution is deployed will  cause the cube to move to “Unprocessed” status and needs new full process. This is by design. You could refer to SSAS: Which change makes the cube/dimension unprocessed? for details.

    In  addition, I wonder whether “Date partition” is partition you create based on date for corresponding measure group? If so, you could try to refer to Create and Manage a Local Partition for details.

    Best Regards,

    Zoe Zhi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by LisaKruger Friday, November 16, 2018 2:45 PM
    Tuesday, November 13, 2018 8:42 AM
  • I have deleted the measure groups that i created for the distinct counts. I created partitions using Query binding and limiting data for one year. I have a fact table with 46 million records , i divided the data across four years and restricted each partition for one year. The processing is very very slow and taking forever , please advice.

    Using a rough rule of thumb of being able to read 50,000 rows per second you should be able to read the entire 46 million in around 15 minutes. If it's taking significantly longer than that you first need to figure out where the bottleneck is. If the source system is not sending the data fast enough you need to look at optimizing that by doing things like adding indexes, changing your data structures or adding/improving the hardware.

    Otherwise if you think the issue is on the SSAS side it's worth reading both of the following:

    SSAS Performance Guide
    https://msdn.microsoft.com/en-us/library/dn749781.aspx?f=255&MSPPError=-2147217396

    SSAS Operations Guide
    https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/hh226085(v=msdn.10)

    These whitepapers are both a few years old now, but the information in them and the techniques they describe still apply to later SSAS multi-dimensional versions upto and including 2017.


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

    • Marked as answer by LisaKruger Friday, November 16, 2018 2:45 PM
    Wednesday, November 14, 2018 2:44 AM
    Moderator
  • Thanks Zoe Zhi,
    Tried different methods  like backup and restore and others finally had luck with the XMLA script. Thanks everyone

    Thanks

    • Marked as answer by LisaKruger Friday, November 16, 2018 2:45 PM
    Friday, November 16, 2018 2:45 PM

All replies

  • Hi All,

    I created a cube with default measures , later when i tried to add a new measure its getting created as a new measure group instead of new measure. 

    My second issue is in the older version (BIDS) i used partitions based off select statements filtering by year, in this new version i do not see an option of creating partitions using the select statement. Below is the version information.

    Microsoft Visual Studio Professional 2015
    Version 14.0.25431.01 Update 3
    Microsoft .NET Framework
    Version 4.7.03056

    Installed Version: Professional

    Microsoft Visual Studio Tools for Applications 2015   00322-40000-00000-AA509
    Microsoft Visual Studio Tools for Applications 2015

    Visual Basic 2015   00322-40000-00000-AA509
    Microsoft Visual Basic 2015

    Visual C# 2015   00322-40000-00000-AA509
    Microsoft Visual C# 2015

    Visual C++ 2015   00322-40000-00000-AA509
    Microsoft Visual C++ 2015

    Application Insights Tools for Visual Studio Package   7.0.20622.1
    Application Insights Tools for Visual Studio

    ASP.NET and Web Tools 2015.1 (Beta8)   14.1.11107.0
    ASP.NET and Web Tools 2015.1 (Beta8)

    ASP.NET Web Frameworks and Tools 2012.2   4.1.41102.0
    For additional information, visit http://go.microsoft.com/fwlink/?LinkID=309563

    ASP.NET Web Frameworks and Tools 2013   5.2.40314.0
    For additional information, visit http://www.asp.net/

    Azure Data Lake Node   1.0
    This package contains the Data Lake integration nodes for Server Explorer.

    Azure Data Lake Tools for Visual Studio   2.2.6000.1
    Microsoft Azure Data Lake Tools for Visual Studio

    Azure Data Lake Tools for Visual Studio   2.2.6000.1
    Microsoft Azure Data Lake Tools for Visual Studio

    Common Azure Tools   1.8
    Provides common services for use by Azure Mobile Services and Microsoft Azure Tools.

    GitHub.VisualStudio   2.5.9.5485
    A Visual Studio Extension that brings the GitHub Flow into Visual Studio.

    JavaScript Language Service   2.0
    JavaScript Language Service

    JavaScript Project System   2.0
    JavaScript Project System

    Microsoft Azure HDInsight Azure Node   2.2.6000.1
    HDInsight Node under Azure Node

    Microsoft Azure Hive Query Language Service   2.2.6000.1
    Language service for Hive query

    Microsoft Azure Mobile Services Tools   1.4
    Microsoft Azure Mobile Services Tools

    Microsoft Azure Stream Analytics Language Service   2.2.6000.1
    Language service for Azure Stream Analytics

    Microsoft Azure Tools   2.9
    Microsoft Azure Tools for Microsoft Visual Studio 2015 - v2.9.41104.6

    NuGet Package Manager   3.4.4
    NuGet Package Manager in Visual Studio. For more information about NuGet, visit http://docs.nuget.org/.

    PreEmptive Analytics Visualizer   1.2
    Microsoft Visual Studio extension to visualize aggregated summaries from the PreEmptive Analytics product.

    SQL Server Analysis Services   14.0.1016.232
    Microsoft SQL Server Analysis Services Designer 
    Version 14.0.1016.232

    SQL Server Data Tools   14.0.61712.050
    Microsoft SQL Server Data Tools

    SQL Server Integration Services   
    Microsoft SQL Server Integration Services Designer
    Version 14.0.1000.169

    SQL Server Reporting Services   14.0.1016.232
    Microsoft SQL Server Reporting Services Designers 
    Version 14.0.1016.232

    ToolWindowHostedEditor   1.0
    Hosting json editor into a tool window

    TypeScript   1.8.36.0
    TypeScript tools for Visual Studio

    Friday, November 9, 2018 1:31 PM
  • when i tried to add a new measure its getting created as a new measure group instead of new measure. 

    That happens  e.g when you add a semi-additive Aggregation; but you can move the measure with drag&drop to the other measure Group.

    Are you SSAS Enterprise Edition or Standard? In Standard partitioning isn't supported.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Friday, November 9, 2018 1:52 PM
  • Thanks Olaf,

    sorry for my silly question. How can i check if its the EE or the standard?

    Friday, November 9, 2018 2:34 PM
  • this is what i see

    

    Friday, November 9, 2018 3:03 PM
  • Looks like Developer Edition, which equals Enterprise and supports partitioning. Check also your project properties for "Deployment Server Edition" settings


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Pirlo Zhang Monday, November 12, 2018 2:51 AM
    Saturday, November 10, 2018 5:22 AM
  • Looks like Developer Edition, which equals Enterprise and supports partitioning. Check also your project properties for "Deployment Server Edition" settings


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thanks Olaf,

    But i do not see an option to partition. Please can you tell me where to look for partitioning. I looked underneath the partitions tab and i do not see an option.

    Monday, November 12, 2018 3:39 PM
  • What sort of measure are you creating? Are these DistinctCount measures?

    If so this is the expected behaviour. DistinctCount measures are non-additive and need to be stored in their own measure groups.


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

    Monday, November 12, 2018 11:49 PM
    Moderator
  • As Darren said, Distinct Count measures are implemented with every distinct value stored within every cell. This helps querying, but with a large number of distinct values, it can make processing arduous. There is an alternative to using Distinct Count measures, which costs zero on processing. The MDX query will dynamically count the distinct members of a dimension. Note, using this approach, there will be no processing overhead, but your "Distinct Count" queries might be slower. 

    See http://richardlees.blogspot.com/2008/10/alternative-to-physical-distinct-count.html for more info.

    Note, for this technique, there must be a dimension using a key of the "Distinct Count" value.

    Hope that helps,


    Richard

    Tuesday, November 13, 2018 12:39 AM
  • Hi LisaKruger,

    Based on my research,   change something when the updated solution is deployed will  cause the cube to move to “Unprocessed” status and needs new full process. This is by design. You could refer to SSAS: Which change makes the cube/dimension unprocessed? for details.

    In  addition, I wonder whether “Date partition” is partition you create based on date for corresponding measure group? If so, you could try to refer to Create and Manage a Local Partition for details.

    Best Regards,

    Zoe Zhi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by LisaKruger Friday, November 16, 2018 2:45 PM
    Tuesday, November 13, 2018 8:42 AM
  • sorry
    Tuesday, November 13, 2018 4:29 PM
  • Thanks Darren,

    I have deleted the measure groups that i created for the distinct counts. I created partitions using Query binding and limiting data for one year. I have a fact table with 46 million records , i divided the data across four years and restricted each partition for one year. The processing is very very slow and taking forever , please advice.

    Tuesday, November 13, 2018 4:35 PM
  • I have deleted the measure groups that i created for the distinct counts. I created partitions using Query binding and limiting data for one year. I have a fact table with 46 million records , i divided the data across four years and restricted each partition for one year. The processing is very very slow and taking forever , please advice.

    Using a rough rule of thumb of being able to read 50,000 rows per second you should be able to read the entire 46 million in around 15 minutes. If it's taking significantly longer than that you first need to figure out where the bottleneck is. If the source system is not sending the data fast enough you need to look at optimizing that by doing things like adding indexes, changing your data structures or adding/improving the hardware.

    Otherwise if you think the issue is on the SSAS side it's worth reading both of the following:

    SSAS Performance Guide
    https://msdn.microsoft.com/en-us/library/dn749781.aspx?f=255&MSPPError=-2147217396

    SSAS Operations Guide
    https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/hh226085(v=msdn.10)

    These whitepapers are both a few years old now, but the information in them and the techniques they describe still apply to later SSAS multi-dimensional versions upto and including 2017.


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

    • Marked as answer by LisaKruger Friday, November 16, 2018 2:45 PM
    Wednesday, November 14, 2018 2:44 AM
    Moderator
  • Thanks Darren,

    I am processing the cube on my local version. This i think "Might" be the reason. We have a dev server that i was made aware of today for testing cubes with large amounts of data. How can i deploy the cube to a different server?

    Wednesday, November 14, 2018 3:40 PM
  • Hi LisaKruger,

    If you want to deploy the cube to different server, you could backup and restore, or  use XMLA script. You could refer to Deploy a Created Cube to a Different SSAS Server for details.

    Best Regards,
    Zoe Zhi




    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, November 16, 2018 3:04 AM
  • Thanks Zoe Zhi,
    Tried different methods  like backup and restore and others finally had luck with the XMLA script. Thanks everyone

    Thanks

    • Marked as answer by LisaKruger Friday, November 16, 2018 2:45 PM
    Friday, November 16, 2018 2:45 PM