Comparing durations

Dear
Can I compare two duration groups? e.g.,
table1
--
id, start, end
--
1, 2005-01-01, 2005-01-30
2, 2005-02-01, 2005-02-28
table2
--
id_ref, start, end
--
1, 2005-01-01, 2005-01-15
1, 2005-01-17, 2005-01-30
The table1 is a parent of the table2. The duration /2005-01-01 ~ 2005-01-30/
has two child durations in the table2. But the table1 has one missing day or
duration in the table2.
2005-01-06
I want to know if there is a missing day or duration in a given duration in
the table1.
Theoritically,
1. enumerate all the days belonged to the duration of table1
2. check each days against the durations of the table2
Then I may know if there is a missing day or not. But looks inefficient.
Do you have some better idea?
Pohwan Han. Seoul. Have a nice day.OOPS, I mean

> 2005-01-16
by

> 2005-01-06
Pohwan Han. Seoul. Have a nice day.
"Han" <hp4444@.kornet.net.korea> wrote in message
news:uh77TExpFHA.3084@.TK2MSFTNGP09.phx.gbl...
> Dear
> Can I compare two duration groups? e.g.,
> table1
> --
> id, start, end
> --
> 1, 2005-01-01, 2005-01-30
> 2, 2005-02-01, 2005-02-28
> table2
> --
> id_ref, start, end
> --
> 1, 2005-01-01, 2005-01-15
> 1, 2005-01-17, 2005-01-30
> The table1 is a parent of the table2. The duration /2005-01-01 ~
> 2005-01-30/ has two child durations in the table2. But the table1 has one
> missing day or duration in the table2.
> 2005-01-06
> I want to know if there is a missing day or duration in a given duration
> in the table1.
> Theoritically,
> 1. enumerate all the days belonged to the duration of table1
> 2. check each days against the durations of the table2
> Then I may know if there is a missing day or not. But looks inefficient.
> Do you have some better idea?
> --
> Pohwan Han. Seoul. Have a nice day.|||Please post DDL and DML statements.
Regards,
"Han" wrote:

