Primary keys ...

I was setting up replication and discovered that explicitly defined primary
keys are required on the tables being replicated. We currenty have defined
these on exactly zero of our tables (legacy thing - you get the picture). I
told the developers I needed them. They asked me if it would break anything
if we put them on. I'm thinking that as long as we don't define explicit
PK/FK relationshiops bewteen the tables we should be OK.
I would appreciate any insight on the matter.
Bob Castleman
DBA PoseurOne idea would be to add a UniqueIdentifier field and set it to be the
RowGuidCol and the PK (along with a default of NewId()). By doing this, you
avoid SQL adding this column for you anyway for replication.
Thomas
"Bob Castleman" <nomail@.here> wrote in message
news:uNvZICqRFHA.2528@.TK2MSFTNGP10.phx.gbl...
>I was setting up replication and discovered that explicitly defined primary
>keys are required on the tables being replicated. We currenty have defined
>these on exactly zero of our tables (legacy thing - you get the picture). I
>told the developers I needed them. They asked me if it would break anything
if
>we put them on. I'm thinking that as long as we don't define explicit PK/FK
>relationshiops bewteen the tables we should be OK.
> I would appreciate any insight on the matter.
> Bob Castleman
> DBA Poseur
>|||We already have unique IDs on the tables, they were just never explicitly
defined within SQL Server as PKs. We may at some point start defining PK/FK
relationships and referential integrity so wouldn't make sense to use our
current IDs? I am more concerned about unintended side effects. I can't see
how defining a PK on table would cause a problem, but I need to make sure.
Bob
"Thomas" <replyingroup@.anywhere.com> wrote in message
news:eMO7hZqRFHA.244@.TK2MSFTNGP12.phx.gbl...
> One idea would be to add a UniqueIdentifier field and set it to be the
> RowGuidCol and the PK (along with a default of NewId()). By doing this,
> you avoid SQL adding this column for you anyway for replication.
>
> Thomas
>
> "Bob Castleman" <nomail@.here> wrote in message
> news:uNvZICqRFHA.2528@.TK2MSFTNGP10.phx.gbl...
>|||As long as the data is actually unique, it shouldn't be a problem.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Bob Castleman" <nomail@.here> wrote in message
news:%23PkbQfqRFHA.648@.TK2MSFTNGP14.phx.gbl...
> We already have unique IDs on the tables, they were just never explicitly
> defined within SQL Server as PKs. We may at some point start defining
> PK/FK relationships and referential integrity so wouldn't make sense to
> use our current IDs? I am more concerned about unintended side effects. I
> can't see how defining a PK on table would cause a problem, but I need to
> make sure.
> Bob
> "Thomas" <replyingroup@.anywhere.com> wrote in message
> news:eMO7hZqRFHA.244@.TK2MSFTNGP12.phx.gbl...
>|||Why should I consider using an auxiliary numbers table?
http://www.aspfaq.com/show.asp?id=2516
use northwind
go
select
identity(int, 1, 1) as number
into
number
from
sysobjects as a cross join sysobjects as b
go
declare @.sql nvarchar(4000)
declare @.s datetime
declare @.e datetime
declare @.i int
declare @.datepart varchar(15)
set @.s = '2005-01-01T12:00:00.000'
set @.e = '2005-01-01T13:00:00.000'
set @.i = 15
set @.datepart = 'minute'
set @.sql = N'
select
right(convert(varchar(35), dateadd(' + @.datepart + N', number, ''' +
convert(varchar(25), @.s, 126) + N'''), 100), 7) as colA
from
number as n
where
number % ' + ltrim(@.i) + N' = 0
and dateadd(' + @.datepart + N', number, ''' + convert(varchar(25), @.s, 126)
+ N''') < cast(''' + convert(varchar(25), @.e, 126) + N''' as datetime)'
print @.sql
exec sp_executesql @.sql
go
drop table number
go
The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
AMB
"Bob Castleman" wrote:

> I was setting up replication and discovered that explicitly defined primar
y
> keys are required on the tables being replicated. We currenty have defined
> these on exactly zero of our tables (legacy thing - you get the picture).
I
> told the developers I needed them. They asked me if it would break anythin
g
> if we put them on. I'm thinking that as long as we don't define explicit
> PK/FK relationshiops bewteen the tables we should be OK.
> I would appreciate any insight on the matter.
> Bob Castleman
> DBA Poseur
>
>|||Sorry, wrong place.
AMB
"Alejandro Mesa" wrote:
> Why should I consider using an auxiliary numbers table?
> http://www.aspfaq.com/show.asp?id=2516
> use northwind
> go
> select
> identity(int, 1, 1) as number
> into
> number
> from
> sysobjects as a cross join sysobjects as b
> go
>
> declare @.sql nvarchar(4000)
> declare @.s datetime
> declare @.e datetime
> declare @.i int
> declare @.datepart varchar(15)
> set @.s = '2005-01-01T12:00:00.000'
> set @.e = '2005-01-01T13:00:00.000'
> set @.i = 15
> set @.datepart = 'minute'
> set @.sql = N'
> select
> right(convert(varchar(35), dateadd(' + @.datepart + N', number, ''' +
> convert(varchar(25), @.s, 126) + N'''), 100), 7) as colA
> from
> number as n
> where
> number % ' + ltrim(@.i) + N' = 0
> and dateadd(' + @.datepart + N', number, ''' + convert(varchar(25), @.s, 12
6)
> + N''') < cast(''' + convert(varchar(25), @.e, 126) + N''' as datetime)'
> print @.sql
> exec sp_executesql @.sql
> go
> drop table number
> go
>
> The Curse and Blessings of Dynamic SQL
> http://www.sommarskog.se/dynamic_sql.html
>
> AMB
>
> "Bob Castleman" wrote:
>|||If that's the case, then just declare those columns as the PK. You can decla
re a
PK without having to declare FK (although it helps with data integrity).
The only problems you might encounter by setting a given column(s) as the PK
are:
1. If the data is not unique or contains nulls
or
2. If the the app that writes the data expects that it can fill in duplicate
or
null data on one pass even if it changes it to be non-nullable and unique in
another pass. Obviously, this won't work as SQL will prevent any nulls or
duplciate values from ever being written.
Thomas
"Bob Castleman" <nomail@.here> wrote in message
news:%23PkbQfqRFHA.648@.TK2MSFTNGP14.phx.gbl...
> We already have unique IDs on the tables, they were just never explicitly
> defined within SQL Server as PKs. We may at some point start defining PK/F
K
> relationships and referential integrity so wouldn't make sense to use our
> current IDs? I am more concerned about unintended side effects. I can't se
e
> how defining a PK on table would cause a problem, but I need to make sure.
> Bob
> "Thomas" <replyingroup@.anywhere.com> wrote in message
> news:eMO7hZqRFHA.244@.TK2MSFTNGP12.phx.gbl...
>

Previous Month To Date Calculation

I am currenty attempting to add a calculation to get previous month to date calculation. I currently have the following calculation in to calculate previous Year to Date and am having trouble adapting it. Any help is appreciated. Please note I am fairly new at this.

Previous Year To Date:

([Time Calculations].[YTD Pr Yr]=
Aggregate(
Crossjoin({[Calendar Year].[Current Period]},
PeriodsToDate(
[Time].[Calendar Year].[Year],
ParallelPeriod(
[Time].[Calendar Year].[Year],1,
[Time].[Calendar Year].CurrentMember)))
)
);

Adam

Dear Friend,

Check this example:

MTD:

Code Snippet

SUM(PeriodsToDate([DimTime].[Hierarquia].[Month],
[DimTime].[Hierarquia].CurrentMember),
[Measures].[NC_ValorCarteira])

Gets Previous Member

Code Snippet

IIF(IsEMPTY(([Measures].[CM_ResFinAcum],[DimTime].[Hierarquia].PrevMember))
,0,([Measures].[CM_ResFinAcum],[DimTime].[Hierarquia].PrevMember))

Helped?

Regards!

|||That is not quite what I am looking for. I can calculate current month to date just fine, but what I am attempting to calcuate is month to date for the previous year. So from Aug 1, 2006 - Aug 21 2006. Given the nature of our business, know growth from this time last year is essential. This is what I am attempting to use and it returns the entire aggregate of the previous years period. Any help would be greatly appreciated.

Aggregate
(
PeriodsToDate(
[Time].[Year - Month - Week - Day of Week].[Month],
ParallelPeriod(
[Time].[Year - Month - Week - Day of Week].[Month],12,
[Time].[Year - Month - Week - Day of Week].CurrentMember)
),
[Measures].[Orders]
)

Adam|||

Hi Adam,

I'm not sure about the structure of your [Time] dimension, so here's a sample Adventure Works query:

Code Snippet

With

Member [Measures].[MTDSales] as

Aggregate(PeriodsToDate([Date].[Calendar].[Month]),

[Measures].[Sales Amount]),

FORMAT_STRING = 'Currency'

Member [Measures].[MTDSales-PY] as

([Measures].[MTDSales],

ParallelPeriod([Date].[Calendar].[Calendar Year])),

FORMAT_STRING = 'Currency'

select

{[Measures].[Sales Amount], [Measures].[MTDSales],

[Measures].[MTDSales-PY]} on 0,

Non Empty

{[Date].[Calendar].[Month].&[2003]&[7].Children,

[Date].[Calendar].[Month].&[2004]&[7].Children} on 1

from [Adventure Works]

|||Using that structure, I am still getting all of last years numbers. The ideal goal is to compare the current months MTD with the corresponding MTD of last year to evaluate growth. It is misleading to compare the current MTD with the entirety of the correspond prior years MTD. What I need is someone to exlude days from the prior year MTD calculation.
|||

AdamAtAirNWater wrote:

