SSAS partitions

  • Hi all,

    SELECT

    dbo.FactStoreSalesInvoice.StoreSalesInvoiceRowInfoKey,

    dbo.FactStoreSalesInvoice.ProductKey,

    dbo.FactStoreSalesInvoice.VendorKey, dbo.FactStoreSalesInvoice.DeliveryCustomerKey, dbo.FactStoreSalesInvoice.BillingCustomerKey, dbo.FactStoreSalesInvoice.PayerCustomerKey, dbo.FactStoreSalesInvoice.SoldToCustomerKey, bo.FactStoreSalesInvoice.StoreKey,

    dbo.FactStoreSalesInvoice.SalesDateKey, dbo.FactStoreSalesInvoice.DeliveryDateKey, dbo.FactStoreSalesInvoice.BillingDateKey, dbo.FactStoreSalesInvoice.EmployeeKey, dbo.FactStoreSalesInvoice.DocumentCurrencyKey, dbo.FactStoreSalesInvoice.LocalCurrencyKey, dbo.FactStoreSalesInvoice.SalesOrganizationKey, dbo.FactStoreSalesInvoice.CampaignKey, dbo.FactStoreSalesInvoice.PayerAttributeKey, dbo.FactStoreSalesInvoice.ShipToAttributeKey, dbo.FactStoreSalesInvoice.BillToAttributeKey, dbo.FactStoreSalesInvoice.SoldToAttributeKey, dbo.FactStoreSalesInvoice.ChainCustomerSalesKey, dbo.FactStoreSalesInvoice.[Sales Price Before Discounts Document Currency], dbo.FactStoreSalesInvoice.[Sales Price Before Discounts Local Currency], dbo.FactStoreSalesInvoice.[B2B Sales Document Currency], dbo.FactStoreSalesInvoice.[B2B Sales Local Currency], dbo.FactStoreSalesInvoice.[POS Sales Document Currency], dbo.FactStoreSalesInvoice.[POS Sales Local Currency], dbo.FactStoreSalesInvoice.[PTC Sales Document Currency], dbo.FactStoreSalesInvoice.[PTC Sales Local Currency], dbo.FactStoreSalesInvoice.[Stock Sales Document Currency], dbo.FactStoreSalesInvoice.[Stock Sales Local Currency], dbo.FactStoreSalesInvoice.[Stock Sales COGS Document Currency], dbo.FactStoreSalesInvoice.[Stock Sales COGS Local Currency], dbo.FactStoreSalesInvoice.[Sales With VAT Document Currency], dbo.FactStoreSalesInvoice.[Sales With VAT Local Currency], dbo.FactStoreSalesInvoice.[Sales Without Tax Document Currency], dbo.FactStoreSalesInvoice.[Sales Without Tax Local Currency], dbo.FactStoreSalesInvoice.[Sales Margin Document Currency], dbo.FactStoreSalesInvoice.[Sales Margin Local Currency], dbo.FactStoreSalesInvoice.[Cost Document Currency], dbo.FactStoreSalesInvoice.[Cost Local Currency], bo.FactStoreSalesInvoice.[Discount Document Currency], dbo.FactStoreSalesInvoice.[Discount Local Currency],

    dbo.FactStoreSalesInvoice.[Sales Price Average Document Currency], dbo.FactStoreSalesInvoice.[Sales Price Average Local Currency], dbo.FactStoreSalesInvoice.[Sales Before Discounts Document Currency], dbo.FactStoreSalesInvoice.[Sales Before Discounts Local Currency], dbo.FactStoreSalesInvoice.[Sales BU], dbo.FactStoreSalesInvoice.[Promotion Sales Document Currency], dbo.FactStoreSalesInvoice.[Promotion Sales Local Currency], dbo.FactStoreSalesInvoice.[Promotion Sales BU], dbo.FactStoreSalesInvoice.[Promotion Sales Margin Document Currency], dbo.FactStoreSalesInvoice.[Promotion Sales Margin Local Currency], dbo.FactStoreSalesInvoice.[MAP Document Currency], dbo.FactStoreSalesInvoice.[MAP Local Currency],

    dbo.FactStoreSalesInvoice.[YP Sales Document Currency], dbo.FactStoreSalesInvoice.[YP Sales Local Currency], dbo.FactStoreSalesInvoice.InsertAuditKey,

    dbo.DimCampaign.[Campaign Price Euro],

    CASE WHEN CustomerAccountGroupID <> 'ZF11' THEN [Sales Without Tax Local Currency] ELSE 0 END AS LoyaltySales, DATEDIFF(d,

    dbo.DimCampaign.[Earliest Delivery Date], dbo.DimCampaign.[Latest Delivery Date]) AS PromotionDays,

    CASE WHEN LoyaltyCustomerSalesIndicator = 1 THEN 1 ELSE 0 END AS LoyalCustomer,

    CASE WHEN LoyaltyCustomerSalesIndicator = 1 THEN [Sales Without Tax Local Currency] ELSE 0 END AS LoyaltyCustomerSales, dbo.DimStore.StoreID,

    dbo.FactStoreSalesInvoice.MaterialPlantAttributeKey, dbo.FactStoreSalesInvoice.MaterialSalesAttributeKey, dbo.FactStoreSalesInvoice.[Discount With VAT Local Currency], dbo.FactStoreSalesInvoice.[Sales Margin With VAT Local Currency], dbo.FactStoreSalesInvoice.[Cost With VAT Local Currency], dbo.FactStoreSalesInvoice.[Cost per BUn With VAT Local Currency], dbo.FactStoreSalesInvoice.[Promotion Sales Margin With VAT Local Currency], dbo.FactStoreSalesInvoice.[Promotion Sales With VAT Local Currency], dbo.FactStoreSalesInvoice.[Sales Before With VAT Discounts Local Currency], dbo.FactStoreSalesInvoice.[Sales Margin With VAT (Calculated Scrapping) Local Currency], CAST(1 AS INT) AS VersionID, dbo.FactStoreSalesInvoice.[Sales Margin wo VAT (Calculated Scrapping) Local Currency],

    dbo.FactStoreSalesInvoice.[MAP Local Currency with VAT], dbo.FactStoreSalesInvoice.[Cost per BUn wo VAT Local Currency], dbo.FactStoreSalesInvoice.[B2B Sales Local Currency with VAT], ISNULL(DATEDIFF(d,

    DimCampaign.[On Sales From], DimCampaign.[On Sales To]), 0) AS CampaignDays,

    dbo.FactStoreSalesInvoice.[Cost before correction Local Currency], dbo.FactStoreSalesInvoice.[Sales Margin Before Correction Local Currency], dbo.FactStoreSalesInvoice.[Purchase invoice actual cost € Local Currency]

    FROM

    dbo.FactStoreSalesInvoice INNER JOIN dbo.DimCampaign ON dbo.FactStoreSalesInvoice.CampaignKey = dbo.DimCampaign.CampaignKey INNER JOIN

    DimCustomer ON dbo.FactStoreSalesInvoice.SoldToCustomerKey = dbo.DimCustomer.CustomerKey INNER JOIN

    dbo.DimStoreSalesInvoiceRowInfo ON

    dbo.FactStoreSalesInvoice.StoreSalesInvoiceRowInfoKey = dbo.DimStoreSalesInvoiceRowInfo.StoreSalesInvoiceRowInfoKey INNER JOIN

    dbo.DimStore ON dbo.FactStoreSalesInvoice.StoreKey = dbo.DimStore.StoreKey INNER JOIN

    dbo.DimProduct ON dbo.FactStoreSalesInvoice.ProductKey = dbo.DimProduct.ProductKey

    WHERE (DimProduct.MC0 NOT IN

    how can i start to thinking to make partitions for store cube with above query

  • Look at using a SQL formatter (such as: http://poorsql.com/ ) and wrapping your code in tags (located to the left of the editor textbox) to make your posts more readable.

    Your code would look something like:

    SELECT dbo.FactStoreSalesInvoice.StoreSalesInvoiceRowInfoKey,

    dbo.FactStoreSalesInvoice.ProductKey,

    dbo.FactStoreSalesInvoice.VendorKey,

    dbo.FactStoreSalesInvoice.DeliveryCustomerKey,

    dbo.FactStoreSalesInvoice.BillingCustomerKey,

    dbo.FactStoreSalesInvoice.PayerCustomerKey,

    dbo.FactStoreSalesInvoice.SoldToCustomerKey,

    dbo.FactStoreSalesInvoice.StoreKey,

    dbo.FactStoreSalesInvoice.SalesDateKey,

    dbo.FactStoreSalesInvoice.DeliveryDateKey,

    dbo.FactStoreSalesInvoice.BillingDateKey,

    dbo.FactStoreSalesInvoice.EmployeeKey,

    dbo.FactStoreSalesInvoice.DocumentCurrencyKey,

    dbo.FactStoreSalesInvoice.LocalCurrencyKey,

    dbo.FactStoreSalesInvoice.SalesOrganizationKey,

    dbo.FactStoreSalesInvoice.CampaignKey,

    dbo.FactStoreSalesInvoice.PayerAttributeKey,

    dbo.FactStoreSalesInvoice.ShipToAttributeKey,

    dbo.FactStoreSalesInvoice.BillToAttributeKey,

    dbo.FactStoreSalesInvoice.SoldToAttributeKey,

    dbo.FactStoreSalesInvoice.ChainCustomerSalesKey,

    dbo.FactStoreSalesInvoice.[Sales Price Before Discounts Document Currency],

    dbo.FactStoreSalesInvoice.[Sales Price Before Discounts Local Currency],

    dbo.FactStoreSalesInvoice.[B2B Sales Document Currency],

    dbo.FactStoreSalesInvoice.[B2B Sales Local Currency],

    dbo.FactStoreSalesInvoice.[POS Sales Document Currency],

    dbo.FactStoreSalesInvoice.[POS Sales Local Currency],

    dbo.FactStoreSalesInvoice.[PTC Sales Document Currency],

    dbo.FactStoreSalesInvoice.[PTC Sales Local Currency],

    dbo.FactStoreSalesInvoice.[Stock Sales Document Currency],

    dbo.FactStoreSalesInvoice.[Stock Sales Local Currency],

    dbo.FactStoreSalesInvoice.[Stock Sales COGS Document Currency],

    dbo.FactStoreSalesInvoice.[Stock Sales COGS Local Currency],

    dbo.FactStoreSalesInvoice.[Sales With VAT Document Currency],

    dbo.FactStoreSalesInvoice.[Sales With VAT Local Currency],

    dbo.FactStoreSalesInvoice.[Sales Without Tax Document Currency],

    dbo.FactStoreSalesInvoice.[Sales Without Tax Local Currency],

    dbo.FactStoreSalesInvoice.[Sales Margin Document Currency],

    dbo.FactStoreSalesInvoice.[Sales Margin Local Currency],

    dbo.FactStoreSalesInvoice.[Cost Document Currency],

    dbo.FactStoreSalesInvoice.[Cost Local Currency],

    bo.FactStoreSalesInvoice.[Discount Document Currency],

    dbo.FactStoreSalesInvoice.[Discount Local Currency],

    dbo.FactStoreSalesInvoice.[Sales Price Average Document Currency],

    dbo.FactStoreSalesInvoice.[Sales Price Average Local Currency],

    dbo.FactStoreSalesInvoice.[Sales Before Discounts Document Currency],

    dbo.FactStoreSalesInvoice.[Sales Before Discounts Local Currency],

    dbo.FactStoreSalesInvoice.[Sales BU],

    dbo.FactStoreSalesInvoice.[Promotion Sales Document Currency],

    dbo.FactStoreSalesInvoice.[Promotion Sales Local Currency],

    dbo.FactStoreSalesInvoice.[Promotion Sales BU],

    dbo.FactStoreSalesInvoice.[Promotion Sales Margin Document Currency],

    dbo.FactStoreSalesInvoice.[Promotion Sales Margin Local Currency],

    dbo.FactStoreSalesInvoice.[MAP Document Currency],

    dbo.FactStoreSalesInvoice.[MAP Local Currency],

    dbo.FactStoreSalesInvoice.[YP Sales Document Currency],

    dbo.FactStoreSalesInvoice.[YP Sales Local Currency],

    dbo.FactStoreSalesInvoice.InsertAuditKey,

    dbo.DimCampaign.[Campaign Price Euro],

    CASE

    WHEN CustomerAccountGroupID <> 'ZF11'

    THEN [Sales Without Tax Local Currency]

    ELSE 0

    END AS LoyaltySales,

    DATEDIFF(d, dbo.DimCampaign.[Earliest Delivery Date], dbo.DimCampaign.[Latest Delivery Date]) AS PromotionDays,

    CASE

    WHEN LoyaltyCustomerSalesIndicator = 1

    THEN 1

    ELSE 0

    END AS LoyalCustomer,

    CASE

    WHEN LoyaltyCustomerSalesIndicator = 1

    THEN [Sales Without Tax Local Currency]

    ELSE 0

    END AS LoyaltyCustomerSales,

    dbo.DimStore.StoreID,

    dbo.FactStoreSalesInvoice.MaterialPlantAttributeKey,

    dbo.FactStoreSalesInvoice.MaterialSalesAttributeKey,

    dbo.FactStoreSalesInvoice.[Discount With VAT Local Currency],

    dbo.FactStoreSalesInvoice.[Sales Margin With VAT Local Currency],

    dbo.FactStoreSalesInvoice.[Cost With VAT Local Currency],

    dbo.FactStoreSalesInvoice.[Cost per BUn With VAT Local Currency],

    dbo.FactStoreSalesInvoice.[Promotion Sales Margin With VAT Local Currency],

    dbo.FactStoreSalesInvoice.[Promotion Sales With VAT Local Currency],

    dbo.FactStoreSalesInvoice.[Sales Before With VAT Discounts Local Currency],

    dbo.FactStoreSalesInvoice.[Sales Margin With VAT (Calculated Scrapping) Local Currency],

    CAST(1 AS INT) AS VersionID,

    dbo.FactStoreSalesInvoice.[Sales Margin wo VAT (Calculated Scrapping) Local Currency],

    dbo.FactStoreSalesInvoice.[MAP Local Currency with VAT],

    dbo.FactStoreSalesInvoice.[Cost per BUn wo VAT Local Currency],

    dbo.FactStoreSalesInvoice.[B2B Sales Local Currency with VAT],

    ISNULL(DATEDIFF(d, DimCampaign.[On Sales From], DimCampaign.[On Sales To]), 0) AS CampaignDays,

    dbo.FactStoreSalesInvoice.[Cost before correction Local Currency],

    dbo.FactStoreSalesInvoice.[Sales Margin Before Correction Local Currency],

    dbo.FactStoreSalesInvoice.[Purchase invoice actual cost € Local Currency]

    FROM dbo.FactStoreSalesInvoice

    INNER JOIN dbo.DimCampaign ON dbo.FactStoreSalesInvoice.CampaignKey = dbo.DimCampaign.CampaignKey

    INNER JOIN DimCustomer ON dbo.FactStoreSalesInvoice.SoldToCustomerKey = dbo.DimCustomer.CustomerKey

    INNER JOIN dbo.DimStoreSalesInvoiceRowInfo ON dbo.FactStoreSalesInvoice.StoreSalesInvoiceRowInfoKey = dbo.DimStoreSalesInvoiceRowInfo.StoreSalesInvoiceRowInfoKey

    INNER JOIN dbo.DimStore ON dbo.FactStoreSalesInvoice.StoreKey = dbo.DimStore.StoreKey

    INNER JOIN dbo.DimProduct ON dbo.FactStoreSalesInvoice.ProductKey = dbo.DimProduct.ProductKey

    WHERE (DimProduct.MC0 NOT IN)

    Now with that formatting aside, I'd say to get started take a look at:

    It looks like this is sales data; is there natural way to break out the data? Perhaps partition by sales year or sales month?

    HTH,

    Rob

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply