SSAS - TMSL Query and Monthly Partition , how to add Filter

Question

Hello Experts,

Trust you are all are doing good.

Having following environments, one SQL server for DWH and another Enterprise Edition of SSAS hosting Tabular model(TM).

DWH design is Star Schema based, where the fact table would receive roughly 1 million entry per day and in the TM have to maintain 10 years of data.

To process the heavy fact table, have created Monthly Partitions like Jun/July/Aug 2024 etc.. to load the incremental data on daily basis.

When generating the TMSL from SSAS Script below is output

-- Process Partition Data

{

"refresh": {

"type": "add",

"objects": [

  {

    "database": "audex-analyse-Tst",

    "table": "audexdw fact_viewing_session_member",

    "partition": "Partition_Sept2024"

  }

]

}

}

Since its a monthly partition means on every 1st to 30th on respective month the partition will load data from 1st till previous day, I mean on 2nd it will load 1st day of data, on 3rd will load 1st and 2nd .. on 30th it will load data from1st till 29th etc.

This design behavior I wanted to avoid and would like to add where clause in TMSL to filter data like on 2nd it only process 1st and .. and on 30th it only process 29th day of data.

Unfortunately I am not getting any way to add a filter in TMSL to complete the above script, can you guys please suggest best way of processing the such scenario of TM where on respective dates only incremental data get loaded into fact table?

Thanks in advance.

Regards

Prashant Pandey

Answer 1

Hi @PANDEY Prashant

For SSAS Tabular Model, you could use DAX functions to define a calculated column or measure to filter the data.

Check this sample:

IsInDateRange =
VAR StartOfMonth = DATE(YEAR(TODAY()), MONTH(TODAY()), 1)
VAR EndOfRange = TODAY() - 1
RETURN
    IF(
        'YourTable'[DateColumn] >= StartOfMonth &&
        'YourTable'[DateColumn] <= EndOfRange,
        TRUE,
        FALSE
    )

Best regards,

Cosmog

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".