It is misleading to compare the current MTD with the entirety of the correspond prior years MTD. What I need is someone to exlude days from the prior year MTD calculation.

But that's how I thought the sample Adventure Works query worked. For example, compare these 2 result rows:

...

July 15, 2003 $30,792.07 $3,103,364.27 $2,642,983.51

...

July 15, 2004 $1,379.50 $23,234.19 $3,103,364.27

The [MTDSales-PY] for July 15, 2004 is $3,103,364.27, which is identical to [MTDSales] for July 15, 2003 (the total for all days of July, 2003 is $3,552,319.38). To better understand your issue, could you point out specific examples in the sample query results?

|||Perhaps I made an error in the way I adapted it into my calculation. I am attempting to put this into a calculation in a cube.

How would I translate that into an expression for a cube?

Adam
|||This is what I am currently using in my cube to calculate MTD and the Prior MTD. As I said, it is returning the entire value for the previous MTD

Code Snippet

CREATE MEMBER CURRENTCUBE.[MEASURES].[MTD]
AS Aggregate(PeriodsToDate([Time].[Year - Month - Dayof Month].[Month]),
[Measures].[Orders]),
FORMAT_STRING = "Standard",
VISIBLE = 1;
CREATE MEMBER CURRENTCUBE.[MEASURES].[MTD-Prior]
AS ([Measures].[MTD],
ParallelPeriod([Time].[Year - Month - Dayof Month].[Year])),
FORMAT_STRING = "#",
VISIBLE = 1;

|||

Well, the expressions look similar to the Adventure Works sample, so I'm wondering whether there's an issue with the [Time].[Year - Month - Dayof Month] hierarchy not being natural. In any case, it's worth trying the full forms of PeriodsToDate() and ParallelPeriod(), like:

Code Snippet

CREATE MEMBER CURRENTCUBE.[MEASURES].[MTD]
AS Aggregate(PeriodsToDate([Time].[Year - Month - Dayof Month].[Month],

[Time].[Year - Month - Dayof Month].CurrentMember),
[Measures].[Orders]),
FORMAT_STRING = "Standard",
VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[MEASURES].[MTD-Prior]
AS ([Measures].[MTD],
ParallelPeriod([Time].[Year - Month - Dayof Month].[Year], 1,

[Time].[Year - Month - Dayof Month].CurrentMember)),
FORMAT_STRING = "#",
VISIBLE = 1;

|||I am not entirely sure what you mean by the hierarchy not being natural. However, I am assuming this is the issue as it is still not working as intended. I do have a yellow triangle on the hierarchy with the message that states "Attribute relationships do not exist between one or more levels in this hierarchy. The following hierarchies do not have a direct or indirect relationship defined to their parent." I am not entirely sure what to do to fix that however.

Adam
|||

AdamAtAirNWater wrote:

I do have a yellow triangle on the hierarchy with the message that states "Attribute relationships do not exist between one or more levels in this hierarchy. The following hierarchies do not have a direct or indirect relationship defined to their parent."

This indicates that the [Year - Month - Dayof Month] hierarchy is not natural:

SQL Server 2005 Books Online

Attribute Relationships

...

Natural Hierarchy Relationships

A hierarchy is a natural hierarchy when each attribute included in the user-defined hierarchy has a one to many relationship with the attribute immediately below it.

...

Relationships representing natural hierarchies are enforced by creating an attribute relationship between the attribute for a level and the attribute for the level below it.

...

My guess is that the "Month" and/or 'DayOfMonth" attributes in the hierarchy are not unique across higher levels of the hierarchy. For example, if 'DayOfMonth" was like 1, 2, etc, the same member could appear under multiple months. So it should be qualified (could be by month and year) to make it unique. If you study how the [Date] dimension and Fiscal hierarchy in Adventure Works are designed, it will become clearer.

|||I have fixed the hierarchy issues and have even have the calculation almost exactly where I want it. If I use:

Code Snippet

([Measures].[MTD],
ParallelPeriod([Time].[Year - Month - Date].[Simple Date], 365,
[Time].[Year - Month - Date].CurrentMember))

then its gets me the correct information. The only question I now have is there anyway that I can see this number at the month level of the hierarchy. When view my data in the cube, I have to drill down to the day and it give me the correct month to date on that sepecific day, but ideally I don't want to have to go that deep in order to see it. Is that possible?

Thanks again for all the help. It is greatly apprieciated and I have learned a lot.

Adam
|||But which date should be selected for browsing MTD at the month level - is it the last day of the month with data? I'm guessing that most months earlier than the current one have data for all days, so in those cases it will be the total for the month.|||I would want to show the last date with data of the current month for the previous years month.

Adam
|||

One way to do that would be to add a dedicated measure - which would work at any level. So if [MTD-PY] is defined as you indicated above:

Code Snippet

([Measures].[MTD],
ParallelPeriod([Time].[Year - Month - Date].[Simple Date], 365,
[Time].[Year - Month - Date].CurrentMember))

