lastnonempty

Hello,

How can i implement lastnonempty for a dimension that is not a time dimension?

Example: I have a Client, Type and Time dimension and a facttable FactClient; and i want to apply the lastnonempty measure to the Client dimension not to the Time dimension. How can i do that?

If you need more information, please ask.

Thank you,

Vulcano

Sorry, i forgot the example:

The fact table:

Client Type Date Value

John Type1 1 Aug 2006 5

John Type2 5 Aug 2006 3

John Type2 25 Aug 2006 7

Mary Type1 3 Aug 2006 1

Mary Type2 7 Aug 2006 9

Mary Type1 15 Aug 2006 15

Eric Type1 20 Aug 2006 3

I want to get at the end of the month:

Month Aug 2006 John Type2 10

Mary Type1 15

Eric Type 1 3

Thank you,

Vulcano

|||

Given that the last entry for John is:

John Type2 25 Aug 2006 7

why is the month-end total for John 10, rather than 7?

Month Aug 2006 John Type2 10|||

Thanks, for your reply Deepak

The entry for John at the end of month is 10, because the last 2 entries for John have TYPE2 (3+7=10).

I need to take the last entries for a client that have the same TYPE.

Thank you

sql

last update date for a procedure

see SqlServer Enterprise Manager: the list of stored procedures has just
name, owner, type and create date as columns.
I'd like to have also the date of the last update for a stored procedure.
This can help me a lot.
Is this possible? Is this date recorder in the system tables?
Thanks.Hi,
SQL Server will not store the modified date and time of the procedure.So if
you use ALter Procedure you cant see the modified date and time.
Thanks
Hari
SQL Server MVP
"Francesco" <Francesco@.discussions.microsoft.com> wrote in message
news:BE716295-7938-4A53-BA43-7A74DF780488@.microsoft.com...
> see SqlServer Enterprise Manager: the list of stored procedures has just
> name, owner, type and create date as columns.
> I'd like to have also the date of the last update for a stored procedure.
> This can help me a lot.
> Is this possible? Is this date recorder in the system tables?
> Thanks.
>|||Hi
No, SQL Server does not track such info.
Are you only a person in the company to make changes in stored procedures
and other objects?
"Francesco" <Francesco@.discussions.microsoft.com> wrote in message
news:BE716295-7938-4A53-BA43-7A74DF780488@.microsoft.com...
> see SqlServer Enterprise Manager: the list of stored procedures has just
> name, owner, type and create date as columns.
> I'd like to have also the date of the last update for a stored procedure.
> This can help me a lot.
> Is this possible? Is this date recorder in the system tables?
> Thanks.
>|||> see SqlServer Enterprise Manager: the list of stored procedures has just
> name, owner, type and create date as columns.
> I'd like to have also the date of the last update for a stored procedure.
> This can help me a lot.
> Is this possible? Is this date recorder in the system tables?
No. This is what source control is for.|||As Aaron mentions – use a source control, that’s what they are there for
.
This is simply said rather than done however and has been the subject of my
concern for many years now. There is a document
(http://www.innovartis.co.uk/pdf/ In...Mgt
.pdf)
which outlines the approach I’ve taken with unparalleled success. I’d lo
ve an
open debate on this subject as I still see no solution other than the one I
offer which gives an automated approach to database change management that
will save your company a lot of money, which I believe (IMHO) - is our
primary function as IT personnel.
When you have your source code in your source control for any thing - be it
applications, dynamic link libraries, user controls or databases there are
some necessary processes that need to be followed.
1. The source code needs to added to your source control.
2. Developers check out the source code to make changes. With application
code integrated development environments are readily available that will
check out the relevant files however these environments for SQL Server are
not so abundant and suffer many quirks which is why – and this is just my
opinion – I still find Query Analyzer to be the best development environme
nt
meaning I still have to check out the files from the source control via the
source controls interface.
3. Developers add new source code to the source control when new
functionality is required. IE: when dealing with applications this may be ne
w
forms, DLL’s, classes etc and when dealing with databases this may be new
tables or stored procedures. Different objects may be open to different
groups to develop as many organizations require that only a certain
individuals have the necessary abilities to make changes to tables for
example. These rules can be part of the source control allowing for a single
coherent view of functionality across the enterprise.
4. The code needs to be versioned at regular intervals to create snapshots
that facilitate deployment and code audits. All source controls have this
functionality IE: Visual Source Safe uses labels for this functionality.
5. You need a process that can deliver all the changes made to the code base
to the relevant areas. When you are dealing with applications this means
compiling the source code and deploying the compiled code to the required
machines. When talking databases this means compiling the source code and
deploying the changes to the required environments. I think this is where
people start to question this approach. Compile a database? Building a
database from the source code, compiles the source code. This step ensures
any changes made that break existing code are identified. Too often I see
broken code due to deployed code changes that ignore this fact. With
application code, compilers throw errors when dependencies are invalid. Now
that the source code has been validated a comparison can be made to determin
e
what needs to be changed on the target environment and then these changes ar
e
made. For this step I always implement an automated process on a machine
where a copy of the end environment can be held to test the deployment. The
process records a SQL delta file which can then be used (if the process has
been successful) on those environments requiring the changes. In the event a
n
error occurs, the error can be identified, the person responsible can be
identified, the time the error was made can be identified creating an ideal
environment for the code to be fixed quickly and the process repeated to
verify the change/fix.
This is what I like to call database change management and DB Ghost
(http://www.dbghost.com) is the result of the many years of thinking to
facilitate this process.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"Francesco" wrote:

> see SqlServer Enterprise Manager: the list of stored procedures has just
> name, owner, type and create date as columns.
> I'd like to have also the date of the last update for a stored procedure.
> This can help me a lot.
> Is this possible? Is this date recorder in the system tables?
> Thanks.
>|||As Aaron mentions – use a source control, that’s what they are there for
.
This is simply said rather than done however and has been the subject of my
concern for many years now. There is a document
(http://www.innovartis.co.uk/pdf/ In...Mgt
.pdf)
which outlines the approach I’ve taken with unparalleled success. I’d lo
ve an
open debate on this subject as I still see no solution other than the one I
offer which gives an automated approach to database change management that
will save your company a lot of money, which I believe (IMHO) - is our
primary function as IT personnel.
When you have your source code in your source control for any thing - be it
applications, dynamic link libraries, user controls or databases there are
some necessary processes that need to be followed.
1. The source code needs to added to your source control.
2. Developers check out the source code to make changes. With application
code integrated development environments are readily available that will
check out the relevant files however these environments for SQL Server are
not so abundant and suffer many quirks which is why – and this is just my
opinion – I still find Query Analyzer to be the best development environme
nt
meaning I still have to check out the files from the source control via the
source controls interface.
3. Developers add new source code to the source control when new
functionality is required. IE: when dealing with applications this may be ne
w
forms, DLL’s, classes etc and when dealing with databases this may be new
tables or stored procedures. Different objects may be open to different
groups to develop as many organizations require that only a certain
individuals have the necessary abilities to make changes to tables for
example. These rules can be part of the source control allowing for a single
coherent view of functionality across the enterprise.
4. The code needs to be versioned at regular intervals to create snapshots
that facilitate deployment and code audits. All source controls have this
functionality IE: Visual Source Safe uses labels for this functionality.
5. You need a process that can deliver all the changes made to the code base
to the relevant areas. When you are dealing with applications this means
compiling the source code and deploying the compiled code to the required
machines. When talking databases this means compiling the source code and
deploying the changes to the required environments. I think this is where
people start to question this approach. Compile a database? Building a
database from the source code, compiles the source code. This step ensures
any changes made that break existing code are identified. Too often I see
broken code due to deployed code changes that ignore this fact. With
application code, compilers throw errors when dependencies are invalid. Now
that the source code has been validated a comparison can be made to determin
e
what needs to be changed on the target environment and then these changes ar
e
made. For this step I always implement an automated process on a machine
where a copy of the end environment can be held to test the deployment. The
process records a SQL delta file which can then be used (if the process has
been successful) on those environments requiring the changes. In the event a
n
error occurs, the error can be identified, the person responsible can be
identified, the time the error was made can be identified creating an ideal
environment for the code to be fixed quickly and the process repeated to
verify the change/fix.
This is what I like to call database change management and DB Ghost
(http://www.dbghost.com) is the result of the many years of thinking to
facilitate this process.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"Francesco" wrote:

> see SqlServer Enterprise Manager: the list of stored procedures has just
> name, owner, type and create date as columns.
> I'd like to have also the date of the last update for a stored procedure.
> This can help me a lot.
> Is this possible? Is this date recorder in the system tables?
> Thanks.
>|||You can see this in information_schema.routines (column name is last_altered
)
"Francesco" wrote:

> see SqlServer Enterprise Manager: the list of stored procedures has just
> name, owner, type and create date as columns.
> I'd like to have also the date of the last update for a stored procedure.
> This can help me a lot.
> Is this possible? Is this date recorder in the system tables?
> Thanks.
>|||try testing this column, you should see that it doesn't work...
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"harvinder" wrote:
[vbcol=seagreen]
> You can see this in information_schema.routines (column name is last_alter
ed)
> "Francesco" wrote:
>|||> You can see this in information_schema.routines (column name is
> last_altered)
NO, the data is not accurate unless you've never modified the stored
procedure. The datetime value stored here is always the same as created
date, and is never updated on ALTER... this will be more reliable using the
catalog views in SQL Server 2005, but for now the data is not stored.

last update date for a procedure

see SqlServer Enterprise Manager: the list of stored procedures has just
name, owner, type and create date as columns.
I'd like to have also the date of the last update for a stored procedure.
This can help me a lot.
Is this possible? Is this date recorder in the system tables?
Thanks.Hi,
SQL Server will not store the modified date and time of the procedure.So if
you use ALter Procedure you cant see the modified date and time.
Thanks
Hari
SQL Server MVP
"Francesco" <Francesco@.discussions.microsoft.com> wrote in message
news:BE716295-7938-4A53-BA43-7A74DF780488@.microsoft.com...
> see SqlServer Enterprise Manager: the list of stored procedures has just
> name, owner, type and create date as columns.
> I'd like to have also the date of the last update for a stored procedure.
> This can help me a lot.
> Is this possible? Is this date recorder in the system tables?
> Thanks.
>|||Hi
No, SQL Server does not track such info.
Are you only a person in the company to make changes in stored procedures
and other objects?
"Francesco" <Francesco@.discussions.microsoft.com> wrote in message
news:BE716295-7938-4A53-BA43-7A74DF780488@.microsoft.com...
> see SqlServer Enterprise Manager: the list of stored procedures has just
> name, owner, type and create date as columns.
> I'd like to have also the date of the last update for a stored procedure.
> This can help me a lot.
> Is this possible? Is this date recorder in the system tables?
> Thanks.
>|||> see SqlServer Enterprise Manager: the list of stored procedures has just
> name, owner, type and create date as columns.
> I'd like to have also the date of the last update for a stored procedure.
> This can help me a lot.
> Is this possible? Is this date recorder in the system tables?
No. This is what source control is for.|||As Aaron mentions â' use a source control, thatâ's what they are there for.
This is simply said rather than done however and has been the subject of my
concern for many years now. There is a document
(http://www.innovartis.co.uk/pdf/Innovartis_An_Automated_Approach_To_Do_Change_Mgt.pdf)
which outlines the approach Iâ've taken with unparalleled success. Iâ'd love an
open debate on this subject as I still see no solution other than the one I
offer which gives an automated approach to database change management that
will save your company a lot of money, which I believe (IMHO) - is our
primary function as IT personnel.
When you have your source code in your source control for any thing - be it
applications, dynamic link libraries, user controls or databases there are
some necessary processes that need to be followed.
1. The source code needs to added to your source control.
2. Developers check out the source code to make changes. With application
code integrated development environments are readily available that will
check out the relevant files however these environments for SQL Server are
not so abundant and suffer many quirks which is why â' and this is just my
opinion â' I still find Query Analyzer to be the best development environment
meaning I still have to check out the files from the source control via the
source controls interface.
3. Developers add new source code to the source control when new
functionality is required. IE: when dealing with applications this may be new
forms, DLLâ's, classes etc and when dealing with databases this may be new
tables or stored procedures. Different objects may be open to different
groups to develop as many organizations require that only a certain
individuals have the necessary abilities to make changes to tables for
example. These rules can be part of the source control allowing for a single
coherent view of functionality across the enterprise.
4. The code needs to be versioned at regular intervals to create snapshots
that facilitate deployment and code audits. All source controls have this
functionality IE: Visual Source Safe uses labels for this functionality.
5. You need a process that can deliver all the changes made to the code base
to the relevant areas. When you are dealing with applications this means
compiling the source code and deploying the compiled code to the required
machines. When talking databases this means compiling the source code and
deploying the changes to the required environments. I think this is where
people start to question this approach. Compile a database? Building a
database from the source code, compiles the source code. This step ensures
any changes made that break existing code are identified. Too often I see
broken code due to deployed code changes that ignore this fact. With
application code, compilers throw errors when dependencies are invalid. Now
that the source code has been validated a comparison can be made to determine
what needs to be changed on the target environment and then these changes are
made. For this step I always implement an automated process on a machine
where a copy of the end environment can be held to test the deployment. The
process records a SQL delta file which can then be used (if the process has
been successful) on those environments requiring the changes. In the event an
error occurs, the error can be identified, the person responsible can be
identified, the time the error was made can be identified creating an ideal
environment for the code to be fixed quickly and the process repeated to
verify the change/fix.
This is what I like to call database change management and DB Ghost
(http://www.dbghost.com) is the result of the many years of thinking to
facilitate this process.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"Francesco" wrote:
> see SqlServer Enterprise Manager: the list of stored procedures has just
> name, owner, type and create date as columns.
> I'd like to have also the date of the last update for a stored procedure.
> This can help me a lot.
> Is this possible? Is this date recorder in the system tables?
> Thanks.
>|||As Aaron mentions â' use a source control, thatâ's what they are there for.
This is simply said rather than done however and has been the subject of my
concern for many years now. There is a document
(http://www.innovartis.co.uk/pdf/Innovartis_An_Automated_Approach_To_Do_Change_Mgt.pdf)
which outlines the approach Iâ've taken with unparalleled success. Iâ'd love an
open debate on this subject as I still see no solution other than the one I
offer which gives an automated approach to database change management that
will save your company a lot of money, which I believe (IMHO) - is our
primary function as IT personnel.
When you have your source code in your source control for any thing - be it
applications, dynamic link libraries, user controls or databases there are
some necessary processes that need to be followed.
1. The source code needs to added to your source control.
2. Developers check out the source code to make changes. With application
code integrated development environments are readily available that will
check out the relevant files however these environments for SQL Server are
not so abundant and suffer many quirks which is why â' and this is just my
opinion â' I still find Query Analyzer to be the best development environment
meaning I still have to check out the files from the source control via the
source controls interface.
3. Developers add new source code to the source control when new
functionality is required. IE: when dealing with applications this may be new
forms, DLLâ's, classes etc and when dealing with databases this may be new
tables or stored procedures. Different objects may be open to different
groups to develop as many organizations require that only a certain
individuals have the necessary abilities to make changes to tables for
example. These rules can be part of the source control allowing for a single
coherent view of functionality across the enterprise.
4. The code needs to be versioned at regular intervals to create snapshots
that facilitate deployment and code audits. All source controls have this
functionality IE: Visual Source Safe uses labels for this functionality.
5. You need a process that can deliver all the changes made to the code base
to the relevant areas. When you are dealing with applications this means
compiling the source code and deploying the compiled code to the required
machines. When talking databases this means compiling the source code and
deploying the changes to the required environments. I think this is where
people start to question this approach. Compile a database? Building a
database from the source code, compiles the source code. This step ensures
any changes made that break existing code are identified. Too often I see
broken code due to deployed code changes that ignore this fact. With
application code, compilers throw errors when dependencies are invalid. Now
that the source code has been validated a comparison can be made to determine
what needs to be changed on the target environment and then these changes are
made. For this step I always implement an automated process on a machine
where a copy of the end environment can be held to test the deployment. The
process records a SQL delta file which can then be used (if the process has
been successful) on those environments requiring the changes. In the event an
error occurs, the error can be identified, the person responsible can be
identified, the time the error was made can be identified creating an ideal
environment for the code to be fixed quickly and the process repeated to
verify the change/fix.
This is what I like to call database change management and DB Ghost
(http://www.dbghost.com) is the result of the many years of thinking to
facilitate this process.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"Francesco" wrote:
> see SqlServer Enterprise Manager: the list of stored procedures has just
> name, owner, type and create date as columns.
> I'd like to have also the date of the last update for a stored procedure.
> This can help me a lot.
> Is this possible? Is this date recorder in the system tables?
> Thanks.
>|||You can see this in information_schema.routines (column name is last_altered)
"Francesco" wrote:
> see SqlServer Enterprise Manager: the list of stored procedures has just
> name, owner, type and create date as columns.
> I'd like to have also the date of the last update for a stored procedure.
> This can help me a lot.
> Is this possible? Is this date recorder in the system tables?
> Thanks.
>|||try testing this column, you should see that it doesn't work...
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"harvinder" wrote:
> You can see this in information_schema.routines (column name is last_altered)
> "Francesco" wrote:
> > see SqlServer Enterprise Manager: the list of stored procedures has just
> > name, owner, type and create date as columns.
> > I'd like to have also the date of the last update for a stored procedure.
> > This can help me a lot.
> >
> > Is this possible? Is this date recorder in the system tables?
> > Thanks.
> >|||> You can see this in information_schema.routines (column name is
> last_altered)
NO, the data is not accurate unless you've never modified the stored
procedure. The datetime value stored here is always the same as created
date, and is never updated on ALTER... this will be more reliable using the
catalog views in SQL Server 2005, but for now the data is not stored.sql

last update date for a procedure

see SqlServer Enterprise Manager: the list of stored procedures has just
name, owner, type and create date as columns.
I'd like to have also the date of the last update for a stored procedure.
This can help me a lot.
Is this possible? Is this date recorder in the system tables?
Thanks.
Hi,
SQL Server will not store the modified date and time of the procedure.So if
you use ALter Procedure you cant see the modified date and time.
Thanks
Hari
SQL Server MVP
"Francesco" <Francesco@.discussions.microsoft.com> wrote in message
news:BE716295-7938-4A53-BA43-7A74DF780488@.microsoft.com...
> see SqlServer Enterprise Manager: the list of stored procedures has just
> name, owner, type and create date as columns.
> I'd like to have also the date of the last update for a stored procedure.
> This can help me a lot.
> Is this possible? Is this date recorder in the system tables?
> Thanks.
>
|||Hi
No, SQL Server does not track such info.
Are you only a person in the company to make changes in stored procedures
and other objects?
"Francesco" <Francesco@.discussions.microsoft.com> wrote in message
news:BE716295-7938-4A53-BA43-7A74DF780488@.microsoft.com...
> see SqlServer Enterprise Manager: the list of stored procedures has just
> name, owner, type and create date as columns.
> I'd like to have also the date of the last update for a stored procedure.
> This can help me a lot.
> Is this possible? Is this date recorder in the system tables?
> Thanks.
>
|||> see SqlServer Enterprise Manager: the list of stored procedures has just
> name, owner, type and create date as columns.
> I'd like to have also the date of the last update for a stored procedure.
> This can help me a lot.
> Is this possible? Is this date recorder in the system tables?
No. This is what source control is for.
|||As Aaron mentions – use a source control, that’s what they are there for.
This is simply said rather than done however and has been the subject of my
concern for many years now. There is a document
(http://www.innovartis.co.uk/pdf/Inno...ange_Mgt. pdf)
which outlines the approach I’ve taken with unparalleled success. I’d love an
open debate on this subject as I still see no solution other than the one I
offer which gives an automated approach to database change management that
will save your company a lot of money, which I believe (IMHO) - is our
primary function as IT personnel.
When you have your source code in your source control for any thing - be it
applications, dynamic link libraries, user controls or databases there are
some necessary processes that need to be followed.
1.The source code needs to added to your source control.
2.Developers check out the source code to make changes. With application
code integrated development environments are readily available that will
check out the relevant files however these environments for SQL Server are
not so abundant and suffer many quirks which is why – and this is just my
opinion – I still find Query Analyzer to be the best development environment
meaning I still have to check out the files from the source control via the
source controls interface.
3.Developers add new source code to the source control when new
functionality is required. IE: when dealing with applications this may be new
forms, DLL’s, classes etc and when dealing with databases this may be new
tables or stored procedures. Different objects may be open to different
groups to develop as many organizations require that only a certain
individuals have the necessary abilities to make changes to tables for
example. These rules can be part of the source control allowing for a single
coherent view of functionality across the enterprise.
4.The code needs to be versioned at regular intervals to create snapshots
that facilitate deployment and code audits. All source controls have this
functionality IE: Visual Source Safe uses labels for this functionality.
5.You need a process that can deliver all the changes made to the code base
to the relevant areas. When you are dealing with applications this means
compiling the source code and deploying the compiled code to the required
machines. When talking databases this means compiling the source code and
deploying the changes to the required environments. I think this is where
people start to question this approach. Compile a database? Building a
database from the source code, compiles the source code. This step ensures
any changes made that break existing code are identified. Too often I see
broken code due to deployed code changes that ignore this fact. With
application code, compilers throw errors when dependencies are invalid. Now
that the source code has been validated a comparison can be made to determine
what needs to be changed on the target environment and then these changes are
made. For this step I always implement an automated process on a machine
where a copy of the end environment can be held to test the deployment. The
process records a SQL delta file which can then be used (if the process has
been successful) on those environments requiring the changes. In the event an
error occurs, the error can be identified, the person responsible can be
identified, the time the error was made can be identified creating an ideal
environment for the code to be fixed quickly and the process repeated to
verify the change/fix.
This is what I like to call database change management and DB Ghost
(http://www.dbghost.com) is the result of the many years of thinking to
facilitate this process.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"Francesco" wrote:

> see SqlServer Enterprise Manager: the list of stored procedures has just
> name, owner, type and create date as columns.
> I'd like to have also the date of the last update for a stored procedure.
> This can help me a lot.
> Is this possible? Is this date recorder in the system tables?
> Thanks.
>
|||As Aaron mentions – use a source control, that’s what they are there for.
This is simply said rather than done however and has been the subject of my
concern for many years now. There is a document
(http://www.innovartis.co.uk/pdf/Inno...ange_Mgt. pdf)
which outlines the approach I’ve taken with unparalleled success. I’d love an
open debate on this subject as I still see no solution other than the one I
offer which gives an automated approach to database change management that
will save your company a lot of money, which I believe (IMHO) - is our
primary function as IT personnel.
When you have your source code in your source control for any thing - be it
applications, dynamic link libraries, user controls or databases there are
some necessary processes that need to be followed.
1.The source code needs to added to your source control.
2.Developers check out the source code to make changes. With application
code integrated development environments are readily available that will
check out the relevant files however these environments for SQL Server are
not so abundant and suffer many quirks which is why – and this is just my
opinion – I still find Query Analyzer to be the best development environment
meaning I still have to check out the files from the source control via the
source controls interface.
3.Developers add new source code to the source control when new
functionality is required. IE: when dealing with applications this may be new
forms, DLL’s, classes etc and when dealing with databases this may be new
tables or stored procedures. Different objects may be open to different
groups to develop as many organizations require that only a certain
individuals have the necessary abilities to make changes to tables for
example. These rules can be part of the source control allowing for a single
coherent view of functionality across the enterprise.
4.The code needs to be versioned at regular intervals to create snapshots
that facilitate deployment and code audits. All source controls have this
functionality IE: Visual Source Safe uses labels for this functionality.
5.You need a process that can deliver all the changes made to the code base
to the relevant areas. When you are dealing with applications this means
compiling the source code and deploying the compiled code to the required
machines. When talking databases this means compiling the source code and
deploying the changes to the required environments. I think this is where
people start to question this approach. Compile a database? Building a
database from the source code, compiles the source code. This step ensures
any changes made that break existing code are identified. Too often I see
broken code due to deployed code changes that ignore this fact. With
application code, compilers throw errors when dependencies are invalid. Now
that the source code has been validated a comparison can be made to determine
what needs to be changed on the target environment and then these changes are
made. For this step I always implement an automated process on a machine
where a copy of the end environment can be held to test the deployment. The
process records a SQL delta file which can then be used (if the process has
been successful) on those environments requiring the changes. In the event an
error occurs, the error can be identified, the person responsible can be
identified, the time the error was made can be identified creating an ideal
environment for the code to be fixed quickly and the process repeated to
verify the change/fix.
This is what I like to call database change management and DB Ghost
(http://www.dbghost.com) is the result of the many years of thinking to
facilitate this process.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"Francesco" wrote:

> see SqlServer Enterprise Manager: the list of stored procedures has just
> name, owner, type and create date as columns.
> I'd like to have also the date of the last update for a stored procedure.
> This can help me a lot.
> Is this possible? Is this date recorder in the system tables?
> Thanks.
>
|||You can see this in information_schema.routines (column name is last_altered)
"Francesco" wrote:

> see SqlServer Enterprise Manager: the list of stored procedures has just
> name, owner, type and create date as columns.
> I'd like to have also the date of the last update for a stored procedure.
> This can help me a lot.
> Is this possible? Is this date recorder in the system tables?
> Thanks.
>
|||try testing this column, you should see that it doesn't work...
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"harvinder" wrote:
[vbcol=seagreen]
> You can see this in information_schema.routines (column name is last_altered)
> "Francesco" wrote:
|||> You can see this in information_schema.routines (column name is
> last_altered)
NO, the data is not accurate unless you've never modified the stored
procedure. The datetime value stored here is always the same as created
date, and is never updated on ALTER... this will be more reliable using the
catalog views in SQL Server 2005, but for now the data is not stored.

Last image of Historical Data

Hello,

I have a slowly changing dimension Sales Person of type 2, where I am keeping history of the sales person Unit, Region, and Sales Channel which are attributed on the Sales Person dimension.

The client is requesting to be able to report on the Sales Person last image, for example if the last image Region of this sales person is Region X, then all the portfolio of this Sales Person should now be linked to Region X irrespective of what it was historically. At the same time, we should be able to report on the production done by region as it occured historically.

To my kowledge, my design would allow to report on production historically but not the last image. I read about an article that suggested to create additional attributes in the dimension (i.e. Current_Region, Current_Unit, ....) and thus for each row I will have two values for each historical attribute. Then, in order to produce the report for last image i will use the Current_ attributes that are being updated whenever a new image arrives.

I am not sure if this is a proper solution to duplicate the columns.

Would be glad to hear out any suggestions and opinions regarding this design issue.

Thanks in advance,

Grace

Hi! This is a common problem in data warehouse design and known as the slowly changing dimension(SCD) problem.

The approaches to solve this is normally outside of SSAS2005 and an ETL/DW problem:

-Type one: Overwrite all attributes/columns with the current value (This is the one you are suggesting)

-Type two: Put a timestamp on dimension members when a an attribute changes, that you would like to keep an history of. You will have to use surrogate keys for each member and version of the dimension keys to use this approach.

-Type tree: Add a new column for the attribute that you would like to keep history for.

The better approach is to separate attributes in different dimensions tables that have a dynamic relation and enter a new dimension key in the fact table. Separate geography from customer. Never have a key account or salesperson attribute in your customer table, because these relations change over time.

Have a look at www.kimballgroup.com for more information.

HTH

Thomas Ivarsson

Last image of Historical Data

Hello,

I have a slowly changing dimension Sales Person of type 2, where I am keeping history of the sales person Unit, Region, and Sales Channel which are attributed on the Sales Person dimension.

The client is requesting to be able to report on the Sales Person last image, for example if the last image Region of this sales person is Region X, then all the portfolio of this Sales Person should now be linked to Region X irrespective of what it was historically. At the same time, we should be able to report on the production done by region as it occured historically.

To my kowledge, my design would allow to report on production historically but not the last image. I read about an article that suggested to create additional attributes in the dimension (i.e. Current_Region, Current_Unit, ....) and thus for each row I will have two values for each historical attribute. Then, in order to produce the report for last image i will use the Current_ attributes that are being updated whenever a new image arrives.

I am not sure if this is a proper solution to duplicate the columns.

Would be glad to hear out any suggestions and opinions regarding this design issue.

Thanks in advance,

Grace

Hi! This is a common problem in data warehouse design and known as the slowly changing dimension(SCD) problem.

The approaches to solve this is normally outside of SSAS2005 and an ETL/DW problem:

-Type one: Overwrite all attributes/columns with the current value (This is the one you are suggesting)

-Type two: Put a timestamp on dimension members when a an attribute changes, that you would like to keep an history of. You will have to use surrogate keys for each member and version of the dimension keys to use this approach.

-Type tree: Add a new column for the attribute that you would like to keep history for.

The better approach is to separate attributes in different dimensions tables that have a dynamic relation and enter a new dimension key in the fact table. Separate geography from customer. Never have a key account or salesperson attribute in your customer table, because these relations change over time.

Have a look at www.kimballgroup.com for more information.

HTH

Thomas Ivarsson

Largest character data type?

I thought "text" was the largest, as the documentation says the max length
is (2^31)-1
But I can only fit 8000 characters in before it truncates, but even then it
is not exactly 8000 characters, but more like 1000.TEXT is the largest data type. Where do you see the truncation happening'
If it is in Query Analyzer, you need to expand the number of display
character (max is 8192). Your application will never truncate the content of
the TEXT data type.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"Wayne M J" <not@.home.nor.bigpuddle.com> wrote in message
news:OSh09QhvDHA.2076@.TK2MSFTNGP09.phx.gbl...
> I thought "text" was the largest, as the documentation says the max length
> is (2^31)-1
> But I can only fit 8000 characters in before it truncates, but even then
it
> is not exactly 8000 characters, but more like 1000.
>|||"SriSamp" <ssampath@.sct.co.in> wrote in message
news:uTxYyohvDHA.2072@.TK2MSFTNGP10.phx.gbl...
> TEXT is the largest data type. Where do you see the truncation happening'
> If it is in Query Analyzer, you need to expand the number of display
> character (max is 8192). Your application will never truncate the content
of
> the TEXT data type.
I see this from the table view, when I 'return all rows' I can scroll
through the field, but it only seems to have stored to a certain point.
The result is visible here:
http://www.wmjackson.cable.nu/website/goto.aspx?id=0|||That must be a limitation with Enterprise Manager. The data will not be
truncated.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"Wayne M J" <not@.home.nor.bigpuddle.com> wrote in message
news:eOaaYPjvDHA.1512@.TK2MSFTNGP10.phx.gbl...
> "SriSamp" <ssampath@.sct.co.in> wrote in message
> news:uTxYyohvDHA.2072@.TK2MSFTNGP10.phx.gbl...
> > TEXT is the largest data type. Where do you see the truncation
happening'
> > If it is in Query Analyzer, you need to expand the number of display
> > character (max is 8192). Your application will never truncate the
content
> of
> > the TEXT data type.
> I see this from the table view, when I 'return all rows' I can scroll
> through the field, but it only seems to have stored to a certain point.
> The result is visible here:
> http://www.wmjackson.cable.nu/website/goto.aspx?id=0
>|||"SriSamp" <ssampath@.sct.co.in> wrote in message
news:OEnSpbjvDHA.3256@.TK2MSFTNGP11.phx.gbl...
> That must be a limitation with Enterprise Manager. The data will not be
> truncated.
Actually, that makes sense.

large value data type (nvarchar(max))

hi,

I have inserted a new column in my db table (type: nvarchar(max))
now I can't process the cube anymore ... is there a possibility to store large text files in the cube?

thanks,

rhapsy

Hi Rhapsy

What are you trying to achieve with this data? Would it not be easier to try and have an action to get that text if it is needed?

Regards

Mike

|||hey mike,

probably it would be easier, but I'd like to get all my information from the same source ...
and I'm a little bit curious if thias is possible ...

thanks,
rhapsy

Large fields freeze the report generation

Hi Group,
I have this subreport that displays information from 2 fields of type
nvarchar(4000).
If I try to preview the subreport from the designer, everything works fine -
there are about 2 pages of information displayed. Export to pdf works
normally.
After deploying it on the server, if I use the
http://server_name/Reports/... again everything works fine. Exporting to pdf
produces a 2-3 pages file in about 10 seconds.
The report that contains this subreport works fine - export to pdf goes
through with no problems.
But if I try to use our application that generates / displays the reports,
it just hangs there, after calling the Render method.
The aspnet_wp process on the server stays at about 93% until the Render
method times out (approx. 10 minutes).
I tried removing the two large nvarchar fields from the subreport - the
report starts working fine like any other report.
The information that should be displayed in the report is not even so much -
as I said before, it should generate about 2-3 pages in pdf.
We have more than 200 reports working fine, none of them complained about
large nvarchar fields.
Did anybody have this type of problem ?
Please help !
Thank you,
Andrei.Correction :
After more tests, this is what I have:
Generating the main report with the Report Manager on the server
(http://server/Reports/...) produces an output in 10 seconds.
But trying to export as PDF will lock the process until timeout.
If I remove the fields of type nvarchar(4000) from the SUBreport, then the
report export to PDF works fine.
Generating the SUBreport with the Report Manager on the server
(http://server/Reports/...) produces an output in less than 10 seconds
The export to PDF works fine.
Thank you,
Andrei.
"Andrei" <andrei.toma@.era-environmental.com> wrote in message
news:esEORK8EHHA.2464@.TK2MSFTNGP06.phx.gbl...
> Hi Group,
> I have this subreport that displays information from 2 fields of type
> nvarchar(4000).
> If I try to preview the subreport from the designer, everything works
> fine - there are about 2 pages of information displayed. Export to pdf
> works normally.
> After deploying it on the server, if I use the
> http://server_name/Reports/... again everything works fine. Exporting to
> pdf produces a 2-3 pages file in about 10 seconds.
> The report that contains this subreport works fine - export to pdf goes
> through with no problems.
> But if I try to use our application that generates / displays the reports,
> it just hangs there, after calling the Render method.
> The aspnet_wp process on the server stays at about 93% until the Render
> method times out (approx. 10 minutes).
> I tried removing the two large nvarchar fields from the subreport - the
> report starts working fine like any other report.
> The information that should be displayed in the report is not even so
> much - as I said before, it should generate about 2-3 pages in pdf.
> We have more than 200 reports working fine, none of them complained about
> large nvarchar fields.
> Did anybody have this type of problem ?
> Please help !
> Thank you,
> Andrei.
>