Dimension question

I asked this question in the MS OLAP and the SQL group but got no
response. I am
hoping somebody will have some answers here.
Thanks in advance
HI,
I need to create a dimension that will play off another dimension. For
example: lets say we have branches going into discontinued operations
time to time. What we would like to create is a dimension that will
have the Discontinued status with Year. So something like:
2007 Discontinued
2006 Discontinued
2005 Discontinued
By clicking on each of those members we can effect a 2nd dimension
called Branch which will have its own parent child rollup for example:
United Kingdom
Birmingham
London
USA
New York
Dallas
My question is what is the best way to design this. Thanks for all
your help.
I'll bite.
* This only works if the Branch dimension isn't a true parent-child
hierarchy. *
You need to modify the Branch dimension by adding a Status attribute.
Each branch must be tagged with: "Active", "2007 Discontinued", "2006
Discontinued"... you get the idea.
Your branch dimension has a regular hierarchy without Status, i.e.
Country > City > Location, that people currently use. Add a new
hierarchy named "Branches by Status" or something, and build this one
as Status > Country > City > Location. If users want to navigate
starting at Status, they use that instead.
If the Branch dimension is a parent-child, then why? Parent-child
isn't a good idea design-wise because you lose context - SSAS doesn't
know that the USA node is a country, and if someone asks you for a
list of countries, you can't give it to them. There's also the small
issue of poor support in SSAS - which we've been grappling with for
almost a decade.
The only time you functionally need a parent-child is if you need
unary operators for rollups. I look forward to the day when unary
operators are available for normal hierarchies.
Otherwise, put in the time to un-ragged your ragged hierarchy. In my
experience, designers are lazy and don't want to do the hard schema
and ETL work to normalize a ragged hierarchy, and reason away their
laziness with the notion that "parent-child is flexible". You lose a
lot for that supposed flexibility.
On Jan 18, 2:45 pm, Mark T <vwttra...@.hotmail.com> wrote:
> I asked this question in the MS OLAP and the SQL group but got no
> response. I am
> hoping somebody will have some answers here.
> Thanks in advance
> --
> HI,
> I need to create a dimension that will play off another dimension. For
> example: lets say we have branches going into discontinued operations
> time to time. What we would like to create is a dimension that will
> have the Discontinued status with Year. So something like:
> 2007 Discontinued
> 2006 Discontinued
> 2005 Discontinued
> By clicking on each of those members we can effect a 2nd dimension
> called Branch which will have its own parent child rollup for example:
> United Kingdom
> Birmingham
> London
> USA
> New York
> Dallas
> My question is what is the best way to design this. Thanks for all
> your help.
|||On Jan 18, 1:04 pm, entaroadun <johnny.c.k...@.gmail.com> wrote:[vbcol=seagreen]
> I'll bite.
> * This only works if the Branch dimension isn't a true parent-child
> hierarchy. *
> You need to modify the Branch dimension by adding a Status attribute.
> Each branch must be tagged with: "Active", "2007 Discontinued", "2006
> Discontinued"... you get the idea.
> Your branch dimension has a regular hierarchy without Status, i.e.
> Country > City > Location, that people currently use. Add a new
> hierarchy named "Branches by Status" or something, and build this one
> as Status > Country > City > Location. If users want to navigate
> starting at Status, they use that instead.
> If the Branch dimension is a parent-child, then why? Parent-child
> isn't a good idea design-wise because you lose context - SSAS doesn't
> know that the USA node is a country, and if someone asks you for a
> list of countries, you can't give it to them. There's also the small
> issue of poor support in SSAS - which we've been grappling with for
> almost a decade.
> The only time you functionally need a parent-child is if you need
> unary operators for rollups. I look forward to the day when unary
> operators are available for normal hierarchies.
> Otherwise, put in the time to un-ragged your ragged hierarchy. In my
> experience, designers are lazy and don't want to do the hard schema
> and ETL work to normalize a ragged hierarchy, and reason away their
> laziness with the notion that "parent-child is flexible". You lose a
> lot for that supposed flexibility.
> On Jan 18, 2:45 pm, Mark T <vwttra...@.hotmail.com> wrote:
>
>
Thanks for your reply. Unfortunately, i cant move away from the parent-
child relationship. The issue is all of our branch dimensions are
based on this and at least for now I need to follow the design
methodology that we have in place. So, i dont think this solution
would work... though i wish we didnt have to worry about the parent-
child issue. I did create multiple rollups somewhat similar to what
you defined:
1990 - Total Company
1991 - Total Company
1992 - Total Company
...and so on. These rollups were parent child but you can see the
problem... This list gets too long and cumbersome.

Different performance in different machines,,,

Hi All,
I am looking if somebody know how can this happen,,,
I have a couple of stored procedures that I run, one SP call the other.
There are cursor, memory table, and recursive query.
When I run this against a local database in my computer it takes arount 15
secs to return the result and it is just fine
considering the table is about 1 + million records. My PC is P4 512 kb RAM
Now, when I create this SP in the server machine and test them against the
database and run it, it takes more than 10 minutes.
Amazing!! considering is the same table, same indexes, but this server has 4
processors and 1 gig RAM.
What can that be? Can it be a MS SQL configuration?
Thanks in advance
RobertoAre statistics up to date on the server?
"Roberto Martinez" <roberto@.ccubetech.com> wrote in message
news:ecFiunkJEHA.3436@.tk2msftngp13.phx.gbl...
> Hi All,
> I am looking if somebody know how can this happen,,,
> I have a couple of stored procedures that I run, one SP call the other.
> There are cursor, memory table, and recursive query.
> When I run this against a local database in my computer it takes arount 15
> secs to return the result and it is just fine
> considering the table is about 1 + million records. My PC is P4 512 kb RAM
> Now, when I create this SP in the server machine and test them against the
> database and run it, it takes more than 10 minutes.
> Amazing!! considering is the same table, same indexes, but this server has
4
> processors and 1 gig RAM.
> What can that be? Can it be a MS SQL configuration?
> Thanks in advance
> Roberto
>|||What does you mean with statistic are up to date on the server?
"chris" <chris@.noemail.com> wrote in message
news:%23q$6gDlJEHA.3712@.TK2MSFTNGP09.phx.gbl...
> Are statistics up to date on the server?
>
> "Roberto Martinez" <roberto@.ccubetech.com> wrote in message
> news:ecFiunkJEHA.3436@.tk2msftngp13.phx.gbl...
> > Hi All,
> >
> > I am looking if somebody know how can this happen,,,
> >
> > I have a couple of stored procedures that I run, one SP call the other.
> > There are cursor, memory table, and recursive query.
> > When I run this against a local database in my computer it takes arount
15
> > secs to return the result and it is just fine
> > considering the table is about 1 + million records. My PC is P4 512 kb
RAM
> >
> > Now, when I create this SP in the server machine and test them against
the
> > database and run it, it takes more than 10 minutes.
> > Amazing!! considering is the same table, same indexes, but this server
has
> 4
> > processors and 1 gig RAM.
> >
> > What can that be? Can it be a MS SQL configuration?
> >
> > Thanks in advance
> >
> > Roberto
> >
> >
>|||Check the query plan for the queries on both machines to ensure the same
indexes are used both places.
If they differ, try doing
update statistics <tablename>
on each table used in the query, and look at the Query plan, and compare
execution times. again..
Lastly ( perhaps) try setting maxdop to 1 for the query on the multi proc
machine and see if performance improves...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Roberto Martinez" <roberto@.ccubetech.com> wrote in message
news:ecFiunkJEHA.3436@.tk2msftngp13.phx.gbl...
> Hi All,
> I am looking if somebody know how can this happen,,,
> I have a couple of stored procedures that I run, one SP call the other.
> There are cursor, memory table, and recursive query.
> When I run this against a local database in my computer it takes arount 15
> secs to return the result and it is just fine
> considering the table is about 1 + million records. My PC is P4 512 kb RAM
> Now, when I create this SP in the server machine and test them against the
> database and run it, it takes more than 10 minutes.
> Amazing!! considering is the same table, same indexes, but this server has
4
> processors and 1 gig RAM.
> What can that be? Can it be a MS SQL configuration?
> Thanks in advance
> Roberto
>

Different performance in different machines,,,

Hi All,
I am looking if somebody know how can this happen,,,
I have a couple of stored procedures that I run, one SP call the other.
There are cursor, memory table, and recursive query.
When I run this against a local database in my computer it takes arount 15
secs to return the result and it is just fine
considering the table is about 1 + million records. My PC is P4 512 kb RAM
Now, when I create this SP in the server machine and test them against the
database and run it, it takes more than 10 minutes.
Amazing!! considering is the same table, same indexes, but this server has 4
processors and 1 gig RAM.
What can that be? Can it be a MS SQL configuration?
Thanks in advance
RobertoAre statistics up to date on the server?
"Roberto Martinez" <roberto@.ccubetech.com> wrote in message
news:ecFiunkJEHA.3436@.tk2msftngp13.phx.gbl...
> Hi All,
> I am looking if somebody know how can this happen,,,
> I have a couple of stored procedures that I run, one SP call the other.
> There are cursor, memory table, and recursive query.
> When I run this against a local database in my computer it takes arount 15
> secs to return the result and it is just fine
> considering the table is about 1 + million records. My PC is P4 512 kb RAM
> Now, when I create this SP in the server machine and test them against the
> database and run it, it takes more than 10 minutes.
> Amazing!! considering is the same table, same indexes, but this server has
4
> processors and 1 gig RAM.
> What can that be? Can it be a MS SQL configuration?
> Thanks in advance
> Roberto
>|||What does you mean with statistic are up to date on the server?
"chris" <chris@.noemail.com> wrote in message
news:%23q$6gDlJEHA.3712@.TK2MSFTNGP09.phx.gbl...
> Are statistics up to date on the server?
>
> "Roberto Martinez" <roberto@.ccubetech.com> wrote in message
> news:ecFiunkJEHA.3436@.tk2msftngp13.phx.gbl...
15[vbcol=seagreen]
RAM[vbcol=seagreen]
the[vbcol=seagreen]
has[vbcol=seagreen]
> 4
>|||Check the query plan for the queries on both machines to ensure the same
indexes are used both places.
If they differ, try doing
update statistics <tablename>
on each table used in the query, and look at the Query plan, and compare
execution times. again..
Lastly ( perhaps) try setting maxdop to 1 for the query on the multi proc
machine and see if performance improves...
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Roberto Martinez" <roberto@.ccubetech.com> wrote in message
news:ecFiunkJEHA.3436@.tk2msftngp13.phx.gbl...
> Hi All,
> I am looking if somebody know how can this happen,,,
> I have a couple of stored procedures that I run, one SP call the other.
> There are cursor, memory table, and recursive query.
> When I run this against a local database in my computer it takes arount 15
> secs to return the result and it is just fine
> considering the table is about 1 + million records. My PC is P4 512 kb RAM
> Now, when I create this SP in the server machine and test them against the
> database and run it, it takes more than 10 minutes.
> Amazing!! considering is the same table, same indexes, but this server has
4
> processors and 1 gig RAM.
> What can that be? Can it be a MS SQL configuration?
> Thanks in advance
> Roberto
>

Different performance in different machines,,,

