October 18, 2012 at 9:31 am
Hi am trying to do partitions on monthly basis for every year in my solution.
currently am trying it for material delivery cube.
i just need right approach to do this.
SELECT [dbo].[FactMaterialDelivery].[MaterialDeliveryRowInfoKey],
[dbo].[FactMaterialDelivery].[ActualIssueDateKey],
[dbo].[FactMaterialDelivery].[Bill-toCustomerKey],
[dbo].[FactMaterialDelivery].[IssueDateKey],
[dbo].[FactMaterialDelivery].[MaterialKey],
[dbo].[FactMaterialDelivery].[PayerCustomerKey],
[dbo].[FactMaterialDelivery].[StoreKey],
[dbo].[FactMaterialDelivery].[Ship-toCustomerKey],
[dbo].[FactMaterialDelivery].[Sold-toCustomerKey],
[dbo].[FactMaterialDelivery].[VendorKey],
[dbo].[FactMaterialDelivery].[Actual Quantity Delivered Stock Units],
[dbo].[FactMaterialDelivery].[Actual Quantity Delivered Sales Units],
[dbo].[FactMaterialDelivery].[Gross weight],
[dbo].[FactMaterialDelivery].[Net weight],
[dbo].[FactMaterialDelivery].[Number of Delivery Items],
[dbo].[FactMaterialDelivery].[Volume delivered],
[dbo].[FactMaterialDelivery].[MaterialPlantAttributeKey],
[dbo].[FactMaterialDelivery].[Delivered Qty BUn]
FROM [dbo].[FactMaterialDelivery]
WHERE IssueDateKey<=20111231
so what could be the changes to be done to this query for a specific month of any year
any suggestion would be appriciated
NIHARIKA
October 18, 2012 at 10:33 am
Well in your a DateKey dimenstion table you should have columns like below.
copied this from adventureworksdw
CREATE TABLE [dbo].[DimTime](
[TimeKey] [int] IDENTITY(1,1) NOT NULL,
[FullDateAlternateKey] [datetime] NULL,
[DayNumberOfWeek] [tinyint] NULL,
[EnglishDayNameOfWeek] [nvarchar](10) NULL,
[SpanishDayNameOfWeek] [nvarchar](10) NULL,
[FrenchDayNameOfWeek] [nvarchar](10) NULL,
[DayNumberOfMonth] [tinyint] NULL,
[DayNumberOfYear] [smallint] NULL,
[WeekNumberOfYear] [tinyint] NULL,
[EnglishMonthName] [nvarchar](10) NULL,
[SpanishMonthName] [nvarchar](10) NULL,
[FrenchMonthName] [nvarchar](10) NULL,
[MonthNumberOfYear] [tinyint] NULL,
[CalendarQuarter] [tinyint] NULL,
[CalendarYear] [char](4) NULL,
[CalendarSemester] [tinyint] NULL,
[FiscalQuarter] [tinyint] NULL,
[FiscalYear] [char](4) NULL,
[FiscalSemester] [tinyint] NULL,
CONSTRAINT [PK_DimTime_TimeKey] PRIMARY KEY CLUSTERED
(
[TimeKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [AK_DimTime_FullDateAlternateKey] UNIQUE NONCLUSTERED
(
[FullDateAlternateKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
AND your query would looke like this.
SELECT [dbo].[FactMaterialDelivery].[MaterialDeliveryRowInfoKey],
[dbo].[FactMaterialDelivery].[ActualIssueDateKey],
[dbo].[FactMaterialDelivery].[Bill-toCustomerKey],
[dbo].[FactMaterialDelivery].[IssueDateKey],
[dbo].[FactMaterialDelivery].[MaterialKey],
[dbo].[FactMaterialDelivery].[PayerCustomerKey],
[dbo].[FactMaterialDelivery].[StoreKey],
[dbo].[FactMaterialDelivery].[Ship-toCustomerKey],
[dbo].[FactMaterialDelivery].[Sold-toCustomerKey],
[dbo].[FactMaterialDelivery].[VendorKey],
[dbo].[FactMaterialDelivery].[Actual Quantity Delivered Stock Units],
[dbo].[FactMaterialDelivery].[Actual Quantity Delivered Sales Units],
[dbo].[FactMaterialDelivery].[Gross weight],
[dbo].[FactMaterialDelivery].[Net weight],
[dbo].[FactMaterialDelivery].[Number of Delivery Items],
[dbo].[FactMaterialDelivery].[Volume delivered],
[dbo].[FactMaterialDelivery].[MaterialPlantAttributeKey],
[dbo].[FactMaterialDelivery].[Delivered Qty BUn]
FROM [dbo].[FactMaterialDelivery]
INNER JOIN [dbo].[DimTime] dt
ON [TimeKey] = IssueDateKey
WHERE dt.[MonthNumberOfYear] = 6
AND [CalendarYear] = 2012
October 18, 2012 at 10:47 pm
Hi Ray, Thank you so much for your reply..
October 19, 2012 at 4:55 am
Hi All, for below query what could be the considerable date for partitioning basing on month.
here we can see translation date, document date, posting date dimensions.
which one should consider?
SELECT
[dbo].[FactMaterialMovement].[MaterialMovementRowInfoKey],
[dbo].[FactMaterialMovement].[TranslationDateKey],
[dbo].[FactMaterialMovement].[LocalCurrencyKey],
[dbo].[FactMaterialMovement].[MaterialKey],
[dbo].[FactMaterialMovement].[StoreKey],
[dbo].[FactMaterialMovement].[CampaignKey],
[dbo].[FactMaterialMovement].[DocumentCurrencyKey],
[dbo].[FactMaterialMovement].[SoldToCustomerKey],
[dbo].[FactMaterialMovement].[VendorKey],
[dbo].[FactMaterialMovement].[DocumentDateKey],
[dbo].[FactMaterialMovement].[PostingDateKey],
[dbo].[FactMaterialMovement].[ReceivingPlantKey],
[dbo].[FactMaterialMovement].[ShipToCustomerKey],
[dbo].[FactMaterialMovement].[Subtotal2],
[dbo].[FactMaterialMovement].[Subtotal3],
[dbo].[FactMaterialMovement].[AmountPOUnit],
[dbo].[FactMaterialMovement].[AmountLocalCurrency],
[dbo].[FactMaterialMovement].[BWAmountinBUnitM],
[dbo].[FactMaterialMovement].[BWCostValLocalCurrency],
[dbo].[FactMaterialMovement].[BWSalesValueLocalCurrency],
[dbo].[FactMaterialMovement].[SalesValLocalCurrency],
[dbo].[FactMaterialMovement].[DeliveryCostsLocalCurrency],
[dbo].[FactMaterialMovement].[GrossWeightSU],
[dbo].[FactMaterialMovement].[GrossWeightBU],
[dbo].[FactMaterialMovement].[GrossWeightG/R],
[dbo].[FactMaterialMovement].[VolumeinSU],
[dbo].[FactMaterialMovement].[MovingPriceLocalCurrency],
[dbo].[FactMaterialMovement].[ZFACT1],
[dbo].[FactMaterialMovement].[ZS_FACTO],
[dbo].[FactMaterialMovement].[QuantityBaseUOM],
[dbo].[FactMaterialMovement].[DelivQtyinSUn],
[dbo].[FactMaterialMovement].[Qtyinunofentry],
[dbo].[FactMaterialMovement].[Volume],
[dbo].[FactMaterialMovement].[MateriallantAttributeKey]
FROM
[dbo].[FactMaterialMovement]
WHERE
thanks
Niharika
October 23, 2012 at 3:36 am
Hi,
It depends on two factors
What is the most common date used for querying - therefore the users queries will be quicker if partitioned correctly
Or do you get a date that is always incremental in your load and therefore you could process your cubes by just the latest partition - this will make your processing quicker
That's a judgement call you will have to make as it is impossible for me to understand any of this purely off the column names
Mack
October 23, 2012 at 7:28 am
Thanks for the reply
and if there are many date keys we can choose appropriate one to make partitions.
but for following query , how can we choose
SELECT dbo.DimPurchaseSDABRowInfo_view.RowInfoKey, dbo.DimPurchaseSDABRowInfo_view.Source AS SourceSDAB,
dbo.DimPurchaseSDABRowInfo_view.SourceName AS SourceNameSDAB, ISNULL(dbo.DimProfitCenterNames.ProfitCenterName, N'N/A')
AS [ProfitCenterName_FI-SDAB], REPLACE(LTRIM(REPLACE(dbo.DimPurchaseSDABRowInfo_view.ProfitCenter, '0', ' ')), ' ', '0')
+ ' - ' + ISNULL(dbo.DimProfitCenterNames.ProfitCenterName, N'N/A') AS [ProfitCenter_FI-SDAB],
dbo.DimPurchaseSDABRowInfo_view.PurchaseInvoiceSD AS PurchaseInvoiceSDAB, dbo.DimPurchaseSDABRowInfo_view.FiscalYear AS FiscalYearSDAB,
dbo.DimPurchaseSDABRowInfo_view.FiscalYearVariant AS FiscalYearVariantSDAB, dbo.DimPurchaseSDABRowInfo_view.LIVInvoiceItem AS LIVInvoiceItemSDAB,
dbo.DimPurchaseSDABRowInfo_view.AccountAssignmentCategory AS AccountAssignmentCategorySDAB,
dbo.DimPurchaseSDABRowInfo_view.BaseUnit AS BaseUnitSDAB,
dbo.DimPurchaseSDABRowInfo_view.BWDocumentItemNumber AS BWDocumentItemNumberSDAB,
dbo.DimPurchaseSDABRowInfo_view.PurchaseOrder AS PurchaseOrderSDAB, dbo.DimPurchaseSDABRowInfo_view.CompanyCode AS CompanyCodeSDAB,
dbo.DimPurchaseSDABRowInfo_view.VendorCountry AS VendorCountrySDAB, dbo.DimPurchaseSDABRowInfo_view.DebitCreditIndicator AS DebitCreditIndicatorSDAB,
dbo.DimPurchaseSDABRowInfo_view.DeliveryCompletedIndicator AS DeliveryCompletedIndicatorSDAB, dbo.DimPurchaseSDABRowInfo_view.Vendor AS VendorSDAB,
dbo.DimPurchaseSDABRowInfo_view.PurchaseInvoiceTypeSD AS PurchaseInvoiceTypeSDAB,
dbo.DimPurchaseSDABRowInfo_view.ExchangeRate AS ExchangeRateSDAB,
dbo.DimPurchaseSDABRowInfo_view.FinalInvoiceIndicator AS FinalInvoiceIndicatorSDAB,
dbo.DimPurchaseSDABRowInfo_view.PurchasingGroup AS PurchasingGroupSDAB,
dbo.DimPurchaseSDABRowInfo_view.PurchasingOrganization AS PurchasingOrganizationSDAB,
dbo.DimPurchaseSDABRowInfo_view.ItemCategory AS ItemCategorySDAB, dbo.DimPurchaseSDABRowInfo_view.Material AS MaterialSDAB,
dbo.DimPurchaseSDABRowInfo_view.MaterialGroup AS MaterialGroupSDAB, dbo.DimPurchaseSDABRowInfo_view.Notification AS NotificationSDAB,
dbo.DimPurchaseSDABRowInfo_view.OrderUnit AS OrderUnitSDAB,
dbo.DimPurchaseSDABRowInfo_view.OriginofLogisticsInvoiceVerificationDocument AS OriginofLogisticsInvoiceVerificationDocumentSDAB,
dbo.DimPurchaseSDABRowInfo_view.Plant AS PlantSDAB, dbo.DimPurchaseSDABRowInfo_view.ProfitCenter AS ProfitCenterSDAB,
dbo.DimPurchaseSDABRowInfo_view.PurchaseOrderType AS PurchaseOrderTypeSDAB,
dbo.DimPurchaseSDABRowInfo_view.ReferenceDocumentNumber AS ReferenceDocumentNumberSDAB,
dbo.DimPurchaseSDABRowInfo_view.ConditionType AS ConditionTypeSDAB, dbo.DimPurchaseSDABRowInfo_view.SalesUnit AS SalesUnitSDAB,
dbo.DimPurchaseSDABRowInfo_view.COArea AS COAreaSDAB, dbo.DimPurchaseSDABRowInfo_view.StorageLocation AS StorageLocationSDAB,
dbo.DimPurchaseSDABRowInfo_view.TaxCode AS TaxCodeSDAB, dbo.DimPurchaseSDABRowInfo_view.PaymentTermsKey AS PaymentTermsKeySDAB,
dbo.DimPurchaseSDABRowInfo_view.UnitOfMeasure AS UnitOfMeasureSDAB, dbo.DimPurchaseSDABRowInfo_view.UserName AS UserNameSDAB,
dbo.DimPurchaseSDABRowInfo_view.ZLVType AS ZLVTypeSDAB,
dbo.DimPurchaseSDABRowInfo_view.OutwardDeliveryCompletedIndicator AS OutwardDeliveryCompletedIndicatorSDAB,
dbo.DimPurchaseSDABRowInfo_view.OrderPriceUnit AS OrderPriceUnitSDAB, dbo.DimPurchaseSDABRowInfo_view.LogisticModel AS LogisticModelSDAB,
dbo.DimPurchaseSDABRowInfo_view.InsertAuditKey, dbo.DimPurchaseSDABRowInfo_view.DistributionChannel AS DistributionChannelSDAB,
dbo.DimPurchaseSDABRowInfo_view.DocumentType AS DocumentTypeSDAB, dbo.DimPurchaseSDABRowInfo_view.SalesGroup AS SalesGroupSDAB,
dbo.DimPurchaseSDABRowInfo_view.SalesOffice AS SalesOfficeSDAB, dbo.DimPurchaseSDABRowInfo_view.Incoterms,
dbo.DimPurchaseSDABRowInfo_view.Incoterms2
FROM dbo.DimPurchaseSDABRowInfo_view LEFT OUTER JOIN
dbo.DimProfitCenterNames ON dbo.DimProfitCenterNames.ProfitCenterID = dbo.DimPurchaseSDABRowInfo_view.ProfitCenter AND
dbo.DimProfitCenterNames.[Language] = 'U'
October 23, 2012 at 10:52 pm
niha.736 (10/23/2012)
Thanks for the replyand if there are many date keys we can choose appropriate one to make partitions.
but for following query , how can we choose
SELECT dbo.DimPurchaseSDABRowInfo_view.RowInfoKey, dbo.DimPurchaseSDABRowInfo_view.Source AS SourceSDAB,
dbo.DimPurchaseSDABRowInfo_view.SourceName AS SourceNameSDAB, ISNULL(dbo.DimProfitCenterNames.ProfitCenterName, N'N/A')
AS [ProfitCenterName_FI-SDAB], REPLACE(LTRIM(REPLACE(dbo.DimPurchaseSDABRowInfo_view.ProfitCenter, '0', ' ')), ' ', '0')
+ ' - ' + ISNULL(dbo.DimProfitCenterNames.ProfitCenterName, N'N/A') AS [ProfitCenter_FI-SDAB],
dbo.DimPurchaseSDABRowInfo_view.PurchaseInvoiceSD AS PurchaseInvoiceSDAB, dbo.DimPurchaseSDABRowInfo_view.FiscalYear AS FiscalYearSDAB,
dbo.DimPurchaseSDABRowInfo_view.FiscalYearVariant AS FiscalYearVariantSDAB, dbo.DimPurchaseSDABRowInfo_view.LIVInvoiceItem AS LIVInvoiceItemSDAB,
dbo.DimPurchaseSDABRowInfo_view.AccountAssignmentCategory AS AccountAssignmentCategorySDAB,
dbo.DimPurchaseSDABRowInfo_view.BaseUnit AS BaseUnitSDAB,
dbo.DimPurchaseSDABRowInfo_view.BWDocumentItemNumber AS BWDocumentItemNumberSDAB,
dbo.DimPurchaseSDABRowInfo_view.PurchaseOrder AS PurchaseOrderSDAB, dbo.DimPurchaseSDABRowInfo_view.CompanyCode AS CompanyCodeSDAB,
dbo.DimPurchaseSDABRowInfo_view.VendorCountry AS VendorCountrySDAB, dbo.DimPurchaseSDABRowInfo_view.DebitCreditIndicator AS DebitCreditIndicatorSDAB,
dbo.DimPurchaseSDABRowInfo_view.DeliveryCompletedIndicator AS DeliveryCompletedIndicatorSDAB, dbo.DimPurchaseSDABRowInfo_view.Vendor AS VendorSDAB,
dbo.DimPurchaseSDABRowInfo_view.PurchaseInvoiceTypeSD AS PurchaseInvoiceTypeSDAB,
dbo.DimPurchaseSDABRowInfo_view.ExchangeRate AS ExchangeRateSDAB,
dbo.DimPurchaseSDABRowInfo_view.FinalInvoiceIndicator AS FinalInvoiceIndicatorSDAB,
dbo.DimPurchaseSDABRowInfo_view.PurchasingGroup AS PurchasingGroupSDAB,
dbo.DimPurchaseSDABRowInfo_view.PurchasingOrganization AS PurchasingOrganizationSDAB,
dbo.DimPurchaseSDABRowInfo_view.ItemCategory AS ItemCategorySDAB, dbo.DimPurchaseSDABRowInfo_view.Material AS MaterialSDAB,
dbo.DimPurchaseSDABRowInfo_view.MaterialGroup AS MaterialGroupSDAB, dbo.DimPurchaseSDABRowInfo_view.Notification AS NotificationSDAB,
dbo.DimPurchaseSDABRowInfo_view.OrderUnit AS OrderUnitSDAB,
dbo.DimPurchaseSDABRowInfo_view.OriginofLogisticsInvoiceVerificationDocument AS OriginofLogisticsInvoiceVerificationDocumentSDAB,
dbo.DimPurchaseSDABRowInfo_view.Plant AS PlantSDAB, dbo.DimPurchaseSDABRowInfo_view.ProfitCenter AS ProfitCenterSDAB,
dbo.DimPurchaseSDABRowInfo_view.PurchaseOrderType AS PurchaseOrderTypeSDAB,
dbo.DimPurchaseSDABRowInfo_view.ReferenceDocumentNumber AS ReferenceDocumentNumberSDAB,
dbo.DimPurchaseSDABRowInfo_view.ConditionType AS ConditionTypeSDAB, dbo.DimPurchaseSDABRowInfo_view.SalesUnit AS SalesUnitSDAB,
dbo.DimPurchaseSDABRowInfo_view.COArea AS COAreaSDAB, dbo.DimPurchaseSDABRowInfo_view.StorageLocation AS StorageLocationSDAB,
dbo.DimPurchaseSDABRowInfo_view.TaxCode AS TaxCodeSDAB, dbo.DimPurchaseSDABRowInfo_view.PaymentTermsKey AS PaymentTermsKeySDAB,
dbo.DimPurchaseSDABRowInfo_view.UnitOfMeasure AS UnitOfMeasureSDAB, dbo.DimPurchaseSDABRowInfo_view.UserName AS UserNameSDAB,
dbo.DimPurchaseSDABRowInfo_view.ZLVType AS ZLVTypeSDAB,
dbo.DimPurchaseSDABRowInfo_view.OutwardDeliveryCompletedIndicator AS OutwardDeliveryCompletedIndicatorSDAB,
dbo.DimPurchaseSDABRowInfo_view.OrderPriceUnit AS OrderPriceUnitSDAB, dbo.DimPurchaseSDABRowInfo_view.LogisticModel AS LogisticModelSDAB,
dbo.DimPurchaseSDABRowInfo_view.InsertAuditKey, dbo.DimPurchaseSDABRowInfo_view.DistributionChannel AS DistributionChannelSDAB,
dbo.DimPurchaseSDABRowInfo_view.DocumentType AS DocumentTypeSDAB, dbo.DimPurchaseSDABRowInfo_view.SalesGroup AS SalesGroupSDAB,
dbo.DimPurchaseSDABRowInfo_view.SalesOffice AS SalesOfficeSDAB, dbo.DimPurchaseSDABRowInfo_view.Incoterms,
dbo.DimPurchaseSDABRowInfo_view.Incoterms2
FROM dbo.DimPurchaseSDABRowInfo_view LEFT OUTER JOIN
dbo.DimProfitCenterNames ON dbo.DimProfitCenterNames.ProfitCenterID = dbo.DimPurchaseSDABRowInfo_view.ProfitCenter AND
dbo.DimProfitCenterNames.[Language] = 'U'
This has nothing to do with the problem at hamd but this would be a whole lot easier to read if you learned how to use table aliases.
As to which date to use, only you can decide that.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 24, 2012 at 3:31 am
Hi Thanks for ur reply,
if u can explain in detail about previous query , it will help me alot.
show me the right way of thinking.
am not an expert in sql queries, but am trying to do it.
thanks,
Niharika
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply