Mapping a string field to Boolean output in SELECT clause

Hello,
I am facing a problem in a SELECT clause which i cannot solve.
In my SQL table ("myTable") i have a few columns ("Column1", "Column2", "TypeColumn"). When I select different columns of the table, instead of getting the value of TypeColumn, i would like to get a boolean indicating whether its value is a certain string or not.
For example, the TypeColumn accepts only a number of selected strings: "AAA", "BBB", "CCC".
when i do a select query on the table, instead of asking for TypeColumn i would like to ask a boolean value of 1 if TypeColumn is "AAA" and 0 if TypeColumn is "BBB" or "CCC". Also, i would like to make this query while I am also fetching the other columns. And i would like to use one query to get all that. I thought something like thsi would work:

SELECT Column1 AS Col1, Column2 AS Col2, IF(TypeColumn = "AAA", 1, 0) AS Col3
FROM myTable

but this doesn't work in SQL 2005!
Is it possible to do something similar in SQL 2005 using one query only? i am trying to avoid multiple queries for this.

thanks a lot for your help!

Hi,

try this here:

SELECT Column1 AS Col1, Column2 AS Col2, CASE WHEN TypeColumn = "AAA" THEN 1 ELSE 0 END AS Col3
FROM myTable

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||It works!
Thank you, thank you, thank you!!!!!!!sql

many-to-many parent-child relationship in the dimension hierarchy

This novice would like your assistance to solve a problem. The goal is to build a Data mart for use in a cube. The cube would have a Service Fact related to the Dimension of Personnel and Assets. The Measures would be:

Provision of Assets by Departments

Availability of Assets by Departments or Service

Volume of Support Calls by Departments

The model has one major problem: a many-to-many parent-child relationship in the hierarchy of the Asset Dimension. The hierarchy within the Dimension is

Bundle

Service

Assets

And the many-to-many relationship resides between Assets & Service.I wish to know how best to handle this situation; create a bridge table or create a Service Dimension. All advice is welcomed.

What is the business process you are modeling in your fact table? The description of the measures is a little confusing to me.

Regarding the many-to-many relationship, you are saying that the fact table has a relationship to an asset, the asset belongs to multiple services, and a service belongs to a single bundle. Is that correct?

Also, what is the relationship between the fact and the service? Does a single fact record associate to a single service or does it associated to multiple services through the asset?

Thanks,
Bryan

|||The main objective is to create a cube on IT Support for performance reporting against SLAs, with a secondary objective being to expose the use of IT within the organisation. The high level categories of measures are: provisioning, availability, volume, and minutes. These would translate into measurement exposing the aggregated number of Assets provisioned; the percentage an Asset was available; the aggregated number of support calls received for an Asset; the minutes taken to close a support call on an Assest. Basically, sliced, diced and rolled up and down by organisation section, business unit, IT bundle and service. This is datamart design based on this information:

Code Snippet

Assets Dim -> Support Fact <- Personnel Dim
AssetID AssetID PersonnelID
Class PersonnelID Job
Service %Available Business Unit
Bundle Provisioned Assets Section
Call volume
Call minutes

The Dimensions have been model to the lowest level of granularity. The Personnel Dimension offers no problems with the hierarchy; a staff member only belongs to one job, business unit & section. This is not the case for Assets. All Assets are assigned to a member of staff. An Asset is a member of a class --mobile, pc, server,...--, one or many services and those services are member of one bundle.

"Regarding the many-to-many relationship, you are saying that the fact table has a relationship to an asset, the asset belongs to multiple services, and a service belongs to a single bundle. Is that correct?"

Yes. The IT Business Unit offers 5 bundles of services. Each bundle is comprise of mutually exclusive services. An example would be the End User Computing Bundle comprising of Desk Support, Printing & Fax, etc.

"Also, what is the relationship between the fact and the service? Does a single fact record associate to a single service or does it associated to multiple services through the asset?"

One or multiple through the asset. Each Asset is tired to at lest one service. Assets such as servers offer more than one service.

I appreciate your response and am happy to supply additional information.
|||

According to Kimball methodology, a fact table should represent a single business process. So in this data mart, you would have a fact table for the handling of service tickets, another one for provisioning, another one for asset uptime/availability, and so on and so on. You might bring all this data together in a report using conformed dimensions between the fact tables. I'd recommend taking a look into that. It will make your maintanance of the data much, much easier. (You can easily tie all this data together in your olap cube so your users won't have to jump between fact tables aka measure groups.)

When you do that, I think you will find that some facts relate directly to assets while others relate directly to the services.

Still, you will have assets providing services and need to model that relationship. I'd recommend building a service group table that ties together the services being offered. Then associate an asset with a service group. Here's a rough example:

Asset (AssetID, ServiceGroupID, ..., StartDate, EndDate)

ServiceGroup (ServiceGroupID, ....)

ServiceGroupServiceJunction (ServiceGroupID, ServiceID, ....)

Service (ServiceID, ....)

So, if you had a fact associated with an asset, you would be able to identify what services are provided by that asset. If you are working with Asset as a Type 2 slowly changing dimension, there would be different records for an asset in the Asset table based on the ServiceGroups it was associated with across time.

If you have a copy of "The Microsoft Data Warehouse Toolkit", this is covered on page 60. (I think this is covered in "The Data Warehouse Toolkit" as well but someone has borrowed by copy.)

Hope that helps,
Bryan

|||Bryan,

Thanks for your suggestions and insight. I will have a read of the books mentioned and most likely return with a few questions...hopefully your generosity will continue to provide answers.

Ian
|||

