I am creating a partition for the last 14 days of data, but when dealing with the slice, I don't know how to handle that, Is there a way to slice for an interval of 14 days for example (From today to 05/02/19)

<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
    <ParentObject>
        <DatabaseID>test_db</DatabaseID>
        <CubeID>HOURLY_STM_VOLUME</CubeID>
        <MeasureGroupID>HOURLY_STM_VOLUME</MeasureGroupID>
    </ParentObject>
    <ObjectDefinition>
        <Partition xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400">
            <ID>PA_HOURLY_STM_VOLUME_LAST_14DAYS</ID>
            <Name>PA_HOURLY_STM_VOLUME_LAST_14DAYS</Name>
            <Source xsi:type="QueryBinding">
                <DataSourceID>ctxsb_as</DataSourceID>
                <QueryDefinition>SELECT [PA_HOURLY_STM_VOLUME].[LOCATION],[PA_HOURLY_STM_VOLUME].[DAY],[PA_HOURLY_STM_VOLUME].[MATERIAL],[PA_HOURLY_STM_VOLUME].[SHIP_TO],[PA_HOURLY_STM_VOLUME].[TIME],[PA_HOURLY_STM_VOLUME].[DOW],[PA_HOURLY_STM_VOLUME].[HOUR],[PA_HOURLY_STM_VOLUME].[LOCMAT],[PA_HOURLY_STM_VOLUME].[PUMP_PRICE],[PA_HOURLY_STM_VOLUME].[TRANSFER_PRICE],[PA_HOURLY_STM_VOLUME].[INLAND_DIST_FREIGHT],[PA_HOURLY_STM_VOLUME].[COMMISSION],[PA_HOURLY_STM_VOLUME].[VOLUME],[PA_HOURLY_STM_VOLUME].[EXCISE_AMOUNT],[PA_HOURLY_STM_VOLUME].[GST],[PA_HOURLY_STM_VOLUME].[MOPS_INDEX],1 AS [HOURLY_STM_PRICE_CNT],(( ISNULL([PUMP_PRICE],0) - ISNULL([COMMISSION],0) ) - ISNULL([EXCISE_AMOUNT],0) - ISNULL([INLAND_DIST_FREIGHT],0) - ISNULL([TRANSFER_PRICE],0) - ISNULL([GST],0)) AS [DFM],(( ( ( ISNULL([PUMP_PRICE],0) - ISNULL([COMMISSION],0) ) - ISNULL([EXCISE_AMOUNT],0) - ISNULL([INLAND_DIST_FREIGHT],0) - ISNULL([TRANSFER_PRICE],0) - ISNULL([GST],0) ) * ISNULL([VOLUME],0) )) AS [DFM_EXT],(( ISNULL([PUMP_PRICE],0) - ISNULL([COMMISSION],0) )) AS [NET_INVOICE_PRICE],(( ISNULL([PUMP_PRICE],0) * ISNULL([VOLUME],0) )) AS [REVENUE]
  FROM [PA_HOURLY_STM_VOLUME]
        WHERE [DAY] &gt;= CONVERT(VARCHAR(10), GETDATE() - 14, 112)</QueryDefinition>
            </Source>
            <StorageMode>Molap</StorageMode>
            <ProcessingMode>Regular</ProcessingMode>
            <Slice>[DATE].[DATE].[DAY].&amp;[20190502]</Slice>
            <ProactiveCaching>
                <SilenceInterval>-PT1S</SilenceInterval>
                <Latency>-PT1S</Latency>
                <SilenceOverrideInterval>-PT1S</SilenceOverrideInterval>
                <ForceRebuildInterval>-PT1S</ForceRebuildInterval>
                <Source xsi:type="ProactiveCachingInheritedBinding" />
            </ProactiveCaching>
        </Partition>
    </ObjectDefinition>
</Create>

Slicing part (Not working as expected)

<Slice>[DATE].[DATE].[DAY].&amp;[20190502]</Slice>

0 Answers