Hi All,
I am looking if somebody know how can this happen,,,
I have a couple of stored procedures that I run, one SP call the other.
There are cursor, memory table, and recursive query.
When I run this against a local database in my computer it takes arount 15
secs to return the result and it is just fine
considering the table is about 1 + million records. My PC is P4 512 kb RAM
Now, when I create this SP in the server machine and test them against the
database and run it, it takes more than 10 minutes.
Amazing!! considering is the same table, same indexes, but this server has 4
processors and 1 gig RAM.
What can that be? Can it be a MS SQL configuration?
Thanks in advance
Roberto
Are statistics up to date on the server?
"Roberto Martinez" <roberto@.ccubetech.com> wrote in message
news:ecFiunkJEHA.3436@.tk2msftngp13.phx.gbl...
> Hi All,
> I am looking if somebody know how can this happen,,,
> I have a couple of stored procedures that I run, one SP call the other.
> There are cursor, memory table, and recursive query.
> When I run this against a local database in my computer it takes arount 15
> secs to return the result and it is just fine
> considering the table is about 1 + million records. My PC is P4 512 kb RAM
> Now, when I create this SP in the server machine and test them against the
> database and run it, it takes more than 10 minutes.
> Amazing!! considering is the same table, same indexes, but this server has
4
> processors and 1 gig RAM.
> What can that be? Can it be a MS SQL configuration?
> Thanks in advance
> Roberto
>
|||What does you mean with statistic are up to date on the server?
"chris" <chris@.noemail.com> wrote in message
news:%23q$6gDlJEHA.3712@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Are statistics up to date on the server?
>
> "Roberto Martinez" <roberto@.ccubetech.com> wrote in message
> news:ecFiunkJEHA.3436@.tk2msftngp13.phx.gbl...
15[vbcol=seagreen]
RAM[vbcol=seagreen]
the[vbcol=seagreen]
has
> 4
>
|||Check the query plan for the queries on both machines to ensure the same
indexes are used both places.
If they differ, try doing
update statistics <tablename>
on each table used in the query, and look at the Query plan, and compare
execution times. again..
Lastly ( perhaps) try setting maxdop to 1 for the query on the multi proc
machine and see if performance improves...
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Roberto Martinez" <roberto@.ccubetech.com> wrote in message
news:ecFiunkJEHA.3436@.tk2msftngp13.phx.gbl...
> Hi All,
> I am looking if somebody know how can this happen,,,
> I have a couple of stored procedures that I run, one SP call the other.
> There are cursor, memory table, and recursive query.
> When I run this against a local database in my computer it takes arount 15
> secs to return the result and it is just fine
> considering the table is about 1 + million records. My PC is P4 512 kb RAM
> Now, when I create this SP in the server machine and test them against the
> database and run it, it takes more than 10 minutes.
> Amazing!! considering is the same table, same indexes, but this server has
4
> processors and 1 gig RAM.
> What can that be? Can it be a MS SQL configuration?
> Thanks in advance
> Roberto
>

different formats of datetime?

Can somebody explain me why or due to what I have different formats of the same datetime data (SS2005) while opening a table (SS Mangmt Studio) and quering it?

For ex., AdventureWorks database, HumanResources.Department table, ModifiedDate column
1) RighClicking on table in ObjectExplorer (MS SQL Server Management Studio) --> Open Table shows the datetime data like
01.06.1998 0:00:00
2) executing
select ModifiedDate from HumanResources.Department
shows the same as
1998-06-01 00:00:00.000

Guennadi Vanine
PS.
I am trying to resolve the problem described in
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=180740&SiteID=1

Because datetime representation completely defined by client application, not by server storage. In a database this type stored in 8 byte container and its internal format has nothing in common with what you see when you retrieve the data.

About these two different formats... the first one is a typical German or Russian national date representation. In this place SSMS takes into consideration your computer' locale settings. The second one is a default format used in a query results.|||

Hmm, I see. The first one coincides with date in my Windows XP (Russian) though I am surprised that it is typical Russian (I have never seen the dates here used with dots, then Russia is multinational and multicultural federation having various republics)

Anyway, it is useful to remeber since this format from OS is the same (and independent on default language of installed db instance) for db instances with different different languages installed. And the default language/collation cannot be reset but only fresh (re)installed

I am still bewildered what is default language for? This does not have much sense to me especially due to the problem it is causing in SSIS [1], [2]

That one I could overcome only by installing additional db instance with English(US) as default language

[1]
collation or local sensitive settings or other configuration properties
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1566456&SiteID=1&mode=1
[2]
ETL Package Problem
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=603488&SiteID=1