SSAS partitions query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Analysis Server and Reporting Services (2008)

SSAS partitions query

Author

Topic

niha.736
Starting Member

4 Posts

Posted - 2012-10-24 : 07:33:16

Hi
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 we change this query to make monthly partitions

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-10-24 : 07:58:03

Do you have a date key on the fact table?
You would usually use that.

Consider partitioning for current data only - previous years are static and create partitions going forward. Makes things easier to handle imho.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

niha.736
Starting Member

4 Posts

Posted - 2012-10-29 : 04:34:29

Thanks and the best way to make partitions is this right.
Thanks Alot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources