Determining Security: analysis

Showing posts with label analysis. Show all posts

Tuesday, March 27, 2012

Determine service pack version of Analysis Services using AMO

Hi,

Our product uses features that require Analysis Services SP2 to be installed. How can I tell if an Analysis Services server is running SP2 or higher using Analysis Services Management Objects (AMO)? I'm not concerned about the Operating System service pack edition, I'm concerned about the SP version of Analysis Services.

Thanks.

Figured it out. Use the Version property off the Server object.

Server svr = new Server();

svr.Connect(serverName);

Version minASVersion = new Version(MIN_AS_SERVICES_VERSION);

Version serverVersion = new Version(svr.Version);

if (serverVersion < minASVersion)

{

// error

}

|||

You can get this using ADOMD too:

http://geekswithblogs.net/darrengosbell/archive/2007/07/14/SSAS-Detecting-the-version-of-SSAS-on-a-given-server.aspx

sql

Determine performance issue

I read from a book and part of the comment, it mention this:
========================
After further analysis, we determined that more than
80 percent of the total resources being consumed-processor, memory,
and
input/output (I/O)-was involved in this conversion process.
========================
The conversion process in the comment is the conversion of datetime
format to another format.
QUESTION:
1. How can we determine the percentage of time was involve in
conversion process?
2. How can we check for it? Using profiler?
I'm interested to the method of checking performance problem, but have
no idea where to start with and what to check.
Any guideline?
Thanks.
wodoy.peter
One reliable method is to conduct controled tests to compare the performance
between using the conversion and not using the conversion. This may not be
possible in some scenario, and may be too expensive in some other scenarios.
Without such a comparison, I'm not sure how one can determine with certainty
that X% of resources is being consumed by a conversion step by looking at
perfmon counters or SQL Trace results.
Linchi
"wodoy.peter" wrote:

> I read from a book and part of the comment, it mention this:
> ========================
> After further analysis, we determined that more than
> 80 percent of the total resources being consumed-processor, memory,
> and
> input/output (I/O)-was involved in this conversion process.
> ========================
> The conversion process in the comment is the conversion of datetime
> format to another format.
> QUESTION:
> 1. How can we determine the percentage of time was involve in
> conversion process?
> 2. How can we check for it? Using profiler?
> I'm interested to the method of checking performance problem, but have
> no idea where to start with and what to check.
> Any guideline?
> Thanks.
>
> wodoy.peter
>
|||On Fri, 2 Feb 2007 14:12:01 -0800, Linchi Shea
<LinchiShea@.discussions.microsoft.com> wrote:

>One reliable method is to conduct controled tests to compare the performance
>between using the conversion and not using the conversion. This may not be
>possible in some scenario, and may be too expensive in some other scenarios.
Excellent point, just comment out the line and use the wall clock!
When possible ...

>Without such a comparison, I'm not sure how one can determine with certainty
>that X% of resources is being consumed by a conversion step by looking at
>perfmon counters or SQL Trace results.
Profiler can give you some pretty fine-grained results, if you turn
everything on. It also impacts performance, especially at those
levels.
J.

Determine performance issue

I read from a book and part of the comment, it mention this:
======================== After further analysis, we determined that more than
80 percent of the total resources being consumed-processor, memory,
and
input/output (I/O)-was involved in this conversion process.
========================
The conversion process in the comment is the conversion of datetime
format to another format.
QUESTION:
1. How can we determine the percentage of time was involve in
conversion process?
2. How can we check for it? Using profiler?
I'm interested to the method of checking performance problem, but have
no idea where to start with and what to check.
Any guideline?
Thanks.
wodoy.peterOne reliable method is to conduct controled tests to compare the performance
between using the conversion and not using the conversion. This may not be
possible in some scenario, and may be too expensive in some other scenarios.
Without such a comparison, I'm not sure how one can determine with certainty
that X% of resources is being consumed by a conversion step by looking at
perfmon counters or SQL Trace results.
Linchi
"wodoy.peter" wrote:
> I read from a book and part of the comment, it mention this:
> ========================> After further analysis, we determined that more than
> 80 percent of the total resources being consumed-processor, memory,
> and
> input/output (I/O)-was involved in this conversion process.
> ========================> The conversion process in the comment is the conversion of datetime
> format to another format.
> QUESTION:
> 1. How can we determine the percentage of time was involve in
> conversion process?
> 2. How can we check for it? Using profiler?
> I'm interested to the method of checking performance problem, but have
> no idea where to start with and what to check.
> Any guideline?
> Thanks.
>
> wodoy.peter
>|||On Fri, 2 Feb 2007 14:12:01 -0800, Linchi Shea
<LinchiShea@.discussions.microsoft.com> wrote:
>One reliable method is to conduct controled tests to compare the performance
>between using the conversion and not using the conversion. This may not be
>possible in some scenario, and may be too expensive in some other scenarios.
Excellent point, just comment out the line and use the wall clock!
When possible ...
>Without such a comparison, I'm not sure how one can determine with certainty
>that X% of resources is being consumed by a conversion step by looking at
>perfmon counters or SQL Trace results.
Profiler can give you some pretty fine-grained results, if you turn
everything on. It also impacts performance, especially at those
levels.
J.

Determine performance issue

I read from a book and part of the comment, it mention this:
========================
After further analysis, we determined that more than
80 percent of the total resources being consumed-processor, memory,
and
input/output (I/O)-was involved in this conversion process.
========================
The conversion process in the comment is the conversion of datetime
format to another format.
QUESTION:
1. How can we determine the percentage of time was involve in
conversion process?
2. How can we check for it? Using profiler?
I'm interested to the method of checking performance problem, but have
no idea where to start with and what to check.
Any guideline?
Thanks.
wodoy.peterOne reliable method is to conduct controled tests to compare the performance
between using the conversion and not using the conversion. This may not be
possible in some scenario, and may be too expensive in some other scenarios.
Without such a comparison, I'm not sure how one can determine with certainty
that X% of resources is being consumed by a conversion step by looking at
perfmon counters or SQL Trace results.
Linchi
"wodoy.peter" wrote:

> I read from a book and part of the comment, it mention this:
> ========================
> After further analysis, we determined that more than
> 80 percent of the total resources being consumed-processor, memory,
> and
> input/output (I/O)-was involved in this conversion process.
> ========================
> The conversion process in the comment is the conversion of datetime
> format to another format.
> QUESTION:
> 1. How can we determine the percentage of time was involve in
> conversion process?
> 2. How can we check for it? Using profiler?
> I'm interested to the method of checking performance problem, but have
> no idea where to start with and what to check.
> Any guideline?
> Thanks.
>
> wodoy.peter
>|||On Fri, 2 Feb 2007 14:12:01 -0800, Linchi Shea
<LinchiShea@.discussions.microsoft.com> wrote:

>One reliable method is to conduct controled tests to compare the performanc
e
>between using the conversion and not using the conversion. This may not be
>possible in some scenario, and may be too expensive in some other scenarios.[/vbcol
]
Excellent point, just comment out the line and use the wall clock!
When possible ...
[vbcol=seagreen]
>Without such a comparison, I'm not sure how one can determine with certaint
y
>that X% of resources is being consumed by a conversion step by looking at
>perfmon counters or SQL Trace results.
Profiler can give you some pretty fine-grained results, if you turn
everything on. It also impacts performance, especially at those
levels.
J.

Sunday, February 19, 2012

Design Question (Analysis Services or Integration Services)

Hi,

I have a design issue and not sure, which approach to follow. Iam not sure, if I have to use analysis services or integration services. But we would like to use price trending also, so probably analysis services could be an option.

We have a table which stores the Customer Items information. For making it easier, I will talk about customer 1 and item 1as shown in the table below:

CustomerID

CustomerCategoryID

RegionId

ItemID

Price

10.00

12.00

14.00

18.00

16.00

15.00

12.00

Now the Customers can belong to a single Category and single Region

Customer Categories are 6 (1 -6) and Category 7 indicates all Categories.

Regions are 6 (1-6) and Region 7 indicates all Regions.

Now the reports run from the following particular cube (under the assumption that analysis services will be used to build the following cube)

CustomerID

CustomerCategoryID

RegionID

ItemID

Price

LowPrice

AveragePrice

10.00

12.00

10.00

12.00

14.00

10.00

12.00

10.00

16.00

17.00

10.00

15.00

10.00

12.00

10.00

16.00

17.00

10.00

12.00

10.00

12.00

13.50

10.00

NULL

10.00

NULL

10.00

NULL

10.00

12.00

10.00

16.00

17.00

10.00

15.00

10.00

12.00

10.00

NULL

10.00

NULL

As you can see in the above table for customer 1 and item 1, the lowest and average price is calculated for every available combination for categoryid and regionid(the ones in red indicate all regions and all categories). This may not be the best example, but hope it will help in understanding the issue here. What is the best approach to design this one ? If I have to design the cube can anybody help me in getting started. I have a basic understanding of MDX queries and trying to get a hang of it.

Thanks in advance for your help.

-Prash

Hi,

This is the general dimension model and analysis server 2005 can take care of this very easily.

If you have seperate tables for the Customers, CustomerCategory, Region and Items, then start of by modelling these as dimensions in analysis server. The fact table which you have shown would have foreign key references to the dimenison tables.

If you dont have dimension tables, you can create them. Customers and CustomersCategory can be attributes of the same dimension.

You will have Price, LowPrice and AveragePrice as your measures in the cube.

start of by creating a DataDource, then a DataSourceView. Once this is ready, click on new cube, then select your fact and dimension tables, select your measures and click finish.

The DSV (DataSourceView) would identify the fact and dimension tables. you would need to create primary keys and then create relationships from the fact table to the dimension tables. (for customers, region and item tables)

Hope this is what you were looking for

Regards

|||

Thanks for the reply. I did go ahead and build the cube with Price, LowPrice and AveragePrice as measure.

But Iam having problem with the query below for building the above mentioned report. Any help on this would be really appreciated.

Select

{

[Measures].[Price]

,[Measures].[MinimumPrice]

,[Measures].[AveragePrice]

} on Columns,

{

(

Filter

(

NONEMPTYCrossJoin

([DimRegions].[DimRegions].Children,

[DimCustomerCategories].[DimCustomerCategories].Children,

[DimItem].[ItemName].Children,

[DimCustomer].[CustomerName].Children

)

,[Measures].[Count No Of Rows] > 1

)

} on Rows

From [Cube1]

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

Tuesday, February 14, 2012

Design Aggregations using XMLA

How can I automate aggregation design of a cube in Analysis Services

using XMLA.

I tried following XMLA.

I am not getting any examples for XMLA for Aggregation

design, please help me

===================================================

<DatabaseID>Adventure

Works DW</DatabaseID>

<CubeID>Adventure

Works</CubeID>

<MeasureGroupID>Internet

Sales</MeasureGroupID>

<PartitionID>Inernet_Sales_2001</PartitionID>

</Object>

</DesignAggregations>

</Command>

</PropertyList>

</Properties>

</Execute>

===================================================

Regards

Here's a couple of scripts i was mucking around with. i've split them into 1, 2 and 3 (ie numbered in the orderr in which i ran them). They may give you some pointers to where you want to go.

Sorry about the formatting, i've just pasted them in here and it seems to go on forever.....

Script 1 - Create Aggregation Design for Measure Group --

<!--

XMLA_Creates_Aggregation_Design_for_Measure_Group

This script adds the aggregation design to the measure group

-->

<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<ParentObject>
<DatabaseID>Aggregation Testbed</DatabaseID>
<CubeID>Adventure Works</CubeID>
<MeasureGroupID>Internet Sales</MeasureGroupID>
</ParentObject>
<ObjectDefinition><AggregationDesign>

<ID>AggregationDesign 1</ID>

<Name>AggregationDesign 1</Name>

<CubeDimensionID>Order Date Key - Dim Time</CubeDimensionID>

</Attribute>

<AttributeID>Calendar Year</AttributeID>

</Attribute>

<AttributeID>Calendar Semester</AttributeID>

</Attribute>

<AttributeID>Calendar Quarter</AttributeID>

</Attribute>

<AttributeID>Calendar Month Number</AttributeID>

</Attribute>

<AttributeID>Calendar Month Name</AttributeID>

</Attribute>

<AttributeID>Fiscal Year</AttributeID>

</Attribute>

<AttributeID>Fiscal Semester</AttributeID>

</Attribute>

<AttributeID>Fiscal Quarter</AttributeID>

</Attribute>

<AttributeID>Fiscal Month Number</AttributeID>

</Attribute>

<AttributeID>Fiscal Month Name</AttributeID>

</Attribute>

</Attributes>

</Dimension>

</Attribute>

<AttributeID>Calendar Year</AttributeID>

</Attribute>

<AttributeID>Calendar Semester</AttributeID>

</Attribute>

<AttributeID>Calendar Quarter</AttributeID>

</Attribute>

<AttributeID>Calendar Month Number</AttributeID>

</Attribute>

<AttributeID>Calendar Month Name</AttributeID>

</Attribute>

<AttributeID>Fiscal Year</AttributeID>

</Attribute>

<AttributeID>Fiscal Semester</AttributeID>

</Attribute>

<AttributeID>Fiscal Quarter</AttributeID>

</Attribute>

<AttributeID>Fiscal Month Number</AttributeID>

</Attribute>

<AttributeID>Fiscal Month Name</AttributeID>

</Attribute>

</Attributes>

</Dimension>

<CubeDimensionID>Delivery Date Key - Dim Time</CubeDimensionID>

</Attribute>

<AttributeID>Calendar Year</AttributeID>

</Attribute>

<AttributeID>Calendar Semester</AttributeID>

</Attribute>

<AttributeID>Calendar Quarter</AttributeID>

</Attribute>

<AttributeID>Calendar Month Number</AttributeID>

</Attribute>

<AttributeID>Calendar Month Name</AttributeID>

</Attribute>

<AttributeID>Fiscal Year</AttributeID>

</Attribute>

<AttributeID>Fiscal Semester</AttributeID>

</Attribute>

<AttributeID>Fiscal Quarter</AttributeID>

</Attribute>

<AttributeID>Fiscal Month Number</AttributeID>

</Attribute>

<AttributeID>Fiscal Month Name</AttributeID>

</Attribute>

</Attributes>

</Dimension>

<CubeDimensionID>Customer</CubeDimensionID>

</Attribute>

<AttributeID>Country-Region</AttributeID>

</Attribute>

<AttributeID>State-Province</AttributeID>

</Attribute>

</Attribute>

<AttributeID>Postal Code</AttributeID>

</Attribute>

<AttributeID>Education</AttributeID>

</Attribute>

<AttributeID>Email Address</AttributeID>

</Attribute>

<AttributeID>Gender</AttributeID>

</Attribute>

<AttributeID>Marital Status</AttributeID>

</Attribute>

<AttributeID>Occupation</AttributeID>

</Attribute>

<AttributeID>Phone</AttributeID>

</Attribute>

</Attributes>

</Dimension>

<CubeDimensionID>Product</CubeDimensionID>

<AttributeID>Product Name</AttributeID>

</Attribute>

<AttributeID>Product Line</AttributeID>

</Attribute>

<AttributeID>Model Name</AttributeID>

</Attribute>

<AttributeID>Subcategory</AttributeID>

</Attribute>

<AttributeID>Category</AttributeID>

</Attribute>

<AttributeID>List Price</AttributeID>

</Attribute>

</Attribute>

<AttributeID>Weight</AttributeID>

</Attribute>

</Attributes>

</Dimension>

<CubeDimensionID>Currency</CubeDimensionID>

<AttributeID>Currency</AttributeID>

</Attribute>

<AttributeID>Currency Code</AttributeID>

</Attribute>

</Attributes>

</Dimension>

<CubeDimensionID>Sales Reason</CubeDimensionID>

<AttributeID>Sales Reason</AttributeID>

</Attribute>

<AttributeID>Sales Reason Type</AttributeID>

</Attribute>

</Attributes>

</Dimension>

<CubeDimensionID>Internet Sales Order Details</CubeDimensionID>

<AttributeID>Sales Order Key</AttributeID>

</Attribute>

<AttributeID>Sales Order Number</AttributeID>

</Attribute>

<AttributeID>Sales Order Line</AttributeID>

</Attribute>

<AttributeID>Carrier Tracking Number</AttributeID>

</Attribute>

<AttributeID>Customer PO Number</AttributeID>

</Attribute>

</Attributes>

</Dimension>

</Dimensions>

</AggregationDesign>

</ObjectDefinition>

</Create>

-- End of script 1 :)

Script 2. - Add/link the aggregation Design to the Measure Group --

<!--

XMLA_Adds_Aggregation_Design_To_Partition

This script adds the aggregation design to the partition. It requires the aggregation design to have been

created for the associated Measure Group

-->

<DatabaseID>Aggregation Testbed</DatabaseID>

<CubeID>Adventure Works</CubeID>

<MeasureGroupID>Internet Sales</MeasureGroupID>

<PartitionID>Internet_Sales_914</PartitionID>

</Object>

<ID>Internet_Sales_914</ID>

<Name>Internet_Sales_914</Name>

<Name>LastOrderDateKey</Name>

</Annotation>

</Annotations>

<DataSourceID>AdventureWorksDW</DataSourceID>

<QueryDefinition>SELECT * FROM [dbo].[FactInternetSales] WHERE OrderDateKey <= '184'</QueryDefinition>

</Source>

<StorageMode>Molap</StorageMode>

<ProcessingMode>Regular</ProcessingMode>

</ProactiveCaching>

<AggregationDesignID>AggregationDesign 1</AggregationDesignID>

</Partition>

</ObjectDefinition>

</Alter>

-- END OF SCRIPT 2 :)

Script 3. - Run the designer to build the aggregation designs themselves (ie the aggregations, but not populated with data, this happens when you build the partition and/or cube).

<!--

XMLA_Runs_Aggregation_Engine_to_Determine_Aggregations

This script runs the aggregation engine that uses the supplied parameters to build an aggregation design

-->

<DatabaseID>Aggregation Testbed</DatabaseID>

<CubeID>Adventure Works</CubeID>

<MeasureGroupID>Internet Sales</MeasureGroupID>

<AggregationDesignID>AggregationDesign 1</AggregationDesignID>

</Object>

</DesignAggregations>

-- END OF SCRIPT 3 :)

Hope you find these of use.

Steve.

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

Tuesday, March 27, 2012

Determine service pack version of Analysis Services using AMO

Determine performance issue

Determine performance issue

Determine performance issue

Sunday, February 19, 2012

Design Question (Analysis Services or Integration Services)

Design Question

Tuesday, February 14, 2012

Design Aggregations using XMLA

Design advice for an education data warehouse

Determining Security

Blog Archive

About Me