Dimension not slicing data - all the same values

When I try to add a referenced dimension, which I done many times already, the values do not slice / are the same value for each dimension entry. Suggestions?Figured it out... I had a many-to-many relationship. Just implemented what was mentioned here: http://technet.microsoft.com/en-us/library/ms345139.aspx

cheers.

Dimension based on (derived from) Measure value

There is a measure in the cube called Price. Also a dimension called Product.

I need to create a Dimension that classifies each Product by a "Price Range". For example, Expensive, Moderate, Cheap.

A user can therefore choose Cheap from "Price Range" dimension and see the "Cheap" Products and the associated Measures (Price, Units Sold, Cost, etc).

To derive the classification, a Case statement can be used
CASE price
WHEN price > 20.00 THEN 'Expensive'
WHEN price BETWEEN 10.00 AND 19.99 THEN 'Moderate'
WHEN price < 10.00 THEN 'Inexpensive'
etc.......

But I can't figure out how to make this information a dimension.

I've tried a couple of things but have been unsuccessful. Please help!

If it was me, I would do the following;

1. add an additional columnn to your fact table for price range, make it an integer

2. create a dimension for these price ranges

3. apply the case statement you have here as an update to that

fact table and change the terms ('Expensive', 'Moderate', etc.) to

integers that match the counterparts in your new price range dimension

4. add the new dimension to the cube schema, etc.

5. reprocess

Unless I am not understanding what you are going for here that should do it.

Edward R Hunter|||

Hi Edward -

Thank you for your feedback. You are right on the money with your suggestion, and that would be my first choice as I think it is the more correct way to design this. However, the relational data store is owned by a different group in my company and I have no update privileges to it, and change requests are added to a very long list of requests.

Also, I just think that this should be something that the tool should allow if needed.

If anyone reading this is interested, I did figure out how to add a dimension based on a Measure. I used the dimension wizard, selected the measure as the source, and select the option "Ordering and Uniqueness of Members" (i think this is the key). Then, in the dimension editor, add the case statement to the member key column and member name column expressions. process and there it is.

Edward's solution above is also a very good one and the one i would have used if I were designing this from the beginning. I would have also created a table that would hold the limits to the ranges. This would be updateable by administrators or power users via a simple web form. Then pass those values as variables to the case statement.

Joel B

Dimension based on (derived from) Measure value

There is a measure in the cube called Price. Also a dimension called Product.

I need to create a Dimension that classifies each Product by a "Price Range". For example, Expensive, Moderate, Cheap.

A user can therefore choose Cheap from "Price Range" dimension and see the "Cheap" Products and the associated Measures (Price, Units Sold, Cost, etc).

To derive the classification, a Case statement can be used
CASE price
WHEN price > 20.00 THEN 'Expensive'
WHEN price BETWEEN 10.00 AND 19.99 THEN 'Moderate'
WHEN price < 10.00 THEN 'Inexpensive'
etc.......

But I can't figure out how to make this information a dimension.

I've tried a couple of things but have been unsuccessful. Please help!

If it was me, I would do the following;
1. add an additional columnn to your fact table for price range, make it an integer
2. create a dimension for these price ranges
3. apply the case statement you have here as an update to that fact table and change the terms ('Expensive', 'Moderate', etc.) to integers that match the counterparts in your new price range dimension
4. add the new dimension to the cube schema, etc.
5. reprocess
Unless I am not understanding what you are going for here that should do it.
Edward R Hunter
|||

Hi Edward -

Thank you for your feedback. You are right on the money with your suggestion, and that would be my first choice as I think it is the more correct way to design this. However, the relational data store is owned by a different group in my company and I have no update privileges to it, and change requests are added to a very long list of requests.

Also, I just think that this should be something that the tool should allow if needed.

If anyone reading this is interested, I did figure out how to add a dimension based on a Measure. I used the dimension wizard, selected the measure as the source, and select the option "Ordering and Uniqueness of Members" (i think this is the key). Then, in the dimension editor, add the case statement to the member key column and member name column expressions. process and there it is.

Edward's solution above is also a very good one and the one i would have used if I were designing this from the beginning. I would have also created a table that would hold the limits to the ranges. This would be updateable by administrators or power users via a simple web form. Then pass those values as variables to the case statement.

Joel B

Digits converted to null

Hi,
Im loading data from excel source into a table with all the columns as varchar, I found out that rows from excel with digit value are transformed to Null values into the destination table.

One workaround was to add single quote at the beginning of the digits from the excel file. Is there a way in the SSIS to do the transformation instead ofmanually updating the excel file?

any help...tnx..

Not that I know of and I've spent some time looking. Reading data from Excel is tricky. For example, the data type of the column can change from row-to-row, and Excel can store data that doesn't match the defined data type. These are pretty big challenges for an OLE DB provider trying to read it like a table.
In several cases I've resorted to exporting my Excel source to a tab-delimited file for SSIS to read. At least you can automate this instead of having to manually fix each Excel file.|||You need to specify Import Mode by adding IMEX=1 to the connection string, in the Extended Properties argument along with the Excel version and HDR name/value pairs.

Please note that we have done our best to document this and other known issues in the topics for the Excel Source and the Excel Destination in BOL. This content has been further augmented for the upcoming Web refresh of BOL.

-Doug|||This type of problem was also applicable to DTS so this article should

probably still apply

Excel Inserts Null Values

(http://www.sqldts.com/default.aspx?254)

Allan

"DouglasL@.discussions.microsoft.com"

wrote in message

news:3b274106-b020-42bb-95cf-a1aeb554ea98@.discussions.microsoft.com:

> You need to specify Import Mode by adding IMEX=1 to the connection

> string, in the Extended Properties argument along with the Excel version

> and HDR name/value pairs.

>

> Please note that we have done our best to document this and other known

> issues in the topics for the Excel Source and the Excel Destination in

> BOL. This content has been further augmented for the upcoming Web

> refresh of BOL.

>

> -Doug

Difficult question

Hello,
I have a table with stock values, they are grouped by a stock id. now I want
to get the trend of each stock. I only need this for one value per w for
one month.
for example:
stockid price
1 20
2 10
3 5
1 21
2 9
3 5
1 24
2 8
3 4
1 28
2 5
3 5
now I wan tot group them like:
stockid price1 price2 price3 price4
1 20 21 24 28
2 10 9 8 5
3 5 5 4 5
This way I can tell what the trend of the prices are, going up or down.
Or is there an other way of doing this?
Any help is appreciated,
-MarkYour design is wrong; we need a date or something by which to arange
the prices. Get a book on basic RDBMS and read Dr. Codd's 12 rules.
Look at the rule about using scalar values in columns of tables to
model all relationships.
SELECT ticker_sym,
CASE WHEN quote_date = '2005-11-22'
THEN price END AS price_1,
CASE WHEN quote_date = '2005-11-23'
THEN price END AS price_2,
CASE WHEN quote_date = '2005-11-24'
THEN price END AS price_3
FROM StockHistory
GROUP BY ticker_sym;|||Thanks,
Well actually there is a date column and other columns as well, I just used
these because I thought they where the importante ones, my mistake.
Table Def:
ID (PK)
StockID (FK)
Date (datetime)
ClosePrice (money)
This gives me the following result:
StockID, P1, P2, P3, P4
1, 20, NULL, NULL, NULL
1, NULL, 21, NULL, NULL
1, NULL, NULL, 24, NULL
1, NULL, NULL, NULL, 28
What I would like is
StockID, P1, P2, P3, P4
1, 20, 21, 24, 28
-Mark
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1132859937.260224.130920@.f14g2000cwb.googlegroups.com...
> Your design is wrong; we need a date or something by which to arange
> the prices. Get a book on basic RDBMS and read Dr. Codd's 12 rules.
> Look at the rule about using scalar values in columns of tables to
> model all relationships.
> SELECT ticker_sym,
> CASE WHEN quote_date = '2005-11-22'
> THEN price END AS price_1,
> CASE WHEN quote_date = '2005-11-23'
> THEN price END AS price_2,
> CASE WHEN quote_date = '2005-11-24'
> THEN price END AS price_3
> FROM StockHistory
> GROUP BY ticker_sym;
>|||SELECT ticker_sym,
SUM( CASE WHEN quote_date = '2005-11-22'
THEN price END) AS price_1,
SUM( CASE WHEN quote_date = '2005-11-23'
THEN price END) AS price_2,
SUM( CASE WHEN quote_date = '2005-11-24'
THEN price END) AS price_3
FROM StockHistory
GROUP BY ticker_sym;|||Thanks Celko,
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1132861841.665270.278530@.g14g2000cwa.googlegroups.com...
> SELECT ticker_sym,
> SUM( CASE WHEN quote_date = '2005-11-22'
> THEN price END) AS price_1,
> SUM( CASE WHEN quote_date = '2005-11-23'
> THEN price END) AS price_2,
> SUM( CASE WHEN quote_date = '2005-11-24'
> THEN price END) AS price_3
> FROM StockHistory
> GROUP BY ticker_sym;
>

Difficult Query. Is it possible?

Hi,

I am looking for a type of aggregate function for a string. Instead of finding a Max value or the average of a column, I would like to build one string value holding the aggregate.

Example:

Source data

RecordID PersonID Name Course Score

1 1 Fred Maths 70

2 1 Fred Science 78

3 2 Mary Maths 65

4 2 Mary Science 60

5 2 Mary History 85

I would like my query to return the following resultset:

Name Scores

Fred 70; 78

Mary 65; 60; 85

Hi R2 DJ,

In your ASP.NET Application, using DataGrid web server control and implementing ItemDataBound event.

Good Coding!

Javier Luna
http://guydotnetxmlwebservices.blogspot.com/

|||

You could use UDF's, but there are some limitations/dificulties.

For instance, the next sample is simple, but will only work for response sizes until varchar (max). You could do the same with text, but it would be a little bit more complicated:

create FUNCTION dbo.ConcatenateEmployeeCustomers

(

-- Add the parameters for the function here

@.iIDEmployee int

)

RETURNS varchar ( max )

AS

BEGIN

declare @.vcTotal as varchar (max )

set @.vcTotal = ''

select @.vcTotal = @.vcTotal + ISNULL ( CustomerID + ',' , '')

From orders

where EmployeeID = @.iIDEmployee

RETURN @.vcTotal

END

GO

select dbo.ConcatenateEmployeeCustomers ( EmployeeID ) , *

from employees

|||

In SQL Server 2005, you can do this using XML. For

your case, it would look something like this:

-- Adapted from an example posted by Erland Sommarskog

select

Name,

substring(IdList, 1, datalength(IdList)/2 - 1)

-- strip the last ',' from the list

from (

select distinct Name from YourTable

) as c -- or use a Names table if one exists

cross apply (

select

convert(nvarchar(30), PersonID) + ',' as [text()]

from YourTable as o

where o.PersonID = c.PersonID

order by o.RecordID

for xml path('')

) as Dummy(IdList)

Steve Kass

Drew University

www.stevekass.com

R2 DJ@.discussions.microsoft.com wrote:

> Hi,

>

> I am looking for a type of aggregate function for a string. Instead of

> finding a Max value or the average of a column, I would like to build

> one string value holding the aggregate.

>

> Example:

>

> Source data

>

> RecordID PersonID Name Course

> Score

>

> 1 1 Fred

> Maths 70

>

> 2 1 Fred

> Science 78

>

> 3 2 Mary

> Maths 65

>

> 4 2 Mary

> Science 60

>

> 5 2 Mary

> History 85

>

> I would like my query to return the following resultset:

>

> Name Scores

>

> Fred 70; 78

>

> Mary 65; 60; 85

>

>

Different results when updating a cube

I've updated an OLAP cube and I can see the new cell value in my report.

But I was surprised when I tried to execute a MDX against the changes I made and compared it with the data of my cube in the reportin-Services.

I updated a cube cell with the value of 1

The report showed - in fact - 1; but the MDX-query got 0.99999 back.

Why is their a different?

Any idea?

Florian

If the cell you updated was not right down at the leaf level, then AS would have split up the value you were allocating across all the leaf level cells under the one that you updated. My guess is that this has most likely resulted in a small rounding error. The report is probably just set to format to a given number of decimal places an is rounding up.

Different Colors for Values

Hi NG,
i want different colors for the values in my drilldown-tables
i.e.
if value < 0 the values should be red
if value > 0 the values should be black
any suggests?
frankHI
Go to Properties > Color > Expression
And type: =iif(value < 0, â'redâ', â'blackâ')
:-)
"Frank Matthiesen" wrote:
> Hi NG,
> i want different colors for the values in my drilldown-tables
> i.e.
> if value < 0 the values should be red
> if value > 0 the values should be black
> any suggests?
>
> frank
>
>|||Soan wrote:
> Go to Properties > Color > Expression
> And type: =iif(value < 0, "red", "black")
oh man...so easy!
top...thx a lot
frank|||Folks,
I can't get line color changes to appear in a line chart. Did SP1 provide
color control for the Line chart as well?
Thanks,
Chris
"Frank Matthiesen" wrote:
> Soan wrote:
> > Go to Properties > Color > Expression
> > And type: =iif(value < 0, "red", "black")
> oh man...so easy!
> top...thx a lot
> frank
>
>|||For line charts, the line border color property is what you are looking for.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Chris Borgers" <ChrisBorgers@.discussions.microsoft.com> wrote in message
news:D655C59C-1B8F-4BC3-A9D3-6FD3CA409F79@.microsoft.com...
> Folks,
> I can't get line color changes to appear in a line chart. Did SP1 provide
> color control for the Line chart as well?
> Thanks,
> Chris
> "Frank Matthiesen" wrote:
> > Soan wrote:
> > > Go to Properties > Color > Expression
> > > And type: =iif(value < 0, "red", "black")
> >
> > oh man...so easy!
> > top...thx a lot
> >
> > frank
> >
> >
> >

Different calculation based on dimension attribute?

Hello

I would like to do this in pseudo code for a calculated member:

if(dim.value == 1) then measure.val

else -measure.val

Anyone have a suggestion?

For AS2005, you should include the following in the MDX script for your cube (your will have to adjust for naming):

CREATE MEMBER CURRENTCUBE.[Measures].[MyCalculatedMeasure] AS

[Measures].[MyMeasure] * -1,

NON_EMPTY_BEHAVIOR = [Measures].[MyMeasure];

SCOPE ([MyDimension].[MyAttribute].[1], [Measures].[MyCalculatedMeasure];

This = [Measures].[MyMeasure];

END SCOPE;

... and if it's a calculated member defined at run-time.

WITH MEMBER [Measures].[MyCalculatedMeasure] AS

IIF([MyDimension].[MyAttribute].currentmember IS [MyDimension].[MyAttribute].[1], [Measures].[MyMeasure], -1 * [Measures].[MyMeasure]), NON_EMPTY_BEHAVIOR = [Measures].[MyMeasure]

SELECT .....

|||

Thanks for the quick response, I did solve my first problem.. sorta, with SQL in the integration layer. Now I have almost the same problem though. Now I am trying to do this instead:

If([Dim Account].[Dim Account Type] = 1) then 0 else [Measures].[Fact Result AMOUNT]

I modified the query like so, but it only returns 0, never the Fact Result AMOUNT measure.

CREATE MEMBER CURRENTCUBE.[Measures].[Cost] AS

0,

NON_EMPTY_BEHAVIOR = [Measures].[Fact Result AMOUNT];

SCOPE ([Dim Account].[Dim Account Type].[1], [Measures].[Cost]);

This = [Measures].[Fact Result AMOUNT];

END SCOPE;

|||

Please remove the NON_EMPTY_BEHAVIOR = [Measures].[Fact Result AMOUNT] part.

If you don't remove it - you are risking to get wrong results.

|||

It looks like your logic is reversed. If you want to achieve the following

If([Dim Account].[Dim Account Type] = 1) then 0 else [Measures].[Fact Result AMOUNT]

Then the script should be.

CREATE MEMBER CURRENTCUBE.[Measures].[Cost] AS

[Measures].[Fact Result AMOUNT],

NON_EMPTY_BEHAVIOR = [Measures].[Fact Result AMOUNT];

SCOPE ([Dim Account].[Dim Account Type].[1], [Measures].[Cost]);

This = 0;

END SCOPE;

Mosha - does this address your issue about the NON_EMPTY_BEHAVIOR returning incorrect results? If not would you be able to explain where the issue is?

|||

Mosha - does this address your issue about the NON_EMPTY_BEHAVIOR returning incorrect results? If not would you be able to explain where the issue is?

No, it is still wrong in this example, because when Fact Result AMOUNT can be NULL, the Cost won't be NULL (it will be 0). Note that in your first example, it was correct, since MyCalculatedMeasure and MyMeasure are always either together NULLs or together not NULLs (assuming that there are no other calculations in the cube).