then [LatestMTD-PY] could be like:

Code Snippet

([Measures].[MTD-PY],

Tail(NonEmpty([Time].[Year - Month - Date].[Simple Date],

{[Measures].[Orders]})).Item(0))

Previous Month To Date Calculation

I am currenty attempting to add a calculation to get previous month to date calculation. I currently have the following calculation in to calculate previous Year to Date and am having trouble adapting it. Any help is appreciated. Please note I am fairly new at this.

Previous Year To Date:

([Time Calculations].[YTD Pr Yr]=
Aggregate(
Crossjoin({[Calendar Year].[Current Period]},
PeriodsToDate(
[Time].[Calendar Year].[Year],
ParallelPeriod(
[Time].[Calendar Year].[Year],1,
[Time].[Calendar Year].CurrentMember)))
)
);

Adam

Dear Friend,

Check this example:

MTD:

Code Snippet

SUM(PeriodsToDate([DimTime].[Hierarquia].[Month],
[DimTime].[Hierarquia].CurrentMember),
[Measures].[NC_ValorCarteira])

Gets Previous Member

Code Snippet

IIF(IsEMPTY(([Measures].[CM_ResFinAcum],[DimTime].[Hierarquia].PrevMember))
,0,([Measures].[CM_ResFinAcum],[DimTime].[Hierarquia].PrevMember))

Helped?

Regards!

|||That is not quite what I am looking for. I can calculate current month to date just fine, but what I am attempting to calcuate is month to date for the previous year. So from Aug 1, 2006 - Aug 21 2006. Given the nature of our business, know growth from this time last year is essential. This is what I am attempting to use and it returns the entire aggregate of the previous years period. Any help would be greatly appreciated.

Aggregate
(
PeriodsToDate(
[Time].[Year - Month - Week - Day of Week].[Month],
ParallelPeriod(
[Time].[Year - Month - Week - Day of Week].[Month],12,
[Time].[Year - Month - Week - Day of Week].CurrentMember)
),
[Measures].[Orders]
)

Adam|||

Hi Adam,

I'm not sure about the structure of your [Time] dimension, so here's a sample Adventure Works query:

Code Snippet

With

Member [Measures].[MTDSales] as

Aggregate(PeriodsToDate([Date].[Calendar].[Month]),

[Measures].[Sales Amount]),

FORMAT_STRING = 'Currency'

Member [Measures].[MTDSales-PY] as

([Measures].[MTDSales],

ParallelPeriod([Date].[Calendar].[Calendar Year])),

FORMAT_STRING = 'Currency'

select

{[Measures].[Sales Amount], [Measures].[MTDSales],

[Measures].[MTDSales-PY]} on 0,

Non Empty

{[Date].[Calendar].[Month].&[2003]&[7].Children,

[Date].[Calendar].[Month].&[2004]&[7].Children} on 1

from [Adventure Works]

|||Using that structure, I am still getting all of last years numbers. The ideal goal is to compare the current months MTD with the corresponding MTD of last year to evaluate growth. It is misleading to compare the current MTD with the entirety of the correspond prior years MTD. What I need is someone to exlude days from the prior year MTD calculation.
|||

AdamAtAirNWater wrote:

It is misleading to compare the current MTD with the entirety of the correspond prior years MTD. What I need is someone to exlude days from the prior year MTD calculation.

But that's how I thought the sample Adventure Works query worked. For example, compare these 2 result rows:

...

July 15, 2003 $30,792.07 $3,103,364.27 $2,642,983.51

...

July 15, 2004 $1,379.50 $23,234.19 $3,103,364.27

The [MTDSales-PY] for July 15, 2004 is $3,103,364.27, which is identical to [MTDSales] for July 15, 2003 (the total for all days of July, 2003 is $3,552,319.38). To better understand your issue, could you point out specific examples in the sample query results?

|||Perhaps I made an error in the way I adapted it into my calculation. I am attempting to put this into a calculation in a cube.

How would I translate that into an expression for a cube?

Adam
|||This is what I am currently using in my cube to calculate MTD and the Prior MTD. As I said, it is returning the entire value for the previous MTD

Code Snippet

CREATE MEMBER CURRENTCUBE.[MEASURES].[MTD]
AS Aggregate(PeriodsToDate([Time].[Year - Month - Dayof Month].[Month]),
[Measures].[Orders]),
FORMAT_STRING = "Standard",
VISIBLE = 1;
CREATE MEMBER CURRENTCUBE.[MEASURES].[MTD-Prior]
AS ([Measures].[MTD],
ParallelPeriod([Time].[Year - Month - Dayof Month].[Year])),
FORMAT_STRING = "#",
VISIBLE = 1;

|||

Well, the expressions look similar to the Adventure Works sample, so I'm wondering whether there's an issue with the [Time].[Year - Month - Dayof Month] hierarchy not being natural. In any case, it's worth trying the full forms of PeriodsToDate() and ParallelPeriod(), like:

Code Snippet

CREATE MEMBER CURRENTCUBE.[MEASURES].[MTD]
AS Aggregate(PeriodsToDate([Time].[Year - Month - Dayof Month].[Month],

[Time].[Year - Month - Dayof Month].CurrentMember),
[Measures].[Orders]),
FORMAT_STRING = "Standard",
VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[MEASURES].[MTD-Prior]
AS ([Measures].[MTD],
ParallelPeriod([Time].[Year - Month - Dayof Month].[Year], 1,

[Time].[Year - Month - Dayof Month].CurrentMember)),
FORMAT_STRING = "#",
VISIBLE = 1;

|||I am not entirely sure what you mean by the hierarchy not being natural. However, I am assuming this is the issue as it is still not working as intended. I do have a yellow triangle on the hierarchy with the message that states "Attribute relationships do not exist between one or more levels in this hierarchy. The following hierarchies do not have a direct or indirect relationship defined to their parent." I am not entirely sure what to do to fix that however.

Adam
|||

AdamAtAirNWater wrote:

I do have a yellow triangle on the hierarchy with the message that states "Attribute relationships do not exist between one or more levels in this hierarchy. The following hierarchies do not have a direct or indirect relationship defined to their parent."

This indicates that the [Year - Month - Dayof Month] hierarchy is not natural:

SQL Server 2005 Books Online

Attribute Relationships

...

Natural Hierarchy Relationships

A hierarchy is a natural hierarchy when each attribute included in the user-defined hierarchy has a one to many relationship with the attribute immediately below it.

...

Relationships representing natural hierarchies are enforced by creating an attribute relationship between the attribute for a level and the attribute for the level below it.

...

My guess is that the "Month" and/or 'DayOfMonth" attributes in the hierarchy are not unique across higher levels of the hierarchy. For example, if 'DayOfMonth" was like 1, 2, etc, the same member could appear under multiple months. So it should be qualified (could be by month and year) to make it unique. If you study how the [Date] dimension and Fiscal hierarchy in Adventure Works are designed, it will become clearer.

|||I have fixed the hierarchy issues and have even have the calculation almost exactly where I want it. If I use:

Code Snippet

([Measures].[MTD],
ParallelPeriod([Time].[Year - Month - Date].[Simple Date], 365,
[Time].[Year - Month - Date].CurrentMember))

then its gets me the correct information. The only question I now have is there anyway that I can see this number at the month level of the hierarchy. When view my data in the cube, I have to drill down to the day and it give me the correct month to date on that sepecific day, but ideally I don't want to have to go that deep in order to see it. Is that possible?

Thanks again for all the help. It is greatly apprieciated and I have learned a lot.

Adam
|||But which date should be selected for browsing MTD at the month level - is it the last day of the month with data? I'm guessing that most months earlier than the current one have data for all days, so in those cases it will be the total for the month.|||I would want to show the last date with data of the current month for the previous years month.

Adam
|||

One way to do that would be to add a dedicated measure - which would work at any level. So if [MTD-PY] is defined as you indicated above:

Code Snippet

([Measures].[MTD],
ParallelPeriod([Time].[Year - Month - Date].[Simple Date], 365,
[Time].[Year - Month - Date].CurrentMember))

then [LatestMTD-PY] could be like:

Code Snippet

([Measures].[MTD-PY],

Tail(NonEmpty([Time].[Year - Month - Date].[Simple Date],

{[Measures].[Orders]})).Item(0))

Previous Month To Date Calculation

I am currenty attempting to add a calculation to get previous month to date calculation. I currently have the following calculation in to calculate previous Year to Date and am having trouble adapting it. Any help is appreciated. Please note I am fairly new at this.

Previous Year To Date:

([Time Calculations].[YTD Pr Yr]=
Aggregate(
Crossjoin({[Calendar Year].[Current Period]},
PeriodsToDate(
[Time].[Calendar Year].[Year],
ParallelPeriod(
[Time].[Calendar Year].[Year],1,
[Time].[Calendar Year].CurrentMember)))
)
);

Adam

Dear Friend,

Check this example:

MTD:

Code Snippet

SUM(PeriodsToDate([DimTime].[Hierarquia].[Month],
[DimTime].[Hierarquia].CurrentMember),
[Measures].[NC_ValorCarteira])

Gets Previous Member

Code Snippet

IIF(IsEMPTY(([Measures].[CM_ResFinAcum],[DimTime].[Hierarquia].PrevMember))
,0,([Measures].[CM_ResFinAcum],[DimTime].[Hierarquia].PrevMember))

Helped?

Regards!

|||That is not quite what I am looking for. I can calculate current month to date just fine, but what I am attempting to calcuate is month to date for the previous year. So from Aug 1, 2006 - Aug 21 2006. Given the nature of our business, know growth from this time last year is essential. This is what I am attempting to use and it returns the entire aggregate of the previous years period. Any help would be greatly appreciated.

