2

I use SSAS Tabular in In-Memory mode. I also use partitions to divide my data. So I will have one partition per month.

AFAIK, when using In-Memory mode, server keeps processed data in memory forever.

My concern is, if after a couple of years, my data get big so that SSAS ran out of RAM, what should I do?

  • Is it possible to remove old partitions from RAM?
  • Can I convert old partitions to use DirectQuery, while newer partitions still use In-Memory mode?
1

For the "Is it possible to remove old partitions from RAM?" portion of your question:

Yes.

The BOL entry for Process Database, Table, or Partition mentions Process Clear in the "To process one or more partitions" section. Effectively you could issue an XMLA command to Process Clear a specific partition (i.e. an old partition).

Here's a method to get the SSMS GUI to generate the XMLA command to clear a specific partition.

In SSMS, while connected to your SSAS Tabular server, expand the Tables node, then right click any of the tables and select "Partitions..." which will bring up a new window UI.

Then, click the button that looks like 3 green arrows in a circle (Process) which will bring up yet another new window UI.

Next, Check the partition(s) that you want to clear.

Then, in the Mode dropdown, select Process Clear.

Next, click the Script button at the top, and it will generate the XMLA code in a new query window. You'll get something like below with the object names (with seemingly random GUIDs on the end of some of them):

<Process xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Type>ProcessClear</Type>
  <Object>
    <DatabaseID>YourTabularDatabase</DatabaseID>
    <CubeID>Model</CubeID>
    <MeasureGroupID>Your Measure Group_06e45707-4027-41d9-8fbd-9217a864d859</MeasureGroupID>
    <PartitionID>Your Partition_06e45707-4027-41d9-8fbd-9217a864d859</PartitionID>
  </Object>
</Process>

You can then save the script and execute it at-will, or copy the script and have an Agent Job run the code as a SQL Server Analysis Services Command type of job step.

For the "Can I convert old partitions to use DirectQuery, while newer partitions still use In-Memory mode?" portion of your question:

Yes.

The BOL entry for Partitions and DirectQuery Mode discusses this some.

In SSDT, within the Partition Manager (Table menu | Partitions), you can set specific Partitions to be either DirectQuery or In-Memory (Set As Sample). When a partition is set to DirectQuery, it will have (DirectQuery) with the parentheses appended to the front of it.

Incidentally, if you want to know how much memory objects in your Tabular model is using, check out Kasper de Jonge's blog post on What is using all that memory on my Analysis server instance ?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy