Modelling Time Span with OLAP tools

This is more of a design question. I am trying to create a fact table for ti
me spans. For example an order might go through the following status of Crea
ted, In-Progress, Delivered. The time span between any 2 states could range
from minutes to days to mon
ths. I want to be able to see the current status and also the status as of l
ast month, or last quarter or some other previous date.
Some of the recommendations by extert sites are to have a date in your fact
table for each status accompained by a field
for storing a value 1. So the order fact by look like
Order Id
Order Create Date
Order Created Value
Order In-Progress Date
Order In-Progress Value
Order Delivered Date
Order Delivered Value
Also I am creating my own time dimension. The above approach will work very
well when you use SQLs to access your star schema, but I am not so sure this
approach will work for olap tools like MSAS.
Is there an alternative?
ThanksWhat you have here looks like an accumulating snapshot fact table. These
are best employed IMHO where you have a business process/relatively short
with milestones which you want to track.
It means that you have less rows in your fact table but that you will
revisit the row to perform UPDATEs, something not typically done.
This type of fact table is good for measuring time lags between business
processes i.e.
Time from Order -> Sale
Time from Sale -> Ship
The way I do this is to create a view over the top of the fact table which
calculates these figures for me. This way i can then use it as a measure
just like any other. I usually do it to difference in hours.
--
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Break It" <anonymous@.discussions.microsoft.com> wrote in message
news:13CB4DFA-500D-4268-B850-5EE207F917CD@.microsoft.com...
> This is more of a design question. I am trying to create a fact table for
time spans. For example an order might go through the following status of
Created, In-Progress, Delivered. The time span between any 2 states could
range from minutes to days to months. I want to be able to see the current
status and also the status as of last month, or last quarter or some other
previous date.
> Some of the recommendations by extert sites are to have a date in your
fact table for each status accompained by a field
> for storing a value 1. So the order fact by look like
> Order Id
> Order Create Date
> Order Created Value
> Order In-Progress Date
> Order In-Progress Value
> Order Delivered Date
> Order Delivered Value
> Also I am creating my own time dimension. The above approach will work
very well when you use SQLs to access your star schema, but I am not so sure
this approach will work for olap tools like MSAS.
> Is there an alternative?
> Thanks

Model Database Status is 1073741824

After upgrade SQL Server 7 to SQL Server 2000, the model database status bec
omes 1073741824. What does it mean? I can create a new database in this stat
us. How do I change it?
Thanks
Walter> After upgrade SQL Server 7 to SQL Server 2000, the model database status
becomes 1073741824. What does it mean?
From the SQL Server 2000 Books Online
<herf="tsqlref.chm::/ts_sys-d_5xrn.htm">, 1073741824 = cleanly shutdown.

> I can create a new database in this status. How do I change it?
I don't understand this part of your question. Please elaborate.
Hope this helps.
Dan Guzman
SQL Server MVP
"Walter" <Walter@.discussions.microsoft.com> wrote in message
news:E1EE009E-34CB-4681-935C-1B30B6DF1B52@.microsoft.com...
> After upgrade SQL Server 7 to SQL Server 2000, the model database status
becomes 1073741824. What does it mean? I can create a new database in this
status. How do I change it?
> Thanks
> Walter|||After upgrade SQL Server from 7 to 2000, the server looks fine and I can cre
ate new database. However,the model database status is 1073741824 (cleanly s
hutdown) (before update SQL Server, the status is 0). It is a problem? Can I
say the upgrade is success
ful? Can I change the status and how to do?
"Dan Guzman" wrote:

> becomes 1073741824. What does it mean?
> From the SQL Server 2000 Books Online
> <herf="tsqlref.chm::/ts_sys-d_5xrn.htm">, 1073741824 = cleanly shutdown.
>
> I don't understand this part of your question. Please elaborate.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Walter" <Walter@.discussions.microsoft.com> wrote in message
> news:E1EE009E-34CB-4681-935C-1B30B6DF1B52@.microsoft.com...
> becomes 1073741824. What does it mean? I can create a new database in this
> status. How do I change it?
>
>|||Yes, this is a normal status for the model database. My test server, a new
SQL 2000 install, has status 1073741824.
Hope this helps.
Dan Guzman
SQL Server MVP
"Walter" <Walter@.discussions.microsoft.com> wrote in message
news:AEE3D7B1-3FD3-4BC8-A656-25BC7F1E1382@.microsoft.com...
> After upgrade SQL Server from 7 to 2000, the server looks fine and I can
create new database. However,the model database status is 1073741824
(cleanly shutdown) (before update SQL Server, the status is 0). It is a
problem? Can I say the upgrade is successful? Can I change the status and
how to do?[vbcol=seagreen]
> "Dan Guzman" wrote:
>
status[vbcol=seagreen]
status[vbcol=seagreen]
this[vbcol=seagreen]sql

Model Database Status is 1073741824