Aggregate
(
PeriodsToDate(
[Time].[Year - Month - Week - Day of Week].[Month],
ParallelPeriod(
[Time].[Year - Month - Week - Day of Week].[Month],12,
[Time].[Year - Month - Week - Day of Week].CurrentMember)
),
[Measures].[Orders]
)

Adam|||

Hi Adam,

I'm not sure about the structure of your [Time] dimension, so here's a sample Adventure Works query:

Code Snippet

With

Member [Measures].[MTDSales] as

Aggregate(PeriodsToDate([Date].[Calendar].[Month]),

[Measures].[Sales Amount]),

FORMAT_STRING = 'Currency'

Member [Measures].[MTDSales-PY] as

([Measures].[MTDSales],

ParallelPeriod([Date].[Calendar].[Calendar Year])),

FORMAT_STRING = 'Currency'

select

{[Measures].[Sales Amount], [Measures].[MTDSales],

[Measures].[MTDSales-PY]} on 0,

Non Empty

{[Date].[Calendar].[Month].&[2003]&[7].Children,

[Date].[Calendar].[Month].&[2004]&[7].Children} on 1

from [Adventure Works]

|||Using that structure, I am still getting all of last years numbers. The ideal goal is to compare the current months MTD with the corresponding MTD of last year to evaluate growth. It is misleading to compare the current MTD with the entirety of the correspond prior years MTD. What I need is someone to exlude days from the prior year MTD calculation.
|||

AdamAtAirNWater wrote:

It is misleading to compare the current MTD with the entirety of the correspond prior years MTD. What I need is someone to exlude days from the prior year MTD calculation.

But that's how I thought the sample Adventure Works query worked. For example, compare these 2 result rows:

...

July 15, 2003 $30,792.07 $3,103,364.27 $2,642,983.51

...

July 15, 2004 $1,379.50 $23,234.19 $3,103,364.27

The [MTDSales-PY] for July 15, 2004 is $3,103,364.27, which is identical to [MTDSales] for July 15, 2003 (the total for all days of July, 2003 is $3,552,319.38). To better understand your issue, could you point out specific examples in the sample query results?

|||Perhaps I made an error in the way I adapted it into my calculation. I am attempting to put this into a calculation in a cube.

How would I translate that into an expression for a cube?

Adam
|||This is what I am currently using in my cube to calculate MTD and the Prior MTD. As I said, it is returning the entire value for the previous MTD

Code Snippet

CREATE MEMBER CURRENTCUBE.[MEASURES].[MTD]
AS Aggregate(PeriodsToDate([Time].[Year - Month - Dayof Month].[Month]),
[Measures].[Orders]),
FORMAT_STRING = "Standard",
VISIBLE = 1;
CREATE MEMBER CURRENTCUBE.[MEASURES].[MTD-Prior]
AS ([Measures].[MTD],
ParallelPeriod([Time].[Year - Month - Dayof Month].[Year])),
FORMAT_STRING = "#",
VISIBLE = 1;

|||

Well, the expressions look similar to the Adventure Works sample, so I'm wondering whether there's an issue with the [Time].[Year - Month - Dayof Month] hierarchy not being natural. In any case, it's worth trying the full forms of PeriodsToDate() and ParallelPeriod(), like:

Code Snippet

CREATE MEMBER CURRENTCUBE.[MEASURES].[MTD]
AS Aggregate(PeriodsToDate([Time].[Year - Month - Dayof Month].[Month],

[Time].[Year - Month - Dayof Month].CurrentMember),
[Measures].[Orders]),
FORMAT_STRING = "Standard",
VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[MEASURES].[MTD-Prior]
AS ([Measures].[MTD],
ParallelPeriod([Time].[Year - Month - Dayof Month].[Year], 1,

[Time].[Year - Month - Dayof Month].CurrentMember)),
FORMAT_STRING = "#",
VISIBLE = 1;

|||I am not entirely sure what you mean by the hierarchy not being natural. However, I am assuming this is the issue as it is still not working as intended. I do have a yellow triangle on the hierarchy with the message that states "Attribute relationships do not exist between one or more levels in this hierarchy. The following hierarchies do not have a direct or indirect relationship defined to their parent." I am not entirely sure what to do to fix that however.

Adam
|||

AdamAtAirNWater wrote:

I do have a yellow triangle on the hierarchy with the message that states "Attribute relationships do not exist between one or more levels in this hierarchy. The following hierarchies do not have a direct or indirect relationship defined to their parent."

This indicates that the [Year - Month - Dayof Month] hierarchy is not natural:

SQL Server 2005 Books Online

Attribute Relationships

...

Natural Hierarchy Relationships

A hierarchy is a natural hierarchy when each attribute included in the user-defined hierarchy has a one to many relationship with the attribute immediately below it.

...

Relationships representing natural hierarchies are enforced by creating an attribute relationship between the attribute for a level and the attribute for the level below it.

...