> Dear
> Can I compare two duration groups? e.g.,
> table1
> --
> id, start, end
> --
> 1, 2005-01-01, 2005-01-30
> 2, 2005-02-01, 2005-02-28
> table2
> --
> id_ref, start, end
> --
> 1, 2005-01-01, 2005-01-15
> 1, 2005-01-17, 2005-01-30
> The table1 is a parent of the table2. The duration /2005-01-01 ~ 2005-01-3
0/
> has two child durations in the table2. But the table1 has one missing day
or
> duration in the table2.
> 2005-01-06
> I want to know if there is a missing day or duration in a given duration i
n
> the table1.
> Theoritically,
> 1. enumerate all the days belonged to the duration of table1
> 2. check each days against the durations of the table2
> Then I may know if there is a missing day or not. But looks inefficient.
> Do you have some better idea?
> --
> Pohwan Han. Seoul. Have a nice day.
>|||OK, here is one.
drop table table1
drop table table2
create table table1 (i int, start1 datetime, end1 datetime)
insert into table1 values(1, '2005-01-01', '2005-01-30')
insert into table1 values(2, '2005-02-01', '2005-02-28')
create table table2 (i int, start2 datetime, end2 datetime)
insert into table2 values(1, '2005-01-01', '2005-01-15')
insert into table2 values(1, '2005-01-17', '2005-01-30')
Pohwan Han. Seoul. Have a nice day.
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:81AFFC28-8CE9-432E-A220-BC7E265014FC@.microsoft.com...
> Please post DDL and DML statements.
> Regards,
> "Han" wrote:
>|||Han
Create a calendar table (look at Aaron's web site www.aspfaq.com) and chek
it againts your table
Why does it seem inefficient to you?
"Han" <hp4444@.kornet.net.korea> wrote in message
news:OFIXJPxpFHA.708@.TK2MSFTNGP09.phx.gbl...
> OK, here is one.
> drop table table1
> drop table table2
> create table table1 (i int, start1 datetime, end1 datetime)
> insert into table1 values(1, '2005-01-01', '2005-01-30')
> insert into table1 values(2, '2005-02-01', '2005-02-28')
> create table table2 (i int, start2 datetime, end2 datetime)
> insert into table2 values(1, '2005-01-01', '2005-01-15')
> insert into table2 values(1, '2005-01-17', '2005-01-30')
> --
> Pohwan Han. Seoul. Have a nice day.
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:81AFFC28-8CE9-432E-A220-BC7E265014FC@.microsoft.com...
>|||I think I got it myself.
select count(*) from table2
where
i=1 and
convert(int, start2) not in (select convert(int, end2)+1 from table2)
will check if every start2 is continued by end2 of the previous record. The
result should be 1, or the data is incorrect.
The rest is just house keeping.
Pohwan Han. Seoul. Have a nice day.
"Han" <hp4444@.kornet.net.korea> wrote in message
news:uh77TExpFHA.3084@.TK2MSFTNGP09.phx.gbl...
> Dear
> Can I compare two duration groups? e.g.,
> table1
> --
> id, start, end
> --
> 1, 2005-01-01, 2005-01-30
> 2, 2005-02-01, 2005-02-28
> table2
> --
> id_ref, start, end
> --
> 1, 2005-01-01, 2005-01-15
> 1, 2005-01-17, 2005-01-30
> The table1 is a parent of the table2. The duration /2005-01-01 ~
> 2005-01-30/ has two child durations in the table2. But the table1 has one
> missing day or duration in the table2.
> 2005-01-06
> I want to know if there is a missing day or duration in a given duration
> in the table1.
> Theoritically,
> 1. enumerate all the days belonged to the duration of table1
> 2. check each days against the durations of the table2
> Then I may know if there is a missing day or not. But looks inefficient.
> Do you have some better idea?
> --
> Pohwan Han. Seoul. Have a nice day.

Comparing 2 different Group's aggregate Values

Hi All,
In my report, i've 3 groups and i want to hide a group footer by
comparing a textbox value in group1 and a textbox value in group2, for
eg., i've Sum(Fields!Amount.Value,"table1_group1") in one of group1
footer's textbox and Sum(Fields!Amount.Value,"table1_group2") in one of
group2 footer's textbox, if both these values are same i want to hide
group1 footer, but i am getting "The value expression for the textbox
has a scope parameter that is not valid for an aggregate function. The
scope parameter must be set to a string constant that is equal to
either the name of a containing group, the name of a containing data
region, or the name of a data set.", any workaround methods for this?.
Your help is highly appreciated.
Thanks in advance
SenTextboxes are ReportItems. To hide a footer base on the value of two
textboxes set the Visible(Hidden) property to:
1. =ReportItems!textbox1.Value + ReportItems!textbox2.Value = 10 or
=IIF(ReportItems!textbox1.Value + ReportItems!textbox2.Value = 10,
False, True)
"Sen" wrote:
> Hi All,
> In my report, i've 3 groups and i want to hide a group footer by
> comparing a textbox value in group1 and a textbox value in group2, for
> eg., i've Sum(Fields!Amount.Value,"table1_group1") in one of group1
> footer's textbox and Sum(Fields!Amount.Value,"table1_group2") in one of
> group2 footer's textbox, if both these values are same i want to hide
> group1 footer, but i am getting "The value expression for the textbox
> has a scope parameter that is not valid for an aggregate function. The
> scope parameter must be set to a string constant that is equal to
> either the name of a containing group, the name of a containing data
> region, or the name of a data set.", any workaround methods for this?.
> Your help is highly appreciated.
> Thanks in advance
> Sen
>|||Hi,
Thanks for your suggestion.
I tried your option also, but i'm getting the following error,
"Report item expressions can only refer to other report items within the
same grouping scope or a containing grouping scope". The problem here is i'm
trying to compare 2 different group's textboxes. So i'm getting this error.
Any help is highly appreciated. I tried with other options of hidden
expressions and all, but it didn't help me. I cannot modify the Stored Procs
used for this report.
Thanks and Regards,
Sen
"OriginalStealth" wrote:
> Textboxes are ReportItems. To hide a footer base on the value of two
> textboxes set the Visible(Hidden) property to:
> 1. =ReportItems!textbox1.Value + ReportItems!textbox2.Value = 10 or
> =IIF(ReportItems!textbox1.Value + ReportItems!textbox2.Value = 10,
> False, True)
>
> "Sen" wrote:
> > Hi All,
> >
> > In my report, i've 3 groups and i want to hide a group footer by
> > comparing a textbox value in group1 and a textbox value in group2, for
> > eg., i've Sum(Fields!Amount.Value,"table1_group1") in one of group1
> > footer's textbox and Sum(Fields!Amount.Value,"table1_group2") in one of
> > group2 footer's textbox, if both these values are same i want to hide
> > group1 footer, but i am getting "The value expression for the textbox
> > has a scope parameter that is not valid for an aggregate function. The
> > scope parameter must be set to a string constant that is equal to
> > either the name of a containing group, the name of a containing data
> > region, or the name of a data set.", any workaround methods for this?.
> > Your help is highly appreciated.
> >
> > Thanks in advance
> > Sen
> >
> >sqlsql

Comments on configuring partitions

Hello,

I'm currently building partitions into my cube. My scenario is thus:

I have 74 measure groups (yeah I know - it sounds ridiculous but trust me, it IS valid)

Each measure group is to be partitioned by year

We are (currently) going to partition them over 12 years

In other words I have to build 74*12 = 888 partitions

Quite apart from the fact that its going to take most of my working week (and it'll be the most boring working week of my life) there is huge potential for human error here.

As stated above I am applying the same partitioning strategy for each of my 74 measure groups so I am repeating the same basic steps 74 times. It occurs to me that there MUST be a better way and if there isn't - there should be!

Does anyone know of a better way? I'd like to automate it somehow but I don't see how that is possible seeing as I have to edit a SQL statement for each one.

Anyone got any thoughts? I'm happy to provide feedback to MSFT about how this could be improved - contact me offline if interested - jamie.thomson[@.]nospam.conchango.com

ta

Jamie

I've done something similar using an SSIS package - I just scripted out the XML/A necessary to build a partition to use as a template, then created a FOR loop inside my package, used some script to generate the XMLA (and the SQL inside it) and then executed it. It worked pretty well, and I'll send you the code even though I know you're more than capable of doing this yourself. I agree it would be nice to have this functionality built into BIDS.

One thing that caught me here though was the problem with setting the slices for partitions that came up at the BI evening we were at, and which is discussed on Mark Hill's blog:
http://markhill.org/blog/?p=11
I created around 600 partitions on my cube and some of them turned out to have very little or no data in; this meant that no slicers got set automatically and my query performance suffered as a result.

Chris

|||

[Chris sent me this stuff offline]

Cheers Chris. That's good stuff and works well.

HOWEVER, this only works once the cube has been deployed. I want the partitions to appear in my BIDS solution (I should have said that originally - sorry about that). Is there a way to reverse-engineer them back in?

-Jamie

|||

Yes, you can reverse engineer a project from a deployed database very easily. All you need to do is open BIDS, then File/New/Project and select "Import Analysis Services 9.0 Database".

Chris

|||

After looking at Chris' package its just occurred to me....hod useful would it be to have an AMO Enumerator in the SSIS ForEach loop?

That would be brilliant!! Microsoft Connect here I come!!!

-Jamie

|||

Jamie Thomson wrote:

After looking at Chris' package its just occurred to me....hod useful would it be to have an AMO Enumerator in the SSIS ForEach loop?

That would be brilliant!! Microsoft Connect here I come!!!

-Jamie

And here it is: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=163202

Click through and vote would you please!!! And leave a comment. Much more likely to get it that way!

|||

Hi Jamie:

You could probably have relational one-to-one for your partitions. That way - you could iterate that in SSIS and conditionally create the partitions if required - and then process them.

Hope that helps.

Suranjan