SSAS partitions

• niha.736

  Ten Centuries

  Points: 1210

  More actions

  October 24, 2012 at 3:34 am

  #393449

  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

• robert.gerald.taylor

  SSChampion

  Points: 13133

  More actions

  October 24, 2012 at 8:12 am

  #1552294

  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:

  • http://www.mssqltips.com/sqlservertip/1549/how-to-define-measure-group-partitions-in-sql-server-analysis-services-ssas/
  • http://msdn.microsoft.com/en-us/library/ms175604.aspx
  • http://blogs.msdn.com/b/sqlcat/archive/2007/03/05/ssas-partition-slicing.aspx

  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