My guess is that the "Month" and/or 'DayOfMonth" attributes in the hierarchy are not unique across higher levels of the hierarchy. For example, if 'DayOfMonth" was like 1, 2, etc, the same member could appear under multiple months. So it should be qualified (could be by month and year) to make it unique. If you study how the [Date] dimension and Fiscal hierarchy in Adventure Works are designed, it will become clearer.

|||I have fixed the hierarchy issues and have even have the calculation almost exactly where I want it. If I use:

Code Snippet

([Measures].[MTD],
ParallelPeriod([Time].[Year - Month - Date].[Simple Date], 365,
[Time].[Year - Month - Date].CurrentMember))

then its gets me the correct information. The only question I now have is there anyway that I can see this number at the month level of the hierarchy. When view my data in the cube, I have to drill down to the day and it give me the correct month to date on that sepecific day, but ideally I don't want to have to go that deep in order to see it. Is that possible?

Thanks again for all the help. It is greatly apprieciated and I have learned a lot.

Adam
|||But which date should be selected for browsing MTD at the month level - is it the last day of the month with data? I'm guessing that most months earlier than the current one have data for all days, so in those cases it will be the total for the month.|||I would want to show the last date with data of the current month for the previous years month.

Adam
|||

One way to do that would be to add a dedicated measure - which would work at any level. So if [MTD-PY] is defined as you indicated above:

Code Snippet

([Measures].[MTD],
ParallelPeriod([Time].[Year - Month - Date].[Simple Date], 365,
[Time].[Year - Month - Date].CurrentMember))

then [LatestMTD-PY] could be like:

Code Snippet

([Measures].[MTD-PY],

Tail(NonEmpty([Time].[Year - Month - Date].[Simple Date],

{[Measures].[Orders]})).Item(0))

Previous Month To Date Calculation

I am currenty attempting to add a calculation to get previous month to date calculation. I currently have the following calculation in to calculate previous Year to Date and am having trouble adapting it. Any help is appreciated. Please note I am fairly new at this.

Previous Year To Date:

([Time Calculations].[YTD Pr Yr]=
Aggregate(
Crossjoin({[Calendar Year].[Current Period]},
PeriodsToDate(
[Time].[Calendar Year].[Year],
ParallelPeriod(
[Time].[Calendar Year].[Year],1,
[Time].[Calendar Year].CurrentMember)))
)
);

Adam

Dear Friend,

Check this example:

MTD:

Code Snippet

SUM(PeriodsToDate([DimTime].[Hierarquia].[Month],
[DimTime].[Hierarquia].CurrentMember),
[Measures].[NC_ValorCarteira])

Gets Previous Member

Code Snippet

IIF(IsEMPTY(([Measures].[CM_ResFinAcum],[DimTime].[Hierarquia].PrevMember))
,0,([Measures].[CM_ResFinAcum],[DimTime].[Hierarquia].PrevMember))

Helped?

Regards!

|||That is not quite what I am looking for. I can calculate current month to date just fine, but what I am attempting to calcuate is month to date for the previous year. So from Aug 1, 2006 - Aug 21 2006. Given the nature of our business, know growth from this time last year is essential. This is what I am attempting to use and it returns the entire aggregate of the previous years period. Any help would be greatly appreciated.

Aggregate
(
PeriodsToDate(
[Time].[Year - Month - Week - Day of Week].[Month],
ParallelPeriod(
[Time].[Year - Month - Week - Day of Week].[Month],12,
[Time].[Year - Month - Week - Day of Week].CurrentMember)
),
[Measures].[Orders]
)

Adam|||

Hi Adam,

I'm not sure about the structure of your [Time] dimension, so here's a sample Adventure Works query:

Code Snippet

With

Member [Measures].[MTDSales] as

Aggregate(PeriodsToDate([Date].[Calendar].[Month]),

[Measures].[Sales Amount]),

FORMAT_STRING = 'Currency'

Member [Measures].[MTDSales-PY] as

([Measures].[MTDSales],

ParallelPeriod([Date].[Calendar].[Calendar Year])),

FORMAT_STRING = 'Currency'

select

{[Measures].[Sales Amount], [Measures].[MTDSales],

[Measures].[MTDSales-PY]} on 0,

Non Empty

{[Date].[Calendar].[Month].&[2003]&[7].Children,

[Date].[Calendar].[Month].&[2004]&[7].Children} on 1

from [Adventure Works]

|||Using that structure, I am still getting all of last years numbers. The ideal goal is to compare the current months MTD with the corresponding MTD of last year to evaluate growth. It is misleading to compare the current MTD with the entirety of the correspond prior years MTD. What I need is someone to exlude days from the prior year MTD calculation.
|||

AdamAtAirNWater wrote:

It is misleading to compare the current MTD with the entirety of the correspond prior years MTD. What I need is someone to exlude days from the prior year MTD calculation.

But that's how I thought the sample Adventure Works query worked. For example, compare these 2 result rows:

...

July 15, 2003 $30,792.07 $3,103,364.27 $2,642,983.51

...

July 15, 2004 $1,379.50 $23,234.19 $3,103,364.27

The [MTDSales-PY] for July 15, 2004 is $3,103,364.27, which is identical to [MTDSales] for July 15, 2003 (the total for all days of July, 2003 is $3,552,319.38). To better understand your issue, could you point out specific examples in the sample query results?

|||Perhaps I made an error in the way I adapted it into my calculation. I am attempting to put this into a calculation in a cube.

How would I translate that into an expression for a cube?

Adam
|||This is what I am currently using in my cube to calculate MTD and the Prior MTD. As I said, it is returning the entire value for the previous MTD

Code Snippet

CREATE MEMBER CURRENTCUBE.[MEASURES].[MTD]
AS Aggregate(PeriodsToDate([Time].[Year - Month - Dayof Month].[Month]),
[Measures].[Orders]),
FORMAT_STRING = "Standard",
VISIBLE = 1;
CREATE MEMBER CURRENTCUBE.[MEASURES].[MTD-Prior]
AS ([Measures].[MTD],
ParallelPeriod([Time].[Year - Month - Dayof Month].[Year])),
FORMAT_STRING = "#",
VISIBLE = 1;

|||

Well, the expressions look similar to the Adventure Works sample, so I'm wondering whether there's an issue with the [Time].[Year - Month - Dayof Month] hierarchy not being natural. In any case, it's worth trying the full forms of PeriodsToDate() and ParallelPeriod(), like:

Code Snippet

CREATE MEMBER CURRENTCUBE.[MEASURES].[MTD]
AS Aggregate(PeriodsToDate([Time].[Year - Month - Dayof Month].[Month],

[Time].[Year - Month - Dayof Month].CurrentMember),
[Measures].[Orders]),
FORMAT_STRING = "Standard",
VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[MEASURES].[MTD-Prior]
AS ([Measures].[MTD],
ParallelPeriod([Time].[Year - Month - Dayof Month].[Year], 1,

[Time].[Year - Month - Dayof Month].CurrentMember)),
FORMAT_STRING = "#",
VISIBLE = 1;

|||I am not entirely sure what you mean by the hierarchy not being natural. However, I am assuming this is the issue as it is still not working as intended. I do have a yellow triangle on the hierarchy with the message that states "Attribute relationships do not exist between one or more levels in this hierarchy. The following hierarchies do not have a direct or indirect relationship defined to their parent." I am not entirely sure what to do to fix that however.

Adam
|||

AdamAtAirNWater wrote:

I do have a yellow triangle on the hierarchy with the message that states "Attribute relationships do not exist between one or more levels in this hierarchy. The following hierarchies do not have a direct or indirect relationship defined to their parent."

This indicates that the [Year - Month - Dayof Month] hierarchy is not natural:

SQL Server 2005 Books Online

Attribute Relationships

...

Natural Hierarchy Relationships

A hierarchy is a natural hierarchy when each attribute included in the user-defined hierarchy has a one to many relationship with the attribute immediately below it.

...

Relationships representing natural hierarchies are enforced by creating an attribute relationship between the attribute for a level and the attribute for the level below it.

...

My guess is that the "Month" and/or 'DayOfMonth" attributes in the hierarchy are not unique across higher levels of the hierarchy. For example, if 'DayOfMonth" was like 1, 2, etc, the same member could appear under multiple months. So it should be qualified (could be by month and year) to make it unique. If you study how the [Date] dimension and Fiscal hierarchy in Adventure Works are designed, it will become clearer.

|||I have fixed the hierarchy issues and have even have the calculation almost exactly where I want it. If I use:

Code Snippet

([Measures].[MTD],
ParallelPeriod([Time].[Year - Month - Date].[Simple Date], 365,
[Time].[Year - Month - Date].CurrentMember))

then its gets me the correct information. The only question I now have is there anyway that I can see this number at the month level of the hierarchy. When view my data in the cube, I have to drill down to the day and it give me the correct month to date on that sepecific day, but ideally I don't want to have to go that deep in order to see it. Is that possible?

Thanks again for all the help. It is greatly apprieciated and I have learned a lot.

Adam
|||But which date should be selected for browsing MTD at the month level - is it the last day of the month with data? I'm guessing that most months earlier than the current one have data for all days, so in those cases it will be the total for the month.|||I would want to show the last date with data of the current month for the previous years month.

Adam
|||

One way to do that would be to add a dedicated measure - which would work at any level. So if [MTD-PY] is defined as you indicated above:

Code Snippet

([Measures].[MTD],
ParallelPeriod([Time].[Year - Month - Date].[Simple Date], 365,
[Time].[Year - Month - Date].CurrentMember))

then [LatestMTD-PY] could be like:

Code Snippet

([Measures].[MTD-PY],

Tail(NonEmpty([Time].[Year - Month - Date].[Simple Date],

{[Measures].[Orders]})).Item(0))