Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSAS partitions


SSAS partitions

Author
Message
niha.736
niha.736
SSC Journeyman
SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)

Group: General Forum Members
Points: 78 Visits: 233
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
robert.gerald.taylor
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1330 Visits: 1399
Look at using a SQL formatter (such as: http://poorsql.com/ ) and wrapping your code in [code] 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search