Assuming that you're using AS 2005, the many-many relationship between Assets and Services could be modelled, if separate Asset and Service dimensions are set up. There is 1 measure that isn't just additive, and hence may need further fields in the fact table: "the percentage an Asset was available". Generally, availability is a ratio like available to total time. Even in the simplest scenario, the numerator and denominator would be separately summed (say across Assets in this case), then their ratio taken. So, if the fact table has either "%Available" or "AvailableTime", and "TotalTime", the aggregate "%Available" could be computed.

The schema for AS 2005 could then be something like:

PersonnelDim SupportFact AssetDim Asset<->ServiceBridge ServiceDim

AssetID --> AssetID <-- AssetID

PersonnelID <-- PersonnelID Asset ServiceID --> ServiceID

Job AvailTime Class Service

Business Unit TotalTime Bundle

Section Provisioned

Call Volume

Call Minutes

There would be a Measure Group on SupportFact, with "sum" measures: AvailTime, TotalTime, Provisioned, Call Volume, Call Minutes.

["%Available] could then be defined as: AvailTime / TotalTime.

The Personnel and Asset dimensions would have a regular relation to the SupportFact Measure Group.

An intermediate Measure Group is then defined on the Asset<->ServiceBridge table, to which Asset and Service dimensions are related.

This would allow the Service dimension to have a many-many relation to the SupportFact Measure Group, via this intermediate MG.

Managing SQL database rights using AD

I’m new to SQL so maybe there is an obvious reference that I have overlook
ed
that someone can point me to.
The problem I am trying to solve is managing SQL database rights using AD.
To simplify my problem imagine I have a single SQL server (SQL 2000). I also
have an app that can be accessed by two different organizations. Within each
organization I have two sets of users: Casual-users that can see a limited
number of tables and then Power-users that can see all tables for their
organization.
The structure can be conceptualized as something like this in AD:
- MyDomain
- Org1
-- Org1 admins
-- Org1 Power-Users
-- Org1 Casual-Users
- Org2
-- Org2 admins
-- Org2 Power-Users
-- Org2 Casual-Users
I can use AD to set up the structure I need. However, I would like to use
this AD structure to manage the user’s database access rights. Ideally I
could add a new user somewhere in my AD defined Domain and they would
automatically have the correct database rights.
Questions:
- Is the scenario that I am describing possible to implement?
- If so is there a source of information someone can point me at?
Thanks,
-AnthonyAnthony
http://vyaskn.tripod.com/sql_server...t_practices.htm --sec
urity
best practices
Also , you may want to look into a database role to manage users rights
"Anthony" <Anthony@.discussions.microsoft.com> wrote in message
news:BEDEB701-EF66-4827-8D3A-F79F6C7EA284@.microsoft.com...
> Im new to SQL so maybe there is an obvious reference that I have
> overlooked
> that someone can point me to.
> The problem I am trying to solve is managing SQL database rights using AD.
> To simplify my problem imagine I have a single SQL server (SQL 2000). I
> also
> have an app that can be accessed by two different organizations. Within
> each
> organization I have two sets of users: Casual-users that can see a limited
> number of tables and then Power-users that can see all tables for their
> organization.
> The structure can be conceptualized as something like this in AD:
> - MyDomain
> - Org1
> -- Org1 admins
> -- Org1 Power-Users
> -- Org1 Casual-Users
> - Org2
> -- Org2 admins
> -- Org2 Power-Users
> -- Org2 Casual-Users
>
> I can use AD to set up the structure I need. However, I would like to use
> this AD structure to manage the users database access rights. Ideally I
> could add a new user somewhere in my AD defined Domain and they would
> automatically have the correct database rights.
> Questions:
> - Is the scenario that I am describing possible to implement?
> - If so is there a source of information someone can point me at?
> Thanks,
> -Anthony
>|||Hi Uri - Thanks for the pointer. However, it still isn't clear to me what I
need to do.
What I'm really trying to do is manage the database roles through AD.
Ideally I would like to use Windows Authentication to access the SQL server
and then have that same set of Windows credentials automatically define the
user’s rights within the database.
In other words I don’t want to administrate each individual user’s right
s at
the database. Instead I want to set up my SQL server to say any member of my
casual-users group can edit table X. Then I would use AD to define what user
s
are in the casual-users group.
Thanks,
-Anthony
"Uri Dimant" wrote:

> Anthony
> http://vyaskn.tripod.com/sql_server...t_practices.htm --s
ecurity
> best practices
>
> Also , you may want to look into a database role to manage users rights
>
>
> "Anthony" <Anthony@.discussions.microsoft.com> wrote in message
> news:BEDEB701-EF66-4827-8D3A-F79F6C7EA284@.microsoft.com...
>
>|||At a high level, you would add the two Windows groups as
logins to SQL Server. You would add these two logins you
just added as users in the database. You would set the
appropriate permissions on these windows groups you just
added as database users. Moving the users in and out of the
AD groups at the AD level then flows down to the members of
the Windows group that you just added as logins (and then
database users). If you user is added to the AD group that
has the login and permissions in the database, that user
gets the login and permissions in the database based in
their membership to the windows. The windows group has the
permissions on SQL Server so moving users in and out of that
Windows group is what would control their access to SQL
Server and the database.
-Sue
On Mon, 13 Feb 2006 12:41:22 -0800, "Anthony"
<Anthony@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Hi Uri - Thanks for the pointer. However, it still isn't clear to me what I
>need to do.
>What I'm really trying to do is manage the database roles through AD.
>Ideally I would like to use Windows Authentication to access the SQL server
>and then have that same set of Windows credentials automatically define the
>users rights within the database.
>In other words I dont want to administrate each individual users rights a
t
>the database. Instead I want to set up my SQL server to say any member of m
y
>casual-users group can edit table X. Then I would use AD to define what use
rs
>are in the casual-users group.
>Thanks,
>-Anthony
>
>"Uri Dimant" wrote:
>