Dimension Design using attributes?

I would like to design a dimension as part of a cube to enable all survey results to be displayed in a single excel chart.

I have 10 questions each with answer choices (A, B, C, D, NO Response)

The excel chart would have a bar for each question, and each bar would be composed of 4 colors representing either A,B,C or D.

I can generate 10 dimensions (one for each question), but I can't get it to fit in the same chart. I would like to create one dimension encapsulating the entire survey..by possibly using attributes.

Is there a bettwer way to do this...using excel and as2005?

Any help or advise would be appreciated!

The "Survey" section of this paper on many-to-many dimensional modelling discusses how to incorporate the answers to multiple questions of a survey in a single dimension:

http://www.sqlbi.eu/Portals/0/Downloads/M2M%20Revolution%201.0.93.pdf

>>

...

Survey

The survey scenario is a common example of a more general case where you have a lot of attributes associated to a case (one customer, one product, and so on) and you want to normalize the model because you do not want to change the UDM each time you add a new attribute to data (as adding a new dimension or changing an existing one). One common scenario is a questionnaire consisting of questions that have predefined answers with both simple and multiple choices.

...

>>

Differentiate between fields of TWO Datasets in a single report

Dear ppl,

I have got 2 datasets D1 & D2 in a Report. How can i differentiate between the fields of these two.

e.g. I got Name field in both the datasets. So when i do =Fields!Name.Value in a textbox the report gives me error

How can i tell the report from which dataset to pick up the field ?

Regards

Nabeel

Is your textbox inside a Data Region (List, or Table), Or is it placed independently on the form?If is placed independently on the form you would have to reference it using an aggregate expression, using the following format.

=First(Fields!Name.Value, “Scope”)

Scope being then name of the Dataset.

Hope this helps!!!

Roberto Hernández-Pou
http://community.rhphconsulting.net

|||

Normally the text box will be nested in a list control which has a dataset property (when dataset has multiple rows).

Alterantively if you just put the textbox directly into the body of the report, you can use an aggregation function which takes the dataset name as the second parameter e.g.

=First(Fields!Name.Value, "DataSetName")

|||

Hello Nabeel,

Try the following syntax: =(Fields!Name.Value, "Put the name of your dataset here including the quotes")

Regards,
Worf
|||

cheers guys..thanx for the help

||| why the aggregate function is required for the differentiation. cant it just differentiate with the name?|||

It's all to do with context.

Usually, you have an expression such as =Fields!x.Value or =Sum(Fields!x.Value) in a textbox of a matrix, table, or list. Each of these controls needs a dataset property to be specified. This sets the context and hence the dataset from which the field is sourced. This can be overridden within an aggregate function by explicitly specifying the scope of the aggregate. This can either be the name of a group (within the current table or matrix) or an entirely separate dataset.

So in answer to your queation, an aggregate function is not in fact required just to specify the dataset. The dataset is normally inherited from a parent control. If no aggregate function is specified for a cell that is aggregated (groupped) then the First function is applied by default.

Differentiate between fields of TWO Datasets in a single report

Dear ppl,

I have got 2 datasets D1 & D2 in a Report. How can i differentiate between the fields of these two.

e.g. I got Name field in both the datasets. So when i do =Fields!Name.Value in a textbox the report gives me error

How can i tell the report from which dataset to pick up the field ?

Regards

Nabeel

Is your textbox inside a Data Region (List, or Table), Or is it placed independently on the form?If is placed independently on the form you would have to reference it using an aggregate expression, using the following format.

=First(Fields!Name.Value, “Scope”)

Scope being then name of the Dataset.

Hope this helps!!!

Roberto Hernández-Pou
http://community.rhphconsulting.net

|||

Normally the text box will be nested in a list control which has a dataset property (when dataset has multiple rows).

Alterantively if you just put the textbox directly into the body of the report, you can use an aggregation function which takes the dataset name as the second parameter e.g.

=First(Fields!Name.Value, "DataSetName")

|||

Hello Nabeel,

Try the following syntax: =(Fields!Name.Value, "Put the name of your dataset here including the quotes")

Regards,
Worf|||

cheers guys..thanx for the help

||| why the aggregate function is required for the differentiation. cant it just differentiate with the name?|||

It's all to do with context.

Usually, you have an expression such as =Fields!x.Value or =Sum(Fields!x.Value) in a textbox of a matrix, table, or list. Each of these controls needs a dataset property to be specified. This sets the context and hence the dataset from which the field is sourced. This can be overridden within an aggregate function by explicitly specifying the scope of the aggregate. This can either be the name of a group (within the current table or matrix) or an entirely separate dataset.

So in answer to your queation, an aggregate function is not in fact required just to specify the dataset. The dataset is normally inherited from a parent control. If no aggregate function is specified for a cell that is aggregated (groupped) then the First function is applied by default.

Different collations on a single server

I have 2 sql server databases on a single server and each database working with a web site written with VBScript. One database with a collation of SQL_Latin1_General_CP1254_CI_AS and another databse with a collation of SQL_Latin1_General_CP850_CI_AS. Because of the server's windows character setting which is set to "English" the web site working with "CP850" displays NVARCHAR characters correctly, but the web site with "CP1254" displays some of the special turkish NVARCHAR characters not correctly. How can I solve this problem? Note : Query Analyzer displays the characters correctly on the database with a collation of "1254"Did you set Session.CodePage or the CodePage server-side directive to
tell IIS to encode outgoing strings as Unicode, and set the META CHARSET
property to tell the client browser to decode the page as Unicode?
Here's some simple ASP code that demonstrates how to round-trip Unicode
data from arbitrary languages from web browser->IIS->ASP->SQL and back
out to the browser:
http://groups.google.com/groups?q=bartd+asp&hl=en&lr=&ie=UTF-8&selm=JrIV8D
ZDDHA.2496%40cpmsftngxa06.phx.gbl&rnum=1
If this line wraps, try this shorter URL:
http://tinyurl.com/2zygn
Try this out -- it should work if you're using IIS 5 (Windows 2000) or
later. The only thing you should need to change is the connection string
(strCnn) in SQLNLS_UTF8.ASP to point to your SQL Server and provide a
valid login/password.
HTH,
Bart
--
Bart Duncan
Microsoft SQL Server Support
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no
rights.
Thread-Topic: Different collations on a single server
thread-index: AcPww2+N7ky8j5FoSwCPaUe5Q6LzGg==X-Tomcat-NG: microsoft.public.sqlserver.server
From: "=?Utf-8?B?bWFocnV0aQ==?=" <anonymous@.discussions.microsoft.com>
Subject: Different collations on a single server
Date: Wed, 11 Feb 2004 09:21:07 -0800
Lines: 1
Message-ID: <FCBF0A2A-02A5-447E-890F-5DD52D4A550D@.microsoft.com>
MIME-Version: 1.0
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
X-Newsreader: Microsoft CDO for Windows 2000
Content-Class: urn:content-classes:message
Importance: normal
Priority: normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
Newsgroups: microsoft.public.sqlserver.server
Path: cpmsftngxa07.phx.gbl
Xref: cpmsftngxa07.phx.gbl microsoft.public.sqlserver.server:328749
NNTP-Posting-Host: tk2msftcmty1.phx.gbl 10.40.1.180
X-Tomcat-NG: microsoft.public.sqlserver.server
I have 2 sql server databases on a single server and each database
working with a web site written with VBScript. One database with a
collation of SQL_Latin1_General_CP1254_CI_AS and another databse with a
collation of SQL_Latin1_General_CP850_CI_AS. Because of the server's
windows character setting which is set to "English" the web site working
with "CP850" displays NVARCHAR characters correctly, but the web site
with "CP1254" displays some of the special turkish NVARCHAR characters
not correctly. How can I solve this problem? Note : Query Analyzer
displays the characters correctly on the database with a collation of
"1254"

Different collations on a single server

I have 2 sql server databases on a single server and each database working w
ith a web site written with VBScript. One database with a collation of SQL_L
atin1_General_CP1254_CI_AS and another databse with a collation of SQL_Latin
1_General_CP850_CI_AS. Beca
use of the server's windows character setting which is set to "English" the
web site working with "CP850" displays NVARCHAR characters correctly, but th
e web site with "CP1254" displays some of the special turkish NVARCHAR chara
cters not correctly. How ca
n I solve this problem? Note : Query Analyzer displays the characters correc
tly on the database with a collation of "1254"Did you set Session.CodePage or the CodePage server-side directive to
tell IIS to encode outgoing strings as Unicode, and set the META CHARSET
property to tell the client browser to decode the page as Unicode?
Here's some simple ASP code that demonstrates how to round-trip Unicode
data from arbitrary languages from web browser->IIS->ASP->SQL and back
out to the browser:
http://groups.google.com/groups?q=b...F-8&selm=JrIV8D
ZDDHA.2496%40cpmsftngxa06.phx.gbl&rnum=1
If this line wraps, try this shorter URL:
http://tinyurl.com/2zygn
Try this out -- it should work if you're using IIS 5 (Windows 2000) or
later. The only thing you should need to change is the connection string
(strCnn) in SQLNLS_UTF8.ASP to point to your SQL Server and provide a
valid login/password.
HTH,
Bart
--
Bart Duncan
Microsoft SQL Server Support
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no
rights.
Thread-Topic: Different collations on a single server
thread-index: AcPww2+N7ky8j5FoSwCPaUe5Q6LzGg==
X-Tomcat-NG: microsoft.public.sqlserver.server
From: "examnotes" <anonymous@.discussions.microsoft.com>
Subject: Different collations on a single server
Date: Wed, 11 Feb 2004 09:21:07 -0800
Lines: 1
Message-ID: <FCBF0A2A-02A5-447E-890F-5DD52D4A550D@.microsoft.com>
MIME-Version: 1.0
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
X-Newsreader: Microsoft CDO for Windows 2000
Content-Class: urn:content-classes:message
Importance: normal
Priority: normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
Newsgroups: microsoft.public.sqlserver.server
Path: cpmsftngxa07.phx.gbl
Xref: cpmsftngxa07.phx.gbl microsoft.public.sqlserver.server:328749
NNTP-Posting-Host: tk2msftcmty1.phx.gbl 10.40.1.180
X-Tomcat-NG: microsoft.public.sqlserver.server
I have 2 sql server databases on a single server and each database
working with a web site written with VBScript. One database with a
collation of SQL_Latin1_General_CP1254_CI_AS and another databse with a
collation of SQL_Latin1_General_CP850_CI_AS. Because of the server's
windows character setting which is set to "English" the web site working
with "CP850" displays NVARCHAR characters correctly, but the web site
with "CP1254" displays some of the special turkish NVARCHAR characters
not correctly. How can I solve this problem? Note : Query Analyzer
displays the characters correctly on the database with a collation of
"1254"

Different Aggregation Function for Single Measure

BOL alludes to being able to set a different aggregation function for a measure for different dimensions/hierarchies. In the June CTP, the link in BOL is as follows:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/uas9/html/c359b4c1-9c3f-41bc-a585-de7c934e2c11.htm

It states that an aggregation function can be set on the measure (as the default, using the AggregateFunction property) in the Properties pane of the Cube Designer. Which is fine.

But, it also states that an aggregation function can be specified for a particular measure when aggregated along a specific hierarchy. The problem is, it doesn't state where this might be done in the Cube Designer and I can't seem to find any property setting or other setting that might lend itself to doing this.

Being able to specify a different aggregation function for a measure based on the hierarchy involved would be very useful. For example, a dimension with multiple date dimensions or hierarchies using different aggregation functions to apply slightly different additive or semiadditive aggregations.

Anyone know how to do this?

Thanks...

Dave Fackler

A way to do this is in the calculations for the cube. Set a scope (for your measure), set a scope for your dimension, the change the value of the calculation. For example:

where the aggregation method for [myMeasure] is SUM:

SCOPE [Measures].[myMeasure];
SCOPE leaves([Region]);
this = [Measures].[myMeasure] * 2;
END SCOPE;
END SCOPE;

Note: the specifics depend strongly on the aggregation effect you're trying to achieve. I've often found that I needed to approach the problem in reverse, to get the results I wanted.

Good luck.