After upgrade SQL Server 7 to SQL Server 2000, the model database status becomes 1073741824. What does it mean? I can create a new database in this status. How do I change it?
Thanks
Walter> After upgrade SQL Server 7 to SQL Server 2000, the model database status
becomes 1073741824. What does it mean?
From the SQL Server 2000 Books Online
<herf="tsqlref.chm::/ts_sys-d_5xrn.htm">, 1073741824 = cleanly shutdown.
> I can create a new database in this status. How do I change it?
I don't understand this part of your question. Please elaborate.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Walter" <Walter@.discussions.microsoft.com> wrote in message
news:E1EE009E-34CB-4681-935C-1B30B6DF1B52@.microsoft.com...
> After upgrade SQL Server 7 to SQL Server 2000, the model database status
becomes 1073741824. What does it mean? I can create a new database in this
status. How do I change it?
> Thanks
> Walter|||After upgrade SQL Server from 7 to 2000, the server looks fine and I can create new database. However,the model database status is 1073741824 (cleanly shutdown) (before update SQL Server, the status is 0). It is a problem? Can I say the upgrade is successful? Can I change the status and how to do?
"Dan Guzman" wrote:
> > After upgrade SQL Server 7 to SQL Server 2000, the model database status
> becomes 1073741824. What does it mean?
> From the SQL Server 2000 Books Online
> <herf="tsqlref.chm::/ts_sys-d_5xrn.htm">, 1073741824 = cleanly shutdown.
> > I can create a new database in this status. How do I change it?
> I don't understand this part of your question. Please elaborate.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Walter" <Walter@.discussions.microsoft.com> wrote in message
> news:E1EE009E-34CB-4681-935C-1B30B6DF1B52@.microsoft.com...
> > After upgrade SQL Server 7 to SQL Server 2000, the model database status
> becomes 1073741824. What does it mean? I can create a new database in this
> status. How do I change it?
> >
> > Thanks
> > Walter
>
>|||Yes, this is a normal status for the model database. My test server, a new
SQL 2000 install, has status 1073741824.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Walter" <Walter@.discussions.microsoft.com> wrote in message
news:AEE3D7B1-3FD3-4BC8-A656-25BC7F1E1382@.microsoft.com...
> After upgrade SQL Server from 7 to 2000, the server looks fine and I can
create new database. However,the model database status is 1073741824
(cleanly shutdown) (before update SQL Server, the status is 0). It is a
problem? Can I say the upgrade is successful? Can I change the status and
how to do?
> "Dan Guzman" wrote:
> > > After upgrade SQL Server 7 to SQL Server 2000, the model database
status
> > becomes 1073741824. What does it mean?
> >
> > From the SQL Server 2000 Books Online
> > <herf="tsqlref.chm::/ts_sys-d_5xrn.htm">, 1073741824 = cleanly shutdown.
> >
> > > I can create a new database in this status. How do I change it?
> >
> > I don't understand this part of your question. Please elaborate.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "Walter" <Walter@.discussions.microsoft.com> wrote in message
> > news:E1EE009E-34CB-4681-935C-1B30B6DF1B52@.microsoft.com...
> > > After upgrade SQL Server 7 to SQL Server 2000, the model database
status
> > becomes 1073741824. What does it mean? I can create a new database in
this
> > status. How do I change it?
> > >
> > > Thanks
> > > Walter
> >
> >
> >

Model Database Status is 1073741824

After upgrade SQL Server 7 to SQL Server 2000, the model database status becomes 1073741824. What does it mean? I can create a new database in this status. How do I change it?
Thanks
Walter
> After upgrade SQL Server 7 to SQL Server 2000, the model database status
becomes 1073741824. What does it mean?
From the SQL Server 2000 Books Online
<herf="tsqlref.chm::/ts_sys-d_5xrn.htm">, 1073741824 = cleanly shutdown.

> I can create a new database in this status. How do I change it?
I don't understand this part of your question. Please elaborate.
Hope this helps.
Dan Guzman
SQL Server MVP
"Walter" <Walter@.discussions.microsoft.com> wrote in message
news:E1EE009E-34CB-4681-935C-1B30B6DF1B52@.microsoft.com...
> After upgrade SQL Server 7 to SQL Server 2000, the model database status
becomes 1073741824. What does it mean? I can create a new database in this
status. How do I change it?
> Thanks
> Walter
|||After upgrade SQL Server from 7 to 2000, the server looks fine and I can create new database. However,the model database status is 1073741824 (cleanly shutdown) (before update SQL Server, the status is 0). It is a problem? Can I say the upgrade is success
ful? Can I change the status and how to do?
"Dan Guzman" wrote:

> becomes 1073741824. What does it mean?
> From the SQL Server 2000 Books Online
> <herf="tsqlref.chm::/ts_sys-d_5xrn.htm">, 1073741824 = cleanly shutdown.
>
> I don't understand this part of your question. Please elaborate.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Walter" <Walter@.discussions.microsoft.com> wrote in message
> news:E1EE009E-34CB-4681-935C-1B30B6DF1B52@.microsoft.com...
> becomes 1073741824. What does it mean? I can create a new database in this
> status. How do I change it?
>
>
|||Yes, this is a normal status for the model database. My test server, a new
SQL 2000 install, has status 1073741824.
Hope this helps.
Dan Guzman
SQL Server MVP
"Walter" <Walter@.discussions.microsoft.com> wrote in message
news:AEE3D7B1-3FD3-4BC8-A656-25BC7F1E1382@.microsoft.com...
> After upgrade SQL Server from 7 to 2000, the server looks fine and I can
create new database. However,the model database status is 1073741824
(cleanly shutdown) (before update SQL Server, the status is 0). It is a
problem? Can I say the upgrade is successful? Can I change the status and
how to do?[vbcol=seagreen]
> "Dan Guzman" wrote:
status[vbcol=seagreen]
status[vbcol=seagreen]
this[vbcol=seagreen]

Mission Impossible ? Pivot Table at front end Excel

I have a table with this field headers Product/Status/USD:-

A/Actual/100
A/Budget/90
A/Variance/10

I have created a Pivot Table in Excel with
Product as row field (A or B)
Status as Column field (Actual, Budget, Variance)
USD as Value

It looks fine like this but I need to create a column called Variance % which is (Variance/Budget x 100%).

Please urgently advise how to create this new % column within Pivot Table.

Thanks. Sorry it seems a bit more about Excel but can't get answers in Excel forum...

I am not using SQL Server.

help...

Sorry, but I think your chances better off with trying Excel forum.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.