Determine Processor Architecture for SQL Server 2005

Determine Processor Architecture for SQL Server 2005

I like the new features in SERVERPROPERTY but there's a huge, glaring oversight in my opinion: it doesn't return the processor architecture. I'm trying to write a detailed server info collection script and (1) use only TSQL, and (2) have it return the processor architecture. Here's what I have so far:

SELECT
SERVERPROPERTY('InstanceName') AS [InstanceName]
, SERVERPROPERTY('ServerName') AS [ServerName]
, @.@.VERSION AS Version
, CASE
WHEN CHARINDEX('Intel X86',@.@.VERSION)<>0 THEN '32-bit'
WHEN CHARINDEX('Intel IA-64',@.@.VERSION)<>0 THEN '64-bit'
-- WHEN CHARINDEX('x86-64',@.@.VERSION)<>0 THEN '64-bit'
-- WHEN CHARINDEX('AMD64',@.@.VERSION)<>0 THEN '64-bit'
-- WHEN CHARINDEX('WOW64',@.@.VERSION)<>0 THEN 'WOW64'
-- WHEN CHARINDEX('AMD64',@.@.VERSION)<>0 THEN 'WOW32'
ELSE 'Unknown'
END AS ProcessorArchitecture

The problem is that I can only include the architectures that I've worked on (X86 and IA-64). The documentation does nothing to give us any clue what the values from @.@.VERSION will possibly return. I've made some guesses (in comments) but I haven't installed these so I'm not positive they are the right values returned by @.@.VERSION.

Anyone have confirmation of other architectures returned by @.@.VERSION? Or, even better, does anyone have a better TSQL-only way of returning this info?

Thanks!

Scott Whigham

Take a look at xp_msver.|||

xp_msver is not really any better/different than @.@.VERSION, is it? Actually I like @.@.VERSION better since I can use it in a query along with the other SERVERPROPERTY properties. Thanks though :) And it's weird that xp_msver and @.@.VERSION report the architecture using different values. That's a big incongruous to me.

Anyone else have other values or can confirm the values I posted?

Determine Processor Architecture for SQL Server 2005

I like the new features in SERVERPROPERTY but there's a huge, glaring oversight in my opinion: it doesn't return the processor architecture. I'm trying to write a detailed server info collection script and (1) use only TSQL, and (2) have it return the processor architecture. Here's what I have so far:

SELECT
SERVERPROPERTY('InstanceName') AS [InstanceName]
, SERVERPROPERTY('ServerName') AS [ServerName]
, @.@.VERSION AS Version
, CASE
WHEN CHARINDEX('Intel X86',@.@.VERSION)<>0 THEN '32-bit'
WHEN CHARINDEX('Intel IA-64',@.@.VERSION)<>0 THEN '64-bit'
-- WHEN CHARINDEX('x86-64',@.@.VERSION)<>0 THEN '64-bit'
-- WHEN CHARINDEX('AMD64',@.@.VERSION)<>0 THEN '64-bit'
-- WHEN CHARINDEX('WOW64',@.@.VERSION)<>0 THEN 'WOW64'
-- WHEN CHARINDEX('AMD64',@.@.VERSION)<>0 THEN 'WOW32'
ELSE 'Unknown'
END AS ProcessorArchitecture

The problem is that I can only include the architectures that I've worked on (X86 and IA-64). The documentation does nothing to give us any clue what the values from @.@.VERSION will possibly return. I've made some guesses (in comments) but I haven't installed these so I'm not positive they are the right values returned by @.@.VERSION.

Anyone have confirmation of other architectures returned by @.@.VERSION? Or, even better, does anyone have a better TSQL-only way of returning this info?

Thanks!

Scott Whigham

Sorry for the double post - the forums reported an error when I clicked "Post" so, of course, I posted again!

Design Question

Hi,

Iam having problem designing a proper solution for the current architecture, we have for a web application.

I would like to make use of Analysis Services, but not sure how to....

These are the typical course of events, which happen :

1) Data is uploaded into a maintenance database

Here during the data upload, a lot of calculations take place and some tables which contain the result of calculations are altered/populated. Usually, this takes hours to take place because of the number of rows being updated, deleted and added.(Usually in millions)

Scenario:

previous data is deleted (not all, only the required)

the tables corresponding to the uploaded data are modified(around 20 tables)

calculations are perfomed on the uploaded data

the tables corresponding to the calculations are updated.

2) The maintenance database is replicated to the production database.

This process is a pain in the neck and hence would like to use to analysis services.But Iam not sure of how to do it. In which direction should I proceed?Do I need a datawarehouse to perform the complex calculations ? Do I need to maintain separate databases, one for the calculations and one for production.

Thank you

Prash

Do you need to replicate back the calculations to the production database in order to support you webb application or are people running queries for pure analytic needs?

This can be reformulated as "are you running reports/analytic applications on your production database?

If so, I would recommend you to build a data warehouse/datamart.

What SSAS2005 can help you with is to aggregate information fast from the records in your data warehouse/dm. It can also help you with complex calculations because MDX(the query language that this product use) is stronger and requires less code than using TSQL/Stored procedures.

HTH

Thomas Ivarsson

|||

Thanks for your reply.

Do you need to replicate back the calculations to the production database in order to support you webb application or are people running queries for pure analytic needs?

Since all the calculations are done and stored in specific tables for the Reports, I just need to copy the database to production. No further calculations are involved.

Pardon me for my questions (Iam a beginner). Say, I have a dataware house which is built using the traditional snowflake/star schema model. Then I would assume that these steps need to be taken.

a) Built the relational database, such that no calculations are done

b) Built the dataware house/datamart and then perform the calculations(complex calculations) using the SSAS2005 by deploying the necessary cubes

c)Then, I could use reporting services to connect to the cubes for generating the reports.

If this is the procedure, then how do I connect my dataware house and the relational database(Integration services ?). Can you throw some light on the procedure, I need to follow.

Once again, thank you for your help.

-Prash

|||

Please see my comments:

a) Built the relational database, such that no calculations are done

You build a staging area which are tables that are only used for pumping data from your production system. The connection ,between your source system and the staging area, and the pumping of data is done by SSIS. You empty all the tables in staging area every time that you extract new source data. From the staging area you then build SSIS packages to move, clean and(or aggregate data.

b) Built the dataware house/datamart and then perform the calculations(complex calculations) using the SSAS2005 by deploying the necessary cubes

You can make some simple calculations in the data wareshouse for performance reasons. The rest is correct.

c)Then, I could use reporting services to connect to the cubes for generating the reports.

Correct. The only problem with reporting services is that it do not have the flexibility of a OLAP-client. Excel 2007 will support all functionality in SSAS2005. Reporting Services is best for standard reports.

If this is the procedure, then how do I connect my dataware house and the relational database(Integration services ?). Can you throw some light on the procedure, I need to follow.

Yes. You will use SSIS for this. Have a look at http://www.msftdwtoolkit.com/ and especially the book" The Microsoft Data Warehouse Toolkit".

HTH

Thomas

|||

Thank you. That's enough for me to get started. Can you suggest me any tutorials for building a dataware house depending on the existing relational database.

Cheers

Prash

Design advice for an education data warehouse

I’m not sure whether this ought to be in the Architecture or

SQL Analysis Services forum.I am after

some initial advice about a suitable structure for a data warehouse which will

be used to build OLAP cubes AS2005.

I work in a county education department.Each year we create/update a set of reports

and procedures which provide schools with online access to many of the

statistics for their school compared to district wide and to national targets,

etc.

After this year’s, which is my second at creating these

report/data, I can’t help but think that the whole thing is screaming out for a

Data Warehouse and Cube data to report on.

I may be wrong on that, as I’m still only doing the learning regarding

AS2005 and haven’t touched a previous version of it.I’m currently working through this book:

Microsoft SQL Server 2005 Analysis Services Step by Step - ISBN

0735621993.But I’m willing to bet that

this should be the way forward.Not only

for the actual schools viewing the data, but also for all those staff who work

at the county level and have to inspect/report on these schools and the

county’s performance.The only thing is

that most examples are stated within companies which have sales as a primary

measure, however I’m not sure education grades, targets, etc fall into the same

scheme.So I can’t use an example

structure to help get me started.

It isn’t something that I’ve got management backing for yet,

as it’s still pretty much just my own and a colleagues’ thoughts at the

moment.Also until I know more about it

myself then I wouldn’t feel comfortable taking my case to management.I feel I will be better to create a simple

version to demonstrate and show the benefit and power of such a system, to

those who will give the backing to the project.

The main central application, which is used in the county,

contains most of the information needed.

And several other systems also contain additional data which would be

useful.All based around a

Unique_Pupil_ID.

Current transactional database

The main transaction database I would be taking data from

has a main student table, then it has a student_results table.The student table also has many lookup type

tables related to ethnic origins, deprivation codes relating to income/student

address, etc.The kind of things that

would be useful when doing analysis on students.As well as at a more broad level, being able

to compare schools to each other at various subjects/overall/etc.

So the student table contains the information about the

student, but not results data.

The results table data is similar to below, but obviously

with more to it than just these fields:

Student_ID

Subject_ID

Grade

GradePointsEquivalent

ExamYear

Then there is obviously a lookup for the subject name.

There are also things like average/points/score per student/year

which aren’t a subject score, but more of a calculated field.So where ought that data to be located table-wise?

There would be a lot more to a total DW solution for this

environment, but I wanted to start with the basics so I have somethign to demonstrate.

Can anyone offer my any advice regarding this? and a possible design structure for the warehouse with regards to which ought to be fact or dimension tables.

Or do you think I'm barking up completely the wrong tree thinking that this would be a good solution?

Thanks for taking the time to read this less than short post.

Kind regards,
David

Hello. I have searched on the subject "education data warehouse" and found some suppliers and solutions in this area.

You design is a good start. A student table, with facts about the student, and a fact table with each students results, by each test or whatever. You will also need a tiem dimension, a test dimension(if these tests are general). Perhaps a school and a teacher dimension can enhance the analytic value of your solution. Students change schools and teachers and teachers change schools.

In the fact table you will normally only put base measures that can be used for calculations. You do the calculations in a cube.

Your problem have a lot in common with analysis when you try to see customer profiles and their buying patters, so you can have some help with sales examples.

HTH

Thomas Ivarsson

|||Thanks for your reply Thomas.

This was my initial thought on the design (although there are a fair few columns that I will not need in the final version, which are included in that diagram at the moment). See link below.

>>Structure<<

What do you think? Anything that jumps out at you regarding it?|||

Hello. The structure is fine for a data warehouse. If you build a datamart on top of this, by using views or new tables I recommend to reduce the number of joins in your snowflake.

Have a look here for more design tips(www.kimballgroup.com)

Regards

Thomas Ivarsson

|||I have made them into more of a star schema that a Snowflake and that has certainly helped.

However, I have a new issue. My understanding of OLAP & AS2005 is still growing, so it's possible I've got the wrong idea here, but...

The student grades are not always numeric, in many of the subjects they are graded A-G etc. They all have points equivalent, but it would be good to look at the grades too.

Common requests about the data are how many students achieved 5 A-C grades or 5 A-G etc. And other bits of analysis that are done around grade.

So some of the data needs to be analysed as stated here and some by numeric points. Can anyone help me as to how I could structure this part of it or achieve such a result?

I'm at a bit of a loss as to how.

Thanks,
David|||

You can used named members/sets for A-C and A-G type grades. For numeric data, you can either create a band dimension or use discretization methods in SSAS. You can search for these techniques in Books online.

One other point in your structure, for languages, you can create one physical dimension and use 'role playing dimensions'.

|||

Hello again. Build a separate dimension table for the grades(A-G), make a foreign key to the fact table and add a measure, called NumberOfGrades to the fact tables.

In this way you should be able to use sum as the aggregation method. This measure can then tell you how many (A-G) grades a student, teacher, school (and so on) that exist in the fact table.

HTH

Thomas Ivarsson