SSAS - Measure Group EstimatedSize and EstimatedRows properties

-
Problem:
The properties panel in SSDS for a Measure Group has two properties I'd like to learn more about: EstimatedRows and EstimatedSize. There seems to be little documentation on either of these.
I did find that I can run the Aggregation Design Wizard and it will not only count the rows, but also automatically populate the EstimatedRows property in each Measure Group. So question #1, how is this property used by SSAS and is it documented anywhere?
In my testing cube the Measure Group's EstimatedSize property is read only (grayed out). The little documentation I've found implies I can/should set this property. And question #2, why is this readonly, and is there any documentation on its use?
Solution:
There is a lack of documentation for these properties, but i will try to give some information about them:

How is this property used by SSAS and is it documented anywhere?

Referring to the SQL Server Analysis 2005 Performance Guide, which is a white paper released by Microsoft:
Once the aggregation design algorithm has identified the aggregation candidates, it performs a cost/benefit analysis of each aggregation. In order to make intelligent assessments of aggregation costs, the design algorithm analyzes statistics about the cube for each aggregation candidate. Examples of this metadata include member counts and fact table record counts. Ensuring that your metadata is up-to-date can improve the effectiveness of your aggregation design. You can define the fact table source record count in the EstimatedRows property of each measure group, and you can define attribute member count in the EstimatedCount property of each attribute.

Why is this readonly, and is there any documentation on its use?

I didn't find an official documentation for that, but while searching for this topic, i found the following answer mentioning:
The estimated row count (of each partition) is used by SSAS to determine the necessary storage space of your aggregation design.
Which means that the EstimatedSize is calculated based on the EstimatedRows property, which can explain why EstimatedSize is read only.
On the other side, the following documentation is mentioning that MeasureGroup.EstimatedSize Property is used to:
Gets or sets the estimated size of a MeasureGroup
In addition, as you mentioned this property is not eligible in Visual studio.
Based on that, we can say that this property is not read only in the Microsoft.AnalysisServices.dll assembly, but it is not enabled in Visual studio because it is calculated based on EstimatedRowsproperty. You may be able to set its value when using AMO assemblies or when building an SSAS project programatically.

Comments

Post a Comment

Popular posts from this blog

SSIS - Script Task error: Exception has been thrown by the target of an invocation.

-
Question: When i execute the SSIS package the Script Task throws the following exception: Exception has been thrown by the target of an invocation. at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor) at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams) at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript() Which is meaningless. How can i read the real error message? Answer: Exception has been thrown by the target of an invocation. Is an general exception that is thrown by Script T...
Read more

Don’t install Hadoop on Windows!

-
Image
A few years ago, I was hearing from my colleagues “don’t ever think about installing Hadoop on Windows operating system!” . I was not convinced of this saying because I am a big fan of Microsoft products, especially Windows. In the past three years, I worked on three projects where I was asked to build a Hadoop cluster on Ubuntu. The first time, it was a single-node Hadoop installation with a single Apache Spark worker. The other projects were about building a Big Data ecosystem for radiation data engineering , where the multi-node Hadoop cluster is deployed. Besides Hadoop, we installed and configured Apache Kafka, Spark, Hive, Pig, and Flume installations (I have published some installation guides previously, you can check the links at the end of this article). It was hard to become familiar with those technologies for the first time since they don’t have much documentation online. Each time I had to install Hadoop, I was thinking that why always Hadoop is installed on Linu...
Read more

SSIS - script component DateTime Formats Implicit Conversion

-
General Info These are the default formats of datetimes datatypes (when converting from string) DT_DBDATE yyyy-mm-dd DT_FILETIME yyyy-mm-dd hh:mm:ss:fff DT_DBTIME hh:mm:ss DT_DBTIME2 hh:mm:ss[.fffffff] DT_DBTIMESTAMP yyyy-mm-dd hh:mm:ss[.fff] DT_DBTIMESTAMP2 yyyy-mm-dd hh:mm:ss[.fffffff] DT_DBTIMESTAMPOFFSET yyyy-mm-dd hh:mm:ss[.fffffff] [{+|-} hh:mm] Note: DT_DATE and DT_DBTIMESTAMP has the same SET method And i think that converting string to date is depending also on your current culture info More detailed information are found here https://msdn.microsoft.com/en-us/library/ms141036.aspx http://www.bidn.com/blogs/DevinKnight/ssis/1387/ssis-to-sql-server-data-type-translations Experiments : I didn't find any related article on this issue so i made the following experiments: SSIS Implicit datetime Conversion i created a SSIS package with a Dataflowtask. in this dataflowtask i created a Script Component (as a Source) and a Flat File Destin...
Read more