Does SQL server install automatically with SBS?
I did an install last night for a client, not at server
at this time.
Did this install auto?I do not believe that it is part of the automated setup.
Rand
This posting is provided "as is" with no warranties and confers no rights.
Showing posts with label time. Show all posts
Showing posts with label time. Show all posts
Thursday, March 29, 2012
does sqlserver install auto w/Small Bus Srv 2003
Does SQL server install automatically with SBS?
I did an install last night for a client, not at server
at this time.
Did this install auto?I do not believe that it is part of the automated setup.
Rand
This posting is provided "as is" with no warranties and confers no rights.
I did an install last night for a client, not at server
at this time.
Did this install auto?I do not believe that it is part of the automated setup.
Rand
This posting is provided "as is" with no warranties and confers no rights.
Does SQL Server handles time zone difference?
Hi
Does SQL Server 200 handles time zone?
We want to transfer all data to another server periodically that is situated
at a different time zone.
Also some time we restore complete database to second server.
we want to keep correct time stored with the data as per the time zone where
second sql server is present.
how can this be achieved?
Regards
Lalit Bhatia
Hi Lalit
SQL Server is storing the time according to the time that it is
getting from the OS. If you modify the time at the OS level, then SQL
Server will NOT modify the values that it already has in the tables.
Personally I wouldn't want it to do so. I want the data to be
consistent and I wouldn't want it to updates all the records twice a
year because of day light savings.
If you have a requirement to store data in a way that users will see
different time according to the server's time zone, then you can store
the time as UCT time and present it as local time. You can use the
function GETUTCDATE() together with DATEADD() and GETDATE() when ever
you insert, modify or present the data. This way the data at the table
will be the same, but if you move the data to a different server in a
different time zone, then users will see the data that is adjusted to
there time zone. Be aware that this might be confusing. If a person
in N.Y is talking with a person in L.A about one of the records, each
one of them will see a different time.
Lalit wrote:
> Hi
> Does SQL Server 200 handles time zone?
> We want to transfer all data to another server periodically that is situated
> at a different time zone.
> Also some time we restore complete database to second server.
> we want to keep correct time stored with the data as per the time zone where
> second sql server is present.
> how can this be achieved?
>
> Regards
> Lalit Bhatia
Does SQL Server 200 handles time zone?
We want to transfer all data to another server periodically that is situated
at a different time zone.
Also some time we restore complete database to second server.
we want to keep correct time stored with the data as per the time zone where
second sql server is present.
how can this be achieved?
Regards
Lalit Bhatia
Hi Lalit
SQL Server is storing the time according to the time that it is
getting from the OS. If you modify the time at the OS level, then SQL
Server will NOT modify the values that it already has in the tables.
Personally I wouldn't want it to do so. I want the data to be
consistent and I wouldn't want it to updates all the records twice a
year because of day light savings.
If you have a requirement to store data in a way that users will see
different time according to the server's time zone, then you can store
the time as UCT time and present it as local time. You can use the
function GETUTCDATE() together with DATEADD() and GETDATE() when ever
you insert, modify or present the data. This way the data at the table
will be the same, but if you move the data to a different server in a
different time zone, then users will see the data that is adjusted to
there time zone. Be aware that this might be confusing. If a person
in N.Y is talking with a person in L.A about one of the records, each
one of them will see a different time.
Lalit wrote:
> Hi
> Does SQL Server 200 handles time zone?
> We want to transfer all data to another server periodically that is situated
> at a different time zone.
> Also some time we restore complete database to second server.
> we want to keep correct time stored with the data as per the time zone where
> second sql server is present.
> how can this be achieved?
>
> Regards
> Lalit Bhatia
Does SQL Server handles time zone difference?
Hi
Does SQL Server 200 handles time zone?
We want to transfer all data to another server periodically that is situated
at a different time zone.
Also some time we restore complete database to second server.
we want to keep correct time stored with the data as per the time zone where
second sql server is present.
how can this be achieved?
Regards
Lalit BhatiaHi Lalit
SQL Server is storing the time according to the time that it is
getting from the OS. If you modify the time at the OS level, then SQL
Server will NOT modify the values that it already has in the tables.
Personally I wouldn't want it to do so. I want the data to be
consistent and I wouldn't want it to updates all the records twice a
year because of day light savings.
If you have a requirement to store data in a way that users will see
different time according to the server's time zone, then you can store
the time as UCT time and present it as local time. You can use the
function GETUTCDATE() together with DATEADD() and GETDATE() when ever
you insert, modify or present the data. This way the data at the table
will be the same, but if you move the data to a different server in a
different time zone, then users will see the data that is adjusted to
there time zone. Be aware that this might be confusing. If a person
in N.Y is talking with a person in L.A about one of the records, each
one of them will see a different time.
Lalit wrote:
> Hi
> Does SQL Server 200 handles time zone?
> We want to transfer all data to another server periodically that is situat
ed
> at a different time zone.
> Also some time we restore complete database to second server.
> we want to keep correct time stored with the data as per the time zone whe
re
> second sql server is present.
> how can this be achieved?
>
> Regards
> Lalit Bhatia
Does SQL Server 200 handles time zone?
We want to transfer all data to another server periodically that is situated
at a different time zone.
Also some time we restore complete database to second server.
we want to keep correct time stored with the data as per the time zone where
second sql server is present.
how can this be achieved?
Regards
Lalit BhatiaHi Lalit
SQL Server is storing the time according to the time that it is
getting from the OS. If you modify the time at the OS level, then SQL
Server will NOT modify the values that it already has in the tables.
Personally I wouldn't want it to do so. I want the data to be
consistent and I wouldn't want it to updates all the records twice a
year because of day light savings.
If you have a requirement to store data in a way that users will see
different time according to the server's time zone, then you can store
the time as UCT time and present it as local time. You can use the
function GETUTCDATE() together with DATEADD() and GETDATE() when ever
you insert, modify or present the data. This way the data at the table
will be the same, but if you move the data to a different server in a
different time zone, then users will see the data that is adjusted to
there time zone. Be aware that this might be confusing. If a person
in N.Y is talking with a person in L.A about one of the records, each
one of them will see a different time.
Lalit wrote:
> Hi
> Does SQL Server 200 handles time zone?
> We want to transfer all data to another server periodically that is situat
ed
> at a different time zone.
> Also some time we restore complete database to second server.
> we want to keep correct time stored with the data as per the time zone whe
re
> second sql server is present.
> how can this be achieved?
>
> Regards
> Lalit Bhatia
Does SQL Server handles time zone difference?
Hi
Does SQL Server 200 handles time zone?
We want to transfer all data to another server periodically that is situated
at a different time zone.
Also some time we restore complete database to second server.
we want to keep correct time stored with the data as per the time zone where
second sql server is present.
how can this be achieved?
Regards
Lalit BhatiaHi Lalit
SQL Server is storing the time according to the time that it is
getting from the OS. If you modify the time at the OS level, then SQL
Server will NOT modify the values that it already has in the tables.
Personally I wouldn't want it to do so. I want the data to be
consistent and I wouldn't want it to updates all the records twice a
year because of day light savings.
If you have a requirement to store data in a way that users will see
different time according to the server's time zone, then you can store
the time as UCT time and present it as local time. You can use the
function GETUTCDATE() together with DATEADD() and GETDATE() when ever
you insert, modify or present the data. This way the data at the table
will be the same, but if you move the data to a different server in a
different time zone, then users will see the data that is adjusted to
there time zone. Be aware that this might be confusing. If a person
in N.Y is talking with a person in L.A about one of the records, each
one of them will see a different time.
Lalit wrote:
> Hi
> Does SQL Server 200 handles time zone?
> We want to transfer all data to another server periodically that is situated
> at a different time zone.
> Also some time we restore complete database to second server.
> we want to keep correct time stored with the data as per the time zone where
> second sql server is present.
> how can this be achieved?
>
> Regards
> Lalit Bhatiasql
Does SQL Server 200 handles time zone?
We want to transfer all data to another server periodically that is situated
at a different time zone.
Also some time we restore complete database to second server.
we want to keep correct time stored with the data as per the time zone where
second sql server is present.
how can this be achieved?
Regards
Lalit BhatiaHi Lalit
SQL Server is storing the time according to the time that it is
getting from the OS. If you modify the time at the OS level, then SQL
Server will NOT modify the values that it already has in the tables.
Personally I wouldn't want it to do so. I want the data to be
consistent and I wouldn't want it to updates all the records twice a
year because of day light savings.
If you have a requirement to store data in a way that users will see
different time according to the server's time zone, then you can store
the time as UCT time and present it as local time. You can use the
function GETUTCDATE() together with DATEADD() and GETDATE() when ever
you insert, modify or present the data. This way the data at the table
will be the same, but if you move the data to a different server in a
different time zone, then users will see the data that is adjusted to
there time zone. Be aware that this might be confusing. If a person
in N.Y is talking with a person in L.A about one of the records, each
one of them will see a different time.
Lalit wrote:
> Hi
> Does SQL Server 200 handles time zone?
> We want to transfer all data to another server periodically that is situated
> at a different time zone.
> Also some time we restore complete database to second server.
> we want to keep correct time stored with the data as per the time zone where
> second sql server is present.
> how can this be achieved?
>
> Regards
> Lalit Bhatiasql
Tuesday, March 27, 2012
Does SQL Server 2005 Express has the method that delete automatically rows ordered by time colum
Hi, I made table that stores monitoring data every 1 minute.
It should have only 60 minutes-data from current time.
I delete data which stored 60 minutes ago before insert.
I'll be happy to find that the database engine does automatically above procedure.
You could use an insert trigger which fires when new data is inserted. Place your delete statement here to delete old records.
--
SvenC
Does SQL Server 2005 Express has the method that delete automatically rows ordered by time c
Hi, I made table that stores monitoring data every 1 minute.
It should have only 60 minutes-data from current time.
I delete data which stored 60 minutes ago before insert.
I'll be happy to find that the database engine does automatically above procedure.
You could use an insert trigger which fires when new data is inserted. Place your delete statement here to delete old records.
--
SvenC
Sunday, March 25, 2012
Does SQL 2005 take XML query time into acount?
if there's an [untyped] XField.Query() in a select, does SQL 2005 do
anything (internally) like an automatic sub-select if there's standard
fields to compare, saving the XML analysis for later, (to avoid uneccessary
calculations)?Hello Jerry,
XML queries do cache their query plans, of course, like any other T-SQL quer
y.
However, unindexed XML has to reconstruct the node table each time. That's
an expensive unit of work (usually), so the best thing you can (usually)
do is create an primary XML index over those instances. That "kind of" does
that you're looking for here.
Cheers,
Kent|||> XML queries do cache their query plans, of course, like any other T-SQL
> query. However, unindexed XML has to reconstruct the node table each time.
> That's an expensive unit of work (usually), so the best thing you can
> (usually) do is create an primary XML index over those instances. That
> "kind of" does that you're looking for here.
Thanks for all, Kent ;)
anything (internally) like an automatic sub-select if there's standard
fields to compare, saving the XML analysis for later, (to avoid uneccessary
calculations)?Hello Jerry,
XML queries do cache their query plans, of course, like any other T-SQL quer
y.
However, unindexed XML has to reconstruct the node table each time. That's
an expensive unit of work (usually), so the best thing you can (usually)
do is create an primary XML index over those instances. That "kind of" does
that you're looking for here.
Cheers,
Kent|||> XML queries do cache their query plans, of course, like any other T-SQL
> query. However, unindexed XML has to reconstruct the node table each time.
> That's an expensive unit of work (usually), so the best thing you can
> (usually) do is create an primary XML index over those instances. That
> "kind of" does that you're looking for here.
Thanks for all, Kent ;)
Does SQL 2005 take XML query time into acount?
if there's an [untyped] XField.Query() in a select, does SQL 2005 do
anything (internally) like an automatic sub-select if there's standard
fields to compare, saving the XML analysis for later, (to avoid uneccessary
calculations)?
Hello Jerry,
XML queries do cache their query plans, of course, like any other T-SQL query.
However, unindexed XML has to reconstruct the node table each time. That's
an expensive unit of work (usually), so the best thing you can (usually)
do is create an primary XML index over those instances. That "kind of" does
that you're looking for here.
Cheers,
Kent
|||> XML queries do cache their query plans, of course, like any other T-SQL
> query. However, unindexed XML has to reconstruct the node table each time.
> That's an expensive unit of work (usually), so the best thing you can
> (usually) do is create an primary XML index over those instances. That
> "kind of" does that you're looking for here.
Thanks for all, Kent ;)
sql
anything (internally) like an automatic sub-select if there's standard
fields to compare, saving the XML analysis for later, (to avoid uneccessary
calculations)?
Hello Jerry,
XML queries do cache their query plans, of course, like any other T-SQL query.
However, unindexed XML has to reconstruct the node table each time. That's
an expensive unit of work (usually), so the best thing you can (usually)
do is create an primary XML index over those instances. That "kind of" does
that you're looking for here.
Cheers,
Kent
|||> XML queries do cache their query plans, of course, like any other T-SQL
> query. However, unindexed XML has to reconstruct the node table each time.
> That's an expensive unit of work (usually), so the best thing you can
> (usually) do is create an primary XML index over those instances. That
> "kind of" does that you're looking for here.
Thanks for all, Kent ;)
sql
Thursday, March 22, 2012
Does SETROWCOUNT 10 apply to only the sp in which it used or outside it also?
I am using SETROWCOUNT 10 in my stored procedure. At end of thi sp I use SETROWCOUNT 0.
Will all other sp's that are executing at the same time as the above sp, get affected by the above SETROWCOUNT statement?
The option SET ROWCOUNT affects only the current connection session. It does not transfer from one connection to another in a connection pooling environment. Nor does the scope exceed stored procedure boundaries
ifobject_id('usp1')isnot nulldrop proc usp1gocreate proc usp1asbegin set rowcount 10select *from sys.objectsendgoexec usp1goselect *from sys.objectsgoifobject_id('usp1')isnot nulldrop proc usp1go
Does rolling a cluster create down time?
We have about 40 DB's blackening 40 web sites running on a single
SQL2k/Win2003 an A/P cluster. When I roll the cluster from one node to the
next I'm wondering exactly what sort of outage our clients would see. Since
the entire cluster rolls in under a minute would there just be a delay on
the web sites with data requests in response from the web/ODBC side or would
there be an ODBC failure. Also, with regards to active web sessions, would
clients mid transaction on the web pages need to restart their transaction
or would there be a short wait during the SQL roll and then they could
proceed with the transaction (or would they need to start all over again).
I guess I'm wondering if I need to add the 1 minute of roll time into our
annual downtime calculation if the clients see an application
failure/unavailability.
On the roll all client connections are lost and must be restarted. As for
what exactly happens, well that depends on the application. If the rows are
returned locally or if they are stored on the web server. And at what stage
in the process each client was in.
Bottom line, in most cases they either hit F5 (refresh) or never notice.
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
"Mike B." <Autobahn97@.hotmail.com> wrote in message
news:eS8jOB3XEHA.3596@.tk2msftngp13.phx.gbl...
> We have about 40 DB's blackening 40 web sites running on a single
> SQL2k/Win2003 an A/P cluster. When I roll the cluster from one node to
the
> next I'm wondering exactly what sort of outage our clients would see.
Since
> the entire cluster rolls in under a minute would there just be a delay on
> the web sites with data requests in response from the web/ODBC side or
would
> there be an ODBC failure. Also, with regards to active web sessions,
would
> clients mid transaction on the web pages need to restart their transaction
> or would there be a short wait during the SQL roll and then they could
> proceed with the transaction (or would they need to start all over again).
> I guess I'm wondering if I need to add the 1 minute of roll time into our
> annual downtime calculation if the clients see an application
> failure/unavailability.
>
|||What the clients see or how it affects their transactions depends solely on
how the client app was written. It must be cluster aware. By that I mean
it must be able to detect a connection failure and reconnect. It must also
be able to resubmit the current transaction as it will be rolled back on the
server side when the cluster comes back up.
Andrew J. Kelly SQL MVP
"Mike B." <Autobahn97@.hotmail.com> wrote in message
news:eS8jOB3XEHA.3596@.tk2msftngp13.phx.gbl...
> We have about 40 DB's blackening 40 web sites running on a single
> SQL2k/Win2003 an A/P cluster. When I roll the cluster from one node to
the
> next I'm wondering exactly what sort of outage our clients would see.
Since
> the entire cluster rolls in under a minute would there just be a delay on
> the web sites with data requests in response from the web/ODBC side or
would
> there be an ODBC failure. Also, with regards to active web sessions,
would
> clients mid transaction on the web pages need to restart their transaction
> or would there be a short wait during the SQL roll and then they could
> proceed with the transaction (or would they need to start all over again).
> I guess I'm wondering if I need to add the 1 minute of roll time into our
> annual downtime calculation if the clients see an application
> failure/unavailability.
>
|||A cluster host transition is exactly like a SQL server stop-start. All
transactions are rolled forward or back at startup. The server is down
during the transition. The client application will see a connection timeout
or a query timeout or a broken connection. Whether the application traps
that or displays that to the end-user depends on the skill of the programmer
who wrote it.
Short answer, a cluster transition is downtime.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Mike B." <Autobahn97@.hotmail.com> wrote in message
news:eS8jOB3XEHA.3596@.tk2msftngp13.phx.gbl...
> We have about 40 DB's blackening 40 web sites running on a single
> SQL2k/Win2003 an A/P cluster. When I roll the cluster from one node to
the
> next I'm wondering exactly what sort of outage our clients would see.
Since
> the entire cluster rolls in under a minute would there just be a delay on
> the web sites with data requests in response from the web/ODBC side or
would
> there be an ODBC failure. Also, with regards to active web sessions,
would
> clients mid transaction on the web pages need to restart their transaction
> or would there be a short wait during the SQL roll and then they could
> proceed with the transaction (or would they need to start all over again).
> I guess I'm wondering if I need to add the 1 minute of roll time into our
> annual downtime calculation if the clients see an application
> failure/unavailability.
>
|||During the failover process, any active connections are broken. For Web browser users, a simple refresh of the Web page should create a new database connection. In a more traditional client/server application, or
one that relies heavily on a middle tier, application designers may want to consider checking to see whether the connection exists, and if not, reconnect. Therefore, whatever the user was working on when the server
went down may not be completed, unless the transaction completes before the server goes down or the transaction is handled within the application.
For more information, see the Knowledge Base article "Q273673 - Virtual Server Client Connections Must be Controlled by Clients" at:
http://support.microsoft.com/?kbid=273673
Best Regards,
Uttam Parui
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way to do this is to visit the following websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx
SQL2k/Win2003 an A/P cluster. When I roll the cluster from one node to the
next I'm wondering exactly what sort of outage our clients would see. Since
the entire cluster rolls in under a minute would there just be a delay on
the web sites with data requests in response from the web/ODBC side or would
there be an ODBC failure. Also, with regards to active web sessions, would
clients mid transaction on the web pages need to restart their transaction
or would there be a short wait during the SQL roll and then they could
proceed with the transaction (or would they need to start all over again).
I guess I'm wondering if I need to add the 1 minute of roll time into our
annual downtime calculation if the clients see an application
failure/unavailability.
On the roll all client connections are lost and must be restarted. As for
what exactly happens, well that depends on the application. If the rows are
returned locally or if they are stored on the web server. And at what stage
in the process each client was in.
Bottom line, in most cases they either hit F5 (refresh) or never notice.
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
"Mike B." <Autobahn97@.hotmail.com> wrote in message
news:eS8jOB3XEHA.3596@.tk2msftngp13.phx.gbl...
> We have about 40 DB's blackening 40 web sites running on a single
> SQL2k/Win2003 an A/P cluster. When I roll the cluster from one node to
the
> next I'm wondering exactly what sort of outage our clients would see.
Since
> the entire cluster rolls in under a minute would there just be a delay on
> the web sites with data requests in response from the web/ODBC side or
would
> there be an ODBC failure. Also, with regards to active web sessions,
would
> clients mid transaction on the web pages need to restart their transaction
> or would there be a short wait during the SQL roll and then they could
> proceed with the transaction (or would they need to start all over again).
> I guess I'm wondering if I need to add the 1 minute of roll time into our
> annual downtime calculation if the clients see an application
> failure/unavailability.
>
|||What the clients see or how it affects their transactions depends solely on
how the client app was written. It must be cluster aware. By that I mean
it must be able to detect a connection failure and reconnect. It must also
be able to resubmit the current transaction as it will be rolled back on the
server side when the cluster comes back up.
Andrew J. Kelly SQL MVP
"Mike B." <Autobahn97@.hotmail.com> wrote in message
news:eS8jOB3XEHA.3596@.tk2msftngp13.phx.gbl...
> We have about 40 DB's blackening 40 web sites running on a single
> SQL2k/Win2003 an A/P cluster. When I roll the cluster from one node to
the
> next I'm wondering exactly what sort of outage our clients would see.
Since
> the entire cluster rolls in under a minute would there just be a delay on
> the web sites with data requests in response from the web/ODBC side or
would
> there be an ODBC failure. Also, with regards to active web sessions,
would
> clients mid transaction on the web pages need to restart their transaction
> or would there be a short wait during the SQL roll and then they could
> proceed with the transaction (or would they need to start all over again).
> I guess I'm wondering if I need to add the 1 minute of roll time into our
> annual downtime calculation if the clients see an application
> failure/unavailability.
>
|||A cluster host transition is exactly like a SQL server stop-start. All
transactions are rolled forward or back at startup. The server is down
during the transition. The client application will see a connection timeout
or a query timeout or a broken connection. Whether the application traps
that or displays that to the end-user depends on the skill of the programmer
who wrote it.
Short answer, a cluster transition is downtime.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Mike B." <Autobahn97@.hotmail.com> wrote in message
news:eS8jOB3XEHA.3596@.tk2msftngp13.phx.gbl...
> We have about 40 DB's blackening 40 web sites running on a single
> SQL2k/Win2003 an A/P cluster. When I roll the cluster from one node to
the
> next I'm wondering exactly what sort of outage our clients would see.
Since
> the entire cluster rolls in under a minute would there just be a delay on
> the web sites with data requests in response from the web/ODBC side or
would
> there be an ODBC failure. Also, with regards to active web sessions,
would
> clients mid transaction on the web pages need to restart their transaction
> or would there be a short wait during the SQL roll and then they could
> proceed with the transaction (or would they need to start all over again).
> I guess I'm wondering if I need to add the 1 minute of roll time into our
> annual downtime calculation if the clients see an application
> failure/unavailability.
>
|||During the failover process, any active connections are broken. For Web browser users, a simple refresh of the Web page should create a new database connection. In a more traditional client/server application, or
one that relies heavily on a middle tier, application designers may want to consider checking to see whether the connection exists, and if not, reconnect. Therefore, whatever the user was working on when the server
went down may not be completed, unless the transaction completes before the server goes down or the transaction is handled within the application.
For more information, see the Knowledge Base article "Q273673 - Virtual Server Client Connections Must be Controlled by Clients" at:
http://support.microsoft.com/?kbid=273673
Best Regards,
Uttam Parui
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way to do this is to visit the following websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx
Sunday, March 11, 2012
Does it take longer to restore the first time?
We moved a 2000 database to another platform by restoring the database. It took a lot longer than I expected. Would it take less time to restore it a second time to the same target database since the allocations are already there?
Thanks
I really doubt it because the files will still need to be extracted from the backup file and those files allocated on the disk. The OS will likely see the files as totally new files and allocate space for them again. If you are using 2005, you have the option for fast recovery, which allows the database to be online while the data is being restored.Does Interactive Sort works with Snapshots and Cached Reports?
I have created reports in RS 2005 using interactive sort on some columns.
When the reports are rendered real time, the sort works perfectly. When the
execution is changed to Snapshot, the sorting no longer works. When the
execution is set to Cached, the sort works the first time the report is
viewed, but not in subsequent viewings.
If this is a known bug, I'll give up on including this feature in my
reports. Can anyone confirm if this is supposed to work in RTM or SP1, or if
it's still on the bug list?
Thanks.I have this exact problem in designer mode with Visual 2005!
I have no clue what is going on - at times with RS2005 - was so stable with
2000 - now I am getting frustrated!
"Denise" wrote:
> I have created reports in RS 2005 using interactive sort on some columns.
> When the reports are rendered real time, the sort works perfectly. When the
> execution is changed to Snapshot, the sorting no longer works. When the
> execution is set to Cached, the sort works the first time the report is
> viewed, but not in subsequent viewings.
> If this is a known bug, I'll give up on including this feature in my
> reports. Can anyone confirm if this is supposed to work in RTM or SP1, or if
> it's still on the bug list?
> Thanks.
When the reports are rendered real time, the sort works perfectly. When the
execution is changed to Snapshot, the sorting no longer works. When the
execution is set to Cached, the sort works the first time the report is
viewed, but not in subsequent viewings.
If this is a known bug, I'll give up on including this feature in my
reports. Can anyone confirm if this is supposed to work in RTM or SP1, or if
it's still on the bug list?
Thanks.I have this exact problem in designer mode with Visual 2005!
I have no clue what is going on - at times with RS2005 - was so stable with
2000 - now I am getting frustrated!
"Denise" wrote:
> I have created reports in RS 2005 using interactive sort on some columns.
> When the reports are rendered real time, the sort works perfectly. When the
> execution is changed to Snapshot, the sorting no longer works. When the
> execution is set to Cached, the sort works the first time the report is
> viewed, but not in subsequent viewings.
> If this is a known bug, I'll give up on including this feature in my
> reports. Can anyone confirm if this is supposed to work in RTM or SP1, or if
> it's still on the bug list?
> Thanks.
Does Indexes actually make queries fast?
I had a query most of the time got "timed out". Add some indexes things did
not get any defferent. After remove the indexes it work fine (rarely got
timed out) I mean the query did not get timed out as it was before. Does
Indexes actually make queries fast?
raj
Raj,
Don't take this the wrong way but it's pretty obvious that you know little
about a modern relational database. That is fine in that no one is born
with this knowledge. But I really do suggest before you start to developed a
database and or db application that you do a little reading on the subject.
Or possibly take a class on SQL Server. It will make your life much easier
and will save you lots of time and frustration in the long run. To answer
your question yes they do make queries faster when done correctly. Without
indexes a modern database would be mostly useless.
Andrew J. Kelly SQL MVP
"raj" <raj@.discussions.microsoft.com> wrote in message
news:B80E0BDF-72D1-4DA2-BEBB-1E23E39B6C25@.microsoft.com...
>I had a query most of the time got "timed out". Add some indexes things did
> not get any defferent. After remove the indexes it work fine (rarely got
> timed out) I mean the query did not get timed out as it was before. Does
> Indexes actually make queries fast?
> raj
>
|||"raj" <raj@.discussions.microsoft.com> wrote in message
news:B80E0BDF-72D1-4DA2-BEBB-1E23E39B6C25@.microsoft.com...
> I had a query most of the time got "timed out". Add some indexes things
did
> not get any defferent. After remove the indexes it work fine (rarely got
> timed out) I mean the query did not get timed out as it was before. Does
> Indexes actually make queries fast?
Done right.. yes.
Done wrong, no.
> raj
>
|||raj,
If they are implemented badly they will make your server SLOWER. I agree
with Andrew.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
raj wrote:
> I had a query most of the time got "timed out". Add some indexes things did
> not get any defferent. After remove the indexes it work fine (rarely got
> timed out) I mean the query did not get timed out as it was before. Does
> Indexes actually make queries fast?
> raj
>
not get any defferent. After remove the indexes it work fine (rarely got
timed out) I mean the query did not get timed out as it was before. Does
Indexes actually make queries fast?
raj
Raj,
Don't take this the wrong way but it's pretty obvious that you know little
about a modern relational database. That is fine in that no one is born
with this knowledge. But I really do suggest before you start to developed a
database and or db application that you do a little reading on the subject.
Or possibly take a class on SQL Server. It will make your life much easier
and will save you lots of time and frustration in the long run. To answer
your question yes they do make queries faster when done correctly. Without
indexes a modern database would be mostly useless.
Andrew J. Kelly SQL MVP
"raj" <raj@.discussions.microsoft.com> wrote in message
news:B80E0BDF-72D1-4DA2-BEBB-1E23E39B6C25@.microsoft.com...
>I had a query most of the time got "timed out". Add some indexes things did
> not get any defferent. After remove the indexes it work fine (rarely got
> timed out) I mean the query did not get timed out as it was before. Does
> Indexes actually make queries fast?
> raj
>
|||"raj" <raj@.discussions.microsoft.com> wrote in message
news:B80E0BDF-72D1-4DA2-BEBB-1E23E39B6C25@.microsoft.com...
> I had a query most of the time got "timed out". Add some indexes things
did
> not get any defferent. After remove the indexes it work fine (rarely got
> timed out) I mean the query did not get timed out as it was before. Does
> Indexes actually make queries fast?
Done right.. yes.
Done wrong, no.
> raj
>
|||raj,
If they are implemented badly they will make your server SLOWER. I agree
with Andrew.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
raj wrote:
> I had a query most of the time got "timed out". Add some indexes things did
> not get any defferent. After remove the indexes it work fine (rarely got
> timed out) I mean the query did not get timed out as it was before. Does
> Indexes actually make queries fast?
> raj
>
Does Indexes actually make queries fast?
I had a query most of the time got "timed out". Add some indexes things did
not get any defferent. After remove the indexes it work fine (rarely got
timed out) I mean the query did not get timed out as it was before. Does
Indexes actually make queries fast?
rajRaj,
Don't take this the wrong way but it's pretty obvious that you know little
about a modern relational database. That is fine in that no one is born
with this knowledge. But I really do suggest before you start to developed a
database and or db application that you do a little reading on the subject.
Or possibly take a class on SQL Server. It will make your life much easier
and will save you lots of time and frustration in the long run. To answer
your question yes they do make queries faster when done correctly. Without
indexes a modern database would be mostly useless.
Andrew J. Kelly SQL MVP
"raj" <raj@.discussions.microsoft.com> wrote in message
news:B80E0BDF-72D1-4DA2-BEBB-1E23E39B6C25@.microsoft.com...
>I had a query most of the time got "timed out". Add some indexes things did
> not get any defferent. After remove the indexes it work fine (rarely got
> timed out) I mean the query did not get timed out as it was before. Does
> Indexes actually make queries fast?
> raj
>|||"raj" <raj@.discussions.microsoft.com> wrote in message
news:B80E0BDF-72D1-4DA2-BEBB-1E23E39B6C25@.microsoft.com...
> I had a query most of the time got "timed out". Add some indexes things
did
> not get any defferent. After remove the indexes it work fine (rarely got
> timed out) I mean the query did not get timed out as it was before. Does
> Indexes actually make queries fast?
Done right.. yes.
Done wrong, no.
> raj
>|||raj,
If they are implemented badly they will make your server SLOWER. I agree
with Andrew.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
raj wrote:
> I had a query most of the time got "timed out". Add some indexes things di
d
> not get any defferent. After remove the indexes it work fine (rarely got
> timed out) I mean the query did not get timed out as it was before. Does
> Indexes actually make queries fast?
> raj
>
not get any defferent. After remove the indexes it work fine (rarely got
timed out) I mean the query did not get timed out as it was before. Does
Indexes actually make queries fast?
rajRaj,
Don't take this the wrong way but it's pretty obvious that you know little
about a modern relational database. That is fine in that no one is born
with this knowledge. But I really do suggest before you start to developed a
database and or db application that you do a little reading on the subject.
Or possibly take a class on SQL Server. It will make your life much easier
and will save you lots of time and frustration in the long run. To answer
your question yes they do make queries faster when done correctly. Without
indexes a modern database would be mostly useless.
Andrew J. Kelly SQL MVP
"raj" <raj@.discussions.microsoft.com> wrote in message
news:B80E0BDF-72D1-4DA2-BEBB-1E23E39B6C25@.microsoft.com...
>I had a query most of the time got "timed out". Add some indexes things did
> not get any defferent. After remove the indexes it work fine (rarely got
> timed out) I mean the query did not get timed out as it was before. Does
> Indexes actually make queries fast?
> raj
>|||"raj" <raj@.discussions.microsoft.com> wrote in message
news:B80E0BDF-72D1-4DA2-BEBB-1E23E39B6C25@.microsoft.com...
> I had a query most of the time got "timed out". Add some indexes things
did
> not get any defferent. After remove the indexes it work fine (rarely got
> timed out) I mean the query did not get timed out as it was before. Does
> Indexes actually make queries fast?
Done right.. yes.
Done wrong, no.
> raj
>|||raj,
If they are implemented badly they will make your server SLOWER. I agree
with Andrew.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
raj wrote:
> I had a query most of the time got "timed out". Add some indexes things di
d
> not get any defferent. After remove the indexes it work fine (rarely got
> timed out) I mean the query did not get timed out as it was before. Does
> Indexes actually make queries fast?
> raj
>
Does Indexes actually make queries fast?
I had a query most of the time got "timed out". Add some indexes things did
not get any defferent. After remove the indexes it work fine (rarely got
timed out) I mean the query did not get timed out as it was before. Does
Indexes actually make queries fast?
rajRaj,
Don't take this the wrong way but it's pretty obvious that you know little
about a modern relational database. That is fine in that no one is born
with this knowledge. But I really do suggest before you start to developed a
database and or db application that you do a little reading on the subject.
Or possibly take a class on SQL Server. It will make your life much easier
and will save you lots of time and frustration in the long run. To answer
your question yes they do make queries faster when done correctly. Without
indexes a modern database would be mostly useless.
--
Andrew J. Kelly SQL MVP
"raj" <raj@.discussions.microsoft.com> wrote in message
news:B80E0BDF-72D1-4DA2-BEBB-1E23E39B6C25@.microsoft.com...
>I had a query most of the time got "timed out". Add some indexes things did
> not get any defferent. After remove the indexes it work fine (rarely got
> timed out) I mean the query did not get timed out as it was before. Does
> Indexes actually make queries fast?
> raj
>|||"raj" <raj@.discussions.microsoft.com> wrote in message
news:B80E0BDF-72D1-4DA2-BEBB-1E23E39B6C25@.microsoft.com...
> I had a query most of the time got "timed out". Add some indexes things
did
> not get any defferent. After remove the indexes it work fine (rarely got
> timed out) I mean the query did not get timed out as it was before. Does
> Indexes actually make queries fast?
Done right.. yes.
Done wrong, no.
> raj
>|||raj,
If they are implemented badly they will make your server SLOWER. I agree
with Andrew.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
raj wrote:
> I had a query most of the time got "timed out". Add some indexes things did
> not get any defferent. After remove the indexes it work fine (rarely got
> timed out) I mean the query did not get timed out as it was before. Does
> Indexes actually make queries fast?
> raj
>
not get any defferent. After remove the indexes it work fine (rarely got
timed out) I mean the query did not get timed out as it was before. Does
Indexes actually make queries fast?
rajRaj,
Don't take this the wrong way but it's pretty obvious that you know little
about a modern relational database. That is fine in that no one is born
with this knowledge. But I really do suggest before you start to developed a
database and or db application that you do a little reading on the subject.
Or possibly take a class on SQL Server. It will make your life much easier
and will save you lots of time and frustration in the long run. To answer
your question yes they do make queries faster when done correctly. Without
indexes a modern database would be mostly useless.
--
Andrew J. Kelly SQL MVP
"raj" <raj@.discussions.microsoft.com> wrote in message
news:B80E0BDF-72D1-4DA2-BEBB-1E23E39B6C25@.microsoft.com...
>I had a query most of the time got "timed out". Add some indexes things did
> not get any defferent. After remove the indexes it work fine (rarely got
> timed out) I mean the query did not get timed out as it was before. Does
> Indexes actually make queries fast?
> raj
>|||"raj" <raj@.discussions.microsoft.com> wrote in message
news:B80E0BDF-72D1-4DA2-BEBB-1E23E39B6C25@.microsoft.com...
> I had a query most of the time got "timed out". Add some indexes things
did
> not get any defferent. After remove the indexes it work fine (rarely got
> timed out) I mean the query did not get timed out as it was before. Does
> Indexes actually make queries fast?
Done right.. yes.
Done wrong, no.
> raj
>|||raj,
If they are implemented badly they will make your server SLOWER. I agree
with Andrew.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
raj wrote:
> I had a query most of the time got "timed out". Add some indexes things did
> not get any defferent. After remove the indexes it work fine (rarely got
> timed out) I mean the query did not get timed out as it was before. Does
> Indexes actually make queries fast?
> raj
>
Friday, March 9, 2012
Does DBREINDEX run longer on tables with lower scan density
I am looking into a way to generate and execute DBCC DBREINDEX statements
during scheduled down time. For a particular table - I need to be able to
estimate how long it will take to REINDEX a particular table.
Let say for example I have a table that had 25000 extents and
1) the ScanDensity was 50.
2) the ScanDensity was 15.
Would it take SIGNIFICANTLY longer to REINDEX the table if it had a
ScanDensity of 15 (as opposed to 50)?
Thanks in advance
Tom-- TJTODD wrote: --
> Would it take SIGNIFICANTLY longer to REINDEX the table if it had a
> ScanDensity of 15 (as opposed to 50)?
--
Hi Tom,
I have not found any official documentation on this area. The only way to find out is to do your own empirical benchmarks.
Hope this helps,
-Eric Cárdenas
SQL Server support|||Tom,
The only thing I can think of is that it will obviously take longer time for SQL Server to scan the
source data if the scan density is low. I'd think that in the whole, this would count as marginal,
though.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
news:OzZlndSuDHA.2304@.tk2msftngp13.phx.gbl...
> I am looking into a way to generate and execute DBCC DBREINDEX statements
> during scheduled down time. For a particular table - I need to be able to
> estimate how long it will take to REINDEX a particular table.
> Let say for example I have a table that had 25000 extents and
> 1) the ScanDensity was 50.
> 2) the ScanDensity was 15.
> Would it take SIGNIFICANTLY longer to REINDEX the table if it had a
> ScanDensity of 15 (as opposed to 50)?
> Thanks in advance
> Tom
>
during scheduled down time. For a particular table - I need to be able to
estimate how long it will take to REINDEX a particular table.
Let say for example I have a table that had 25000 extents and
1) the ScanDensity was 50.
2) the ScanDensity was 15.
Would it take SIGNIFICANTLY longer to REINDEX the table if it had a
ScanDensity of 15 (as opposed to 50)?
Thanks in advance
Tom-- TJTODD wrote: --
> Would it take SIGNIFICANTLY longer to REINDEX the table if it had a
> ScanDensity of 15 (as opposed to 50)?
--
Hi Tom,
I have not found any official documentation on this area. The only way to find out is to do your own empirical benchmarks.
Hope this helps,
-Eric Cárdenas
SQL Server support|||Tom,
The only thing I can think of is that it will obviously take longer time for SQL Server to scan the
source data if the scan density is low. I'd think that in the whole, this would count as marginal,
though.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
news:OzZlndSuDHA.2304@.tk2msftngp13.phx.gbl...
> I am looking into a way to generate and execute DBCC DBREINDEX statements
> during scheduled down time. For a particular table - I need to be able to
> estimate how long it will take to REINDEX a particular table.
> Let say for example I have a table that had 25000 extents and
> 1) the ScanDensity was 50.
> 2) the ScanDensity was 15.
> Would it take SIGNIFICANTLY longer to REINDEX the table if it had a
> ScanDensity of 15 (as opposed to 50)?
> Thanks in advance
> Tom
>
Friday, February 24, 2012
Does a Job execute MUCH faster??
Hi,
Previosuly I was executing 2 DTS packages one afte the other manually and together they took a CONSIDERABLE time. The 1st one was pulling data from the OLPT, doing transformations and populating the tables in my Datamart and the 2nd one was doing a FULL process of all the dimensions and cubes.
However I tried scheduling the DTSs as jobs and havethen merged the 2 resulting jobs as a SINGLe job having 2 sequential steps. To my surprise the resulting job takes less than half the time (actually even lesser) as compared with my original approach i.e. running the DTSs.
Am i getting over excited here or is this natural? I assume that if this is correct then jobs much be some sort of "compiled" version as compared to DTS and maybe that's why I have this terrific improvement in terms of execution times.
I'll appreciate comments. ThanksIt's actually a little simpler than that. The same exact set of processes are involved in running your package(-s) interactively or as a scheduled job. The difference lies in the fact that, while being scheduled, the package runs on the server, and any and all output streams are within the "server reach" (in other words nothing leaves the box), running the same package interactively causes every output flag (such as the number of rows processed so far) to be sent to the client.|||Thanks for the reply :)
Previosuly I was executing 2 DTS packages one afte the other manually and together they took a CONSIDERABLE time. The 1st one was pulling data from the OLPT, doing transformations and populating the tables in my Datamart and the 2nd one was doing a FULL process of all the dimensions and cubes.
However I tried scheduling the DTSs as jobs and havethen merged the 2 resulting jobs as a SINGLe job having 2 sequential steps. To my surprise the resulting job takes less than half the time (actually even lesser) as compared with my original approach i.e. running the DTSs.
Am i getting over excited here or is this natural? I assume that if this is correct then jobs much be some sort of "compiled" version as compared to DTS and maybe that's why I have this terrific improvement in terms of execution times.
I'll appreciate comments. ThanksIt's actually a little simpler than that. The same exact set of processes are involved in running your package(-s) interactively or as a scheduled job. The difference lies in the fact that, while being scheduled, the package runs on the server, and any and all output streams are within the "server reach" (in other words nothing leaves the box), running the same package interactively causes every output flag (such as the number of rows processed so far) to be sent to the client.|||Thanks for the reply :)
Does a checkpoint file record a package's state
The state of a package at any one point in time is determined by the values in all of its user variables as these are the only properties in the package that can be changed and persisted throughout the runtime of the package.
Is the package's state stored in a checkpoint file in the event that the package fails? In other words are the user variable values stored in the checkpoint file?
Thanks
Jamie
Can task A update an enivornment variable i.e. persist the state outside the package.
Out of interest viw does package B need to update a variable in package A.
|||SimonSa wrote:
Can task A update an enivornment variable i.e. persist the state outside the package.
Not in this scenario!
Out of interest viw does package B need to update a variable in package A.
[I presume that word was "why" :)]
That's a question that needs to be answered at length - preferably over a few beers!
-Jamie
|||Jamie,
Can you ellaborate on what this variable (PkgA) represents (I.E., some sort of count, threshold, clock/time, etc...)?
We've had scenerios where a variable needed to be updated and couldn't be done using a GLOBAL_ENV_VAR or some other global, it needed to be updated per client(job), and client(job) specific function. So what we did is created and modified text-based files to update the variables values.
Say for instance, you had to update variableA (which was a boolean true/false), you could create/modify a file specifc to that instance/job run, and change the "variableA=" to true of false. Then you could always refer back to that specific file to lookup the values - like creating your own checkpoint files.
|||
Jason,
The particular one one in question was a variable called PackageStack which maintains a comma seperated list of packages that have been called, in the order that they have been called.
Each package has an OnPreExecute event that "pushes" the name of the package into the PackageStack variable and an OnPostExecute event that "pops" it off.
-Jamie
|||
Similarly, you could log this using a log provider, however, you will run into the same problem I am facing - with that logging bug and only logging the immediately inherited pkg!!!
Maybe, whom ever answers this post can solve both our problems...
|||Yes, the values of the variables are stored in the checkpoint file.
K
|||I disagree, I believe this is only true for simple variables. I have stored a recordset in a variable and this wasn't stored in the checkpoint file.
Either object types aren't supported or there is something about what is saved.
What happens if a variable is changed in a loop. What is stored in the checkpoint, the variable value at the start of the loop or the value at the point of failure.
|||Correct, object types are not stored.
ForEach loops start at the beginning of the collection.
For loops continue where they left off.
They all key off of the variables.
K
|||
Correct, object types are not stored.
Why not?
Will this change?
-Jamie
|||
I've just come across a differrent "problem" with this.
I have 2 packages pkgA and pkgB. pkgA calls pkgB
pkgB has 2 tasks taskA and taskB. taskA is a script task that updates a variable (varA) in pkgA. taskB is a data-flow that is failing.
pkgB uses checkpoints
Now, I can correct the error that causes taskB to fail but the problem I have is that when I rerun it using the checkpoint file taskA will not execute and therefore varA will have the wrong value in it.
There isn't a way around this problem currently but is there a way that this could be catered for in the future using checkpoint files? i.e. The checkpoint file for pkgB would have to store the value of varA even though varA is in pkgA.
-Jamie
Does "Current time member" on the KPIs designer do anything?
The "Current time member" on the KPIs designer is poorly documented and that's an understatement. Does it actually do anything or is it unfinished code?
Has anyone else come to the conclusion that KPIs in SSAS were never "finished"? We've found numerous holes in the way KPIs have been implemented.
I have moved some KPI:s, for a client, from ProClaritys KPI Builder, to SSAS2005 KPI:s.
So far this have worked fine and we are able to show KPI:s in other tools than ProClarity.
As an example the coming Excel 2007 release supports SSAS2005 KPI:s.
These KPI:s can also be imported in to Business Scorecard manager/server. If you need additional functionality I recommend the Business Scorecard Server instead. Most important is that you can group KPI:s and assig weights to each KPI and or parent KPI.
I can agree that view tab in BI-Dev Studio does not always work. Im not sure about the time current member setting but you can use named sets or a default member to point to a current time member.
Kind regards
Thomas Ivarsson
|||Current Time Member is not used by the KPI browser included in BI Dev Studio. Current Time Member is intended as a way to specify a default time member that browsers can show, generally based on the most recent available and complete data. However, when a browser allows the user to slice on time for multiple KPIs, the Current Time Member is less useful since it may be different for each KPI.
KPIs are fully functional on the Analysis Services. There are some known issues with browsing KPIs (specifically around filtering) in the KPI Browser UI, but this is intended only as a sample browser to help in development and it should not prevent you from using this feature. We will be looking into improving the KPI Browser in the next major release.
|||Matt, thank you for your response. That's exactly what I needed to know.
How could the company I'm with get more involved in defining enhancements for Analysis Services? You mentioned the KPI Browser UI is "intended only as a sample browser" and that KPIs are fully functional; however, the more we dig into it the more we lean toward implementing our own architecture for KPIs because what is in SSAS falls short on many things. There should be a way to specify which date dimensions are valid for each KPI and which are not. Some KPIs may be restricted to the fiscal calendar for instance. Others will only be valid for certain date dimensions due to percentages they calculate. KPIs can't be formatted as percents without the status and goal calulations blowing up.
Also, and this is a MAJOR problem with SSAS in general, there is no support for multi-currency considerations. Yes, you can format a raw number as currency using the locale of the server, but there is no support for dealing with multiple amounts all in different currencies and formatting each of them with a currency code. We can't drop the Format as currency call and format it within our application based on another query to retrieve the currency type because then we would not be able to differeniate between percents and amounts. I'm having to explicitly look for $ and ($ in order to see if it's an amount and, if not, format it as a percent.
The multi-currency issue is a much larger problem outside the needed KPI improvements though. I just want to say though that an "amount" by itself is completely meaningless in a large financial application. There should be a currency data type down to the DB level that ties an amount and currency code together at all times. I'm never going to get that one though!
|||
Hi Terry,
The Associated Measure Group property is intended as the mechansim for specifying valid dimensions. I would be interested to hear if you've tried this and still found it to be inadequate.
Please explain this comment " KPIs can't be formatted as percents without the status and goal calulations blowing up" as I'm not aware of any issues here.
We actually do have support for dynamic (or static) formatting of currency values via the Language property (http://sqljunkies.com/WebLog/mosha/archive/2005/10/13/mdx_format_currency.aspx)
-rob
|||Thanks for your reply.
What I was referring to with KPIs is that there is no way (as far as I know) to say that KPI 1 is only valid for the Calendar Hierarchy, KPI 2 is only valid for the Fiscal Hierarchy, KPI 3 is only valid for months and not years or quarters, etc. If you have multiple KPIs in a dashboard and want them filtered based on a time dropdown containing values like "Current Calendar Quarter", "Fiscal YTD", etc. then this becomes an issue.
KPI values can't be formatted as percents for display purposes otherwise the status and goal calculations will throw an error. You have to format them as "Standard" instead of "Percent". After we found this we assumed that is why Adventure Works is also not formatting some of it's KPIs as percents which should be formatted as such.
Thank you for referring me to Mosha's article on formatting currencies in MDX. There is a lot more functionality available in SSAS regarding this than I knew about. I have multi-currency experience outside of the BI world, but I'm still learning how to do it in a dimensional-modeling environment. I would appreciate any other article references on designing a currency dimension. One thought I had on Mosha's article is that if your application is running in Europe and displaying multiple currencies on the same screen (and let's assume they're returned from the same MDX query) then each currency will need a comma as a decimal separator instead of a period. However, you don't want amounts in U.S. dollars to be formatted with a Euro symbol. Also, on many occassions you would want the currency symbol displayed on either the left or right-hand side of the amount (depending on what is common for that currency, dollars versus yen for example) and then the three-letter currency code to the right of the amount (to separated U.S. dollars from Canadian dollars for example). This level of multi-currency support always had to be special-coded for in the pre-.NET/SSAS '05 world anyway; however, I still claim that it would be dramatically easier to handle if there were a Currency data type across the whole .NET /SQL Server platform composed of an amount and currency code in one composite object. I'll keep dreaming... :)
Terry
Subscribe to:
Posts (Atom)