none
How to get the size of each table/each column in a cube? RRS feed

  • Question

  • suppose i have a cube Cube1

    Cube1 has say 4 tables

    now each table has columns (i.e. country, region, sales, etc...)

    how can i get the size of each of these objects? perhaps it could be a powershell script but not sure if its possible to get the size of each table/column, because i dont see in SSMS a size property for the table, nor do i see a way to see the column size

    i am looking to get statistics like this:

    Cube Table Column Size Last Processed
    Cube1 Table1 sales 43KB 1/2/1234
    Cube1 Table1 region 67KB 1/3/1234
    Cube1 Table2 channel 89KB 1/3/1234
    Cube1 Table3 country 90KB 1/6/1234
    Cube1 Table4 sales 70KB 1/6/1234


    • Edited by cataster Monday, July 22, 2019 4:24 PM
    Monday, July 22, 2019 4:23 PM

All replies

  • Is this a tabular or multi-dimensional model? If it's a tabular model you can get this information using Vertipaq Analyzer https://www.sqlbi.com/tools/vertipaq-analyzer/

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

    Monday, July 22, 2019 11:12 PM
    Moderator
  • Is this a tabular or multi-dimensional model? If it's a tabular model you can get this information using Vertipaq Analyzer https://www.sqlbi.com/tools/vertipaq-analyzer/

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

    well for either. i have both multidimensional (1103 comaptibility) and tabular 2017 (1400 compatibility)

    i am not looking for an external tool but more so a custom script (i.e. powershell) that can get this info and export to a csv
    Monday, July 22, 2019 11:44 PM
  • well for either. i have both multidimensional (1103 comaptibility) and tabular 2017 (1400 compatibility)

    i am not looking for an external tool but more so a custom script (i.e. powershell) that can get this info and export to a csv

    Since you would like to use custom powershell script for the achievement, here is a thread which is talking about how to get cube database size, name, status etc. Maybe it is useful to you.

    SSAS – Find Cube Database Name, Size,Status and Total Size of SSAS Server

    Best Regards,

    Will


    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.

    Tuesday, July 23, 2019 3:29 AM
  • well for either. i have both multidimensional (1103 comaptibility) and tabular 2017 (1400 compatibility)

    i am not looking for an external tool but more so a custom script (i.e. powershell) that can get this info and export to a csv

    Since you would like to use custom powershell script for the achievement, here is a thread which is talking about how to get cube database size, name, status etc. Maybe it is useful to you.

    SSAS – Find Cube Database Name, Size,Status and Total Size of SSAS Server

    Best Regards,

    Will


    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.

    Hi will

    I want to get the table sizes and their columns size not the database size


    • Edited by cataster Tuesday, July 23, 2019 3:35 AM
    Tuesday, July 23, 2019 3:35 AM
  • There are two possible scenarios, one is about size in memory, another is on disk
    for first one there are relevant DMVs availlable:
    ------
    $SYSTEM.DISCOVER_MEMORYUSAGE
    $SYSTEM.DISCOVER_OBJECT_MEMORY_USAGE
    ------
    for second you would have to get (column/table) file sizes from disk {Data folder}
    (based on DMVs because these have right folder/file names for each object)
    ------
    $SYSTEM.TMSCHEMA_STORAGE_FILES
    $SYSTEM.TMSCHEMA_STORAGE_FOLDERS
    $SYSTEM.TMSCHEMA_TABLES
    $SYSTEM.TMSCHEMA_TABLE_STORAGES
    $SYSTEM.TMSCHEMA_PARTITIONS
    $SYSTEM.TMSCHEMA_PARTITION_STORAGES
    $SYSTEM.TMSCHEMA_COLUMN_PARTITION_STORAGES
    $SYSTEM.TMSCHEMA_COLUMN_STORAGES
    $SYSTEM.TMSCHEMA_COLUMNS
    ------
    Plus some aggregation for tables having multiple partitions.
    Tuesday, July 23, 2019 11:03 PM
  • There are two possible scenarios, one is about size in memory, another is on disk
    for first one there are relevant DMVs availlable:
    ------
    $SYSTEM.DISCOVER_MEMORYUSAGE
    $SYSTEM.DISCOVER_OBJECT_MEMORY_USAGE
    ------
    for second you would have to get (column/table) file sizes from disk {Data folder}
    (based on DMVs because these have right folder/file names for each object)
    ------
    $SYSTEM.TMSCHEMA_STORAGE_FILES
    $SYSTEM.TMSCHEMA_STORAGE_FOLDERS
    $SYSTEM.TMSCHEMA_TABLES
    $SYSTEM.TMSCHEMA_TABLE_STORAGES
    $SYSTEM.TMSCHEMA_PARTITIONS
    $SYSTEM.TMSCHEMA_PARTITION_STORAGES
    $SYSTEM.TMSCHEMA_COLUMN_PARTITION_STORAGES
    $SYSTEM.TMSCHEMA_COLUMN_STORAGES
    $SYSTEM.TMSCHEMA_COLUMNS
    ------
    Plus some aggregation for tables having multiple partitions.
    Oh man, so the only way is DMV's/files? Theres no SSAS namespaces and objects for this?
    Wednesday, July 24, 2019 12:51 AM
  • Oh man, so the only way is DMV's/files? Theres no SSAS namespaces and objects for this?

    Yes, there are no other options to support this.

    As you know, SSAS features have some limits on this, or your expected function hasn't been focused on by company which produced such product, so some third-part tools developed some information collection functions to achieve that.

    Reference

    Microsoft.AnalysisServices Namespace

    Best Regards,

    Will


    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.

    Wednesday, July 24, 2019 9:50 AM
  • Oh man, so the only way is DMV's/files? Theres no SSAS namespaces and objects for this?

    Yes, there are no other options to support this.

    As you know, SSAS features have some limits on this, or your expected function hasn't been focused on by company which produced such product, so some third-part tools developed some information collection functions to achieve that.

    Reference

    Microsoft.AnalysisServices Namespace

    Best Regards,

    Will


    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.

    ok i understand.

    how would i use the above DMV's Yuri posted?

    so in my case, what query do i need to write to get cube1 table/column sizes?
    Wednesday, July 24, 2019 5:57 PM
  • ok i understand.

    how would i use the above DMV's Yuri posted?

    so in my case, what query do i need to write to get cube1 table/column sizes?

    Hi cataster,

    Thanks for your reply.

    Since you decide to use DMV to achieve your requirement, you need to download the related documents from the two links:

    [MS-SSAS-T]: SQL Server Analysis Services Tabular Protocol

    [MS-SSAS]: SQL Server Analysis Services Protocol

    Then do some research about them, and Yuri has made some tips about that. As for which develop program( C#,PowerShell) you would use, it depends on your requirement. It would be a large project for such requirement.

    We couldn't give you a complete application code in one reply, also, it is beyond of support range. But we could lead you to right direction.

    Reference

    schema-rowsets

    Best Regards,

    Will


    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.

    Thursday, July 25, 2019 8:34 AM