Latest RDL Specification?

I read in someone's blog somewhere that the RDL Specification was changing
for SSRS 2005 (http://blogs.msdn.com/gsnowman/archive/2005/10/12/480328.aspx).
I went to the MS Reporting Services website and this version of the
specification appears to be old
(http://www.microsoft.com/sql/technologies/reporting/rdlspec.mspx). Can
someone either confirm or deny that the spec located at
http://www.microsoft.com/sql/technologies/reporting/rdlspec.mspx is the
latest RDL specification for SSRS2005?
Thanks!
Brian McCulloughWell I guess this was sort of a dumb question...
You can find the latest schema out here (which you can tell by looking at
the reference in the <Report> tag):
http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition/
"brianpmccullough" wrote:
> I read in someone's blog somewhere that the RDL Specification was changing
> for SSRS 2005 (http://blogs.msdn.com/gsnowman/archive/2005/10/12/480328.aspx).
> I went to the MS Reporting Services website and this version of the
> specification appears to be old
> (http://www.microsoft.com/sql/technologies/reporting/rdlspec.mspx). Can
> someone either confirm or deny that the spec located at
> http://www.microsoft.com/sql/technologies/reporting/rdlspec.mspx is the
> latest RDL specification for SSRS2005?
> Thanks!
> Brian McCullough

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

Last Day of the Month Subscription

Does anyone have a way to set up a Subscription for the last day of the
month?
We've been changing reports so they only have data on the last day of the
month, but the report also needs to run on demand and return data.
There must be a way.
Thanks,
MikeSo far, I have not found a simple way to do this (thanks Microsoft for
leaving out the obvious). Interestingly MSDN and Technet both have articles
that say you can run a report 'on the last day of the month' but never
provide details. I have to run my report on the last day of the month...it
cannot be on the first day (which is easy).
My first thought had been to set it to run on 31st for all months and
perhaps Reporting Services is smart enough to figure out that there aren't 31
days in a particular month...but I am afraid that it simply won't fire on
these months...
The only solution I have come up (but not tested) with requires creating 3
separate subscriptions and setting them to run on the last day of specific
months (check only the months you want that version of the subscription to
run).
28 -29 - Feb (uses a range - would probably generate an extra report in
those years with 29 days in Feb...)
30 - April, June, Sept, Nov
31 - Jan, March, May, July, Aug, Oct, Dec

Last Date Stored Proc Updated??

Is there such a date/time?

I see the Created date on the list of stored procs, but really want a
Date Last Updated. After changing code for 3 hours, I tend to forget
which procs I've worked on, and which need to be move to production.
any simple way to keep track of the last procs played with?

thanks in advance...
john@.ViridianTech.comDo you mean you've used ALTER PROC and you want to see when it was
ALTERed? If so, then this isn't currently possible in MSSQL (although
it is in SQL 2005).

In any case, you should hopefully be using some sort of source control
system to store your object creation scripts, and a deployment process
which would help to track your changes. You might also want to consider
a database comparison tool, which can quickly show you the differences
between databases.

Simon|||Actually, we're not using any source control on the stored procs, like
we do on the project source code. How would you do that? is there
something in MSSQL for that? the .NWT IDE for VB make it easy to
integrate with Source Safe, what do you use for stored procs, views and
table creation scripts? You advice would be much appreciated.

john|||<John@.ViridianTech.com> wrote in message
news:1120484863.856415.206040@.o13g2000cwo.googlegr oups.com...
> Actually, we're not using any source control on the stored procs, like
> we do on the project source code. How would you do that? is there
> something in MSSQL for that? the .NWT IDE for VB make it easy to
> integrate with Source Safe, what do you use for stored procs, views and
> table creation scripts? You advice would be much appreciated.
> john

Personally, I simply check code out of VSS and work with it in Query
Analyzer - there's no source control integration in the MSSQL tools
themselves. I believe Visual Studio has some sort of support for SQL code
and VSS, although I don't use VS often myself, so I may be wrong about that.

Even using just QA and VSS, a few scripts can make things easier - the
Customize menu in QA allows you to pass a few useful parameters to batch
files or other programs, so it's not too difficult to script checking in and
out of VSS (SQL 2005 has source control integration in the Management
Studio).

Erland has an interesting toolset for working with SQL source code and VSS,
written in Perl, which might be worth looking at if you want to develop your
own solution or just need some ideas about managing and deploying SQL code:

http://www.abaris.se/abaperls/index.html

Simon|||(John@.ViridianTech.com) writes:
> Actually, we're not using any source control on the stored procs, like
> we do on the project source code. How would you do that?

You just do it!

> is there something in MSSQL for that? the .NWT IDE for VB make it easy
> to integrate with Source Safe,

Hrmpf! Nothing in Visual Studio is easy. (I understand less and less of
it for each new version they come out with.) And in our shop, you may
use the SourceSafe integration for the VB code, but if you mess up,
our build people will tell you to stop doing it.

The absolutely best too to work with SourceSafe is the VSS Explorer.

> what do you use for stored procs, views and
> table creation scripts? You advice would be much appreciated.

Actually, we don't even use QA for editing, but use Textpad instead,
simply because it's a better editor.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||If you're not going to have access to a source control tool or
methodology any time soon, here's a workaround; but there's nothing
foo-proof about this, so you still have to be careful:

Just drop and create your stored procedures instead of altering them.
This modifies the crdate in sysobjects, which is reflected in
Enterprise Manager.

drop procedure proc_mytest
go

create procedure proc_mytest
as
< whatever
hth,

victor dileo|||vjdileo (vic_technews@.yahoo.com) writes:
> If you're not going to have access to a source control tool or
> methodology any time soon, here's a workaround; but there's nothing
> foo-proof about this, so you still have to be careful:
> Just drop and create your stored procedures instead of altering them.
> This modifies the crdate in sysobjects, which is reflected in
> Enterprise Manager.

And there is actually a way of detecting that a procedure have
been altered. sysobjects.schema_ver is incremented with 16 each
you alter the procedure.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Guys, there really is a great way of working with your SQL code in just
the same way as you do for your application code and it's called DB
Ghost (www.dbghost.com). It's the ONLY SQL Server tool on the market
that can build a brand new database from a set of object creation
scripts taking care of all dependencies. This database is then used by
D Ghost to compare and upgrade any target database.

The upshot of our approach is that you have ALL database objects as
'create' scripts under source control and just modify those. You work
in a familiar manner and the source control system becomes your friend
rather than a necessary evil. Most other approaches to having SQL in
source control involve having to do two things for each update:
1. Update the create script.
2. Write an ALTER script.

DB Ghost does away with the second of these not just for stored
procedures but for EVERY database object.

Honestly, you may think this approach cannot work but it does and our
customers use phrases like 'religious experience' when they talk about
it. You just have to take the time to 'get' it...|||We just do a generate sql script for all procs (separate file for each)
and add them to a source safe project and use that through visual
studio. Pretty easy and works great. If you make the project a database
project in visual studio it will let you execute them from there as
well.

Large Slowly Changing Dimension

Hello,
I'm somewhat new to data warehousing, and have some basic questions.
Let's say I have a fact table, named sales. The financial analyst
would like to be able to sort the sales information based on several
customer related attributes. Some of these attributes would include
zip code, age, and income. While I can create a customer dimension,
these attributes change over time.
The analysts are really only interested in these customer attributes
at the time of the sale. For example, they only care about where a
customer lived at the time of the sale, but not where they are living
now. Now I know there are different techniques for dealing with
changing dimensions, but I'm left with the impression that it would be
making things more complicated then necessary.
Would it not be best to just simply store this information in the
fact table itself? While I'm aware that fact tables are primarily used
to store numerical information, I don't see the advantage of putting
this information in a separate dimension.
Your input would be much appreciated.
Thanks,
AttilaHi,
You say the analysts are interested in the customer attributes at the time
of the sale. So these attributes will have to be stored in dimensions to be
able to analyse them using datawarehouse techniques. These must be slowly
changing. So when you add more salesfacts, e.g. with a zipcode that doesnt
exist in the zipcode dimension yet, you'll have to add it to the dimension.
The alternative is to drop and recreate all the facts and dimensions, but
that's no option if the customers move or the incomes change in the source
system.
Good luck...
Jeroen.
"Attila" <ThisIsAFakeAddress35@.hotmail.com> wrote in message
news:9516751f.0401290847.23e49471@.posting.google.com...

quote:

---

> Hello,
> I'm somewhat new to data warehousing, and have some basic questions.
> Let's say I have a fact table, named sales. The financial analyst
> would like to be able to sort the sales information based on several
> customer related attributes. Some of these attributes would include
> zip code, age, and income. While I can create a customer dimension,
> these attributes change over time.
> The analysts are really only interested in these customer attributes
> at the time of the sale. For example, they only care about where a
> customer lived at the time of the sale, but not where they are living
> now. Now I know there are different techniques for dealing with
> changing dimensions, but I'm left with the impression that it would be
> making things more complicated then necessary.
> Would it not be best to just simply store this information in the
> fact table itself? While I'm aware that fact tables are primarily used
> to store numerical information, I don't see the advantage of putting
> this information in a separate dimension.
> Your input would be much appreciated.
> Thanks,
> Attila

---

|||> So these attributes will have to be stored in dimensions to be
able to analyse them using datawarehouse techniques.
Can you elaborate on this part? What specifically will an analyst not
be able to do if I store the customer data in the fact table?
Thanks,
Attila
"Spike" <jeroenaNOSPAM@.hotmail.com> wrote in message news:<bvgf14$sjl$1@.reader08.wxs.nl>...[QUOTE]
> Hi,
> You say the analysts are interested in the customer attributes at the time
> of the sale. So these attributes will have to be stored in dimensions to b
e
> able to analyse them using datawarehouse techniques. These must be slowly
> changing. So when you add more salesfacts, e.g. with a zipcode that doesnt
> exist in the zipcode dimension yet, you'll have to add it to the dimension
.
> The alternative is to drop and recreate all the facts and dimensions, but
> that's no option if the customers move or the incomes change in the source
> system.
> Good luck...
> Jeroen.
> "Attila" <ThisIsAFakeAddress35@.hotmail.com> wrote in message
> news:9516751f.0401290847.23e49471@.posting.google.com...|||I suggest reading articles written by Ralph Kimball. Here's one of his
classic articles on slowly changing dimensions.
http://www.dbmsmag.com/9604d05.html
"Attila" <ThisIsAFakeAddress35@.hotmail.com> wrote in message
news:9516751f.0402020643.36d448b7@.posting.google.com...
> able to analyse them using datawarehouse techniques.
> Can you elaborate on this part? What specifically will an analyst not
> be able to do if I store the customer data in the fact table?
> Thanks,
> Attila
>
> "Spike" <jeroenaNOSPAM@.hotmail.com> wrote in message
news:<bvgf14$sjl$1@.reader08.wxs.nl>...
time
be
slowly
doesnt
dimension.
but
source|||There is an entire chapter in Ralph Kimballs book on mini dimensions,
which help to stabilise the pain of rapidly changing dimensions.
Ashish
On Tue, 17 Feb 2004 15:29:51 -0500, Domenico Discepola
<domenico_discepola@.quadrachemicals.com> wrote:

> I suggest reading articles written by Ralph Kimball. Here's one of his
> classic articles on slowly changing dimensions.
> http://www.dbmsmag.com/9604d05.html
>
> "Attila" <ThisIsAFakeAddress35@.hotmail.com> wrote in message
> news:9516751f.0402020643.36d448b7@.posting.google.com...
> news:<bvgf14$sjl$1@.reader08.wxs.nl>...
> time
> be
> slowly
> doesnt
> dimension.
> but
> source
>
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/