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

PANDEY Prashant 125 Reputation points
2024-09-15T05:21:37.1933333+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,810 questions
SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,284 questions
0 comments No comments
{count} votes

Accepted answer
  1. LiHongMSFT-4306 27,016 Reputation points
    2024-09-16T01:32:46.6033333+00:00

    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".

    1 person found this answer helpful.
    0 comments No comments

0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.