get a value from a measure

I want to create a new calculated member in a virtul cube.
I have a dimension Time with 3 levels Year Month and Day and a Measure ValSa
l.
I want to get a value from the measure ValSal on day 2004/05/25, but i don't
know how?
With this value i want to fill the calculated member.
Any help is appreciated.
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...Try ([Measures].[ValSal],[Time].&[2005].&[5].&[25])
, if your dimension
[Time] is Year-month-day
wrote in message news:epEgjy4UEHA.212@.TK2MSFTNGP12.phx.gbl...
> I want to create a new calculated member in a virtul cube.
> I have a dimension Time with 3 levels Year Month and Day and a Measure
ValSal.
> I want to get a value from the measure ValSal on day 2004/05/25, but i
don't know how?
> With this value i want to fill the calculated member.
>
> Any help is appreciated.
> ****************************************
******************************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP &
ASP.NET resources...

get a value from a measure

I want to create a new calculated member in a virtul cube.
I have a dimension Time with 3 levels Year Month and Day and a Measure ValSal.
I want to get a value from the measure ValSal on day 2004/05/25, but i don't know how?
With this value i want to fill the calculated member.
Any help is appreciated.
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
Try ([Measures].[ValSal],[Time].&[2005].&[5].&[25]), if your dimension
[Time] is Year-month-day
wrote in message news:epEgjy4UEHA.212@.TK2MSFTNGP12.phx.gbl...
> I want to create a new calculated member in a virtul cube.
> I have a dimension Time with 3 levels Year Month and Day and a Measure
ValSal.
> I want to get a value from the measure ValSal on day 2004/05/25, but i
don't know how?
> With this value i want to fill the calculated member.
>
> Any help is appreciated.
> ************************************************** ********************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP &
ASP.NET resources...
sql

Geographical realignment dimension

I have a fact table filled with clients including their address information as well as their original geographical alignment info (RegionID, DistrictID and TerritoryID). In order to forecast as well as perform an “as if” analysis, I need to be able to realign the accounts based on previous or future alignments. I have an alignment table (dimension) that maintains alignment information by year and quarter and I can join to this table using the ClientAddressID field as well as the Year and Quarter fields. I’d like to be able to make the Year and Quarter fields/join be variable so that I can perform realignments simply by changing the year/quarter.

Is this possible in SSAS or am I going to have to find another solution? Any help will be greatly appreciated...

David

I resolved this using a many to many relationship. This write-up was very useful: http://msdn2.microsoft.com/en-us/library/ms170463.aspx

David

Geographical realignment dimension

I have a fact table filled with clients including their address information as well as their original geographical alignment info (RegionID, DistrictID and TerritoryID). In order to forecast as well as perform an “as if” analysis, I need to be able to realign the accounts based on previous or future alignments. I have an alignment table (dimension) that maintains alignment information by year and quarter and I can join to this table using the ClientAddressID field as well as the Year and Quarter fields. I’d like to be able to make the Year and Quarter fields/join be variable so that I can perform realignments simply by changing the year/quarter.

Is this possible in SSAS or am I going to have to find another solution? Any help will be greatly appreciated...

David

I resolved this using a many to many relationship. This write-up was very useful: http://msdn2.microsoft.com/en-us/library/ms170463.aspx

David

sql

Generating a primary key

I'm having problems generating the primary key for a sql server table. I use a slowly changing dimension to discriminate modified and new records. The primary key in the SQL Server table is a combo number/letter incremental (ex. 0000A, 0001A...9999A, 0000B...). I tried creating Instead of insert and For insert trigger for a table but this doesn't seem to do the work.

What are my other options? How can I generate a primary key for every new row?

Any advice is appreciated.

Regards

Sara

I personally prefer generating my key values inside the pipeline. For your scheme you would need to write some bespoke code to generate the incrementing value. Use a Script Component to host this code, and output a new column with the value.

Here is an example of this pattern in use - http://www.ssistalk.com/2007/02/20/generating-surrogate-keys/, just tweak teh code to give you your values.

|||

Wow, thank you very much, I followed your example (ignoring the letters, trying with incremental numbers only), it works and it works well!

Just have to play around to make it work with letters too.

Thanx again!

|||

Sara,

Primary keys should never contain logic within them. Why do you need the letters? Using integers, you can achieve the correct order of SCD changes for a given business key, so I don't see the need for letters at all.

|||What do you exactly mean by 'the correct order of SCD changes for a given business key'?|||

Sara4 wrote:

What do you exactly mean by 'the correct order of SCD changes for a given business key'?

I was just trying to make an assumption as to why you'd want letters in your key. With integers, you could select a business key and order by the primary key to see the order of the changes as they occurred. Granted, you could do that with your approach as well, but you're actually going to use more storage using your method (based on your example above) versus using a 4-byte integer field.

General: Audit Dimension

What is the best practice for setting up audit dimensions in 2005? I know that we have the audit transform available, but should we be capturing extra information beyond this?

I had seen one of the design tip articles by kimball on what should be added, however, this article was from some time ago... (http://kimballgroup.com/html/designtipsPDF/DesignTips2001/KimballDT26AddingAnAudit.pdf)

Also, I try to capture the audit guid in the record and then, later, capture the rest of the audit information... I notice that the guid's are different between the audit transforms... is there a way to capture all of the information but not write it all out to each record? i.e. only capture the guid to the record and the rest of the information to the audit table.

Take a look at the Kimball Webcast on the first page of this forum:. Joy talks about creating auditing features:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=534505&SiteID=1|||

Thanks phil. I have watched that video in the past, I might take another look at it. I was, however, hoping for a bit of discussion on what others in the industry were doing. I have looked through the Microsoft Data Warehouse Toolkit, Practical B.I. for SQL Server 2005, Delivering B.I. with Microsoft 2005, as well as a few of the articles on the web. I was just curious, since each has a different level of detail which they capture, what is acceptable down the road (I was told at one of the tdwi conferences that when building the warehouse, the first thing most people would do if they had the oppurtunity to start over is set up a good mdm / audit system, so that is what I'm trying to do)...

|||I implement a version of what Joy illustrated in her Webcast.