Show Cell Text options use in Cube view

[Madesh]

Hi,

We have a requirement to get the Text fields for 5 columns. So, getting these we have used the "Data Attachment Members in the View Dimension: Annotation, Assumptions, AuditComment, Footnote and VarianceExplanation" and able to get the required text with using Member formulas. Could you please let us know if there are any performance/other impacts with using more cell text options use in Cube views.

Thanks,

Madesh

[TheJonG]

The performance impact will come from the cube view having to run dynamic calcs but there is no difference in performance between returning text vs a number. 

[TonyToniTone]

To expand upon TheJonG.  In general, performance is no different in returning a number from a data cell vs. text.  For instance, take a statement such as "ThereIsAVarianceDueToPandemic" for text.  Each data cell number takes 50 bytes for retrieval/transport/storage from the database to a Cube View or Excel.  Each character entered into a data cell as text consumes 2 bytes.  For the statement, "ThereIsAVarianceDueToPandemic", 29 characters exist x 2 bytes per character = 58 bytes.  So not much different than a data cell with a number and really no performance impact.  The Text column in the Data Attachment table is setup as a data type as nvarchar(max).  This means that each View member associated to Data Attachment such as Annotation, Assumptions, etc. can store up to 2 GB of characters or 4000 characters.  However, even though this data type allows you to store that many characters, it isn't wise to write a book for each data cell, especially if you have many writable text data cells.  So this is all relative.  If I have 10,000 data cells with numbers in a Cube View or Excel, this requires 500,000 bytes for storage/memory/transport.  If I have a comment for each one of 10,000 data cells and each comment averages 50 characters, then this would require 1,000,000 bytes ( 50 characters x 2 bytes/character x 10,000 data cells ) for storage/memory/transport to display in a Cube View or Excel.  This simple example is not likely but this shows the potential.  In this use case, depending on how you setup your Cube View or Excel, commentary will dramatically impact reporting performance.  

In another situation, you could have 10,000 data cells with numbers but only have 100 data cells with text that average 50 characters per data cell.  500,000 bytes for the 10,000 data cells with numbers vs. 10,000 bytes for the 100 data cells with text.  In this case, commentary will unlikely impact performance much.  

So if you are comparing a single data cell with text vs. a data cell as a number in a silo, they should be very comparable for performance up to ~ 50 characters.  If each data cell with text ends up being 4,000 characters, then you may see a performance impact.  Just depends.