Does SSAS allow implementation of SCD?

Can I implement a slowly changing dimension type 2 in SSAS. I am looking at creating an SSAS cube which can pull data directly from an operational OLTP database. The source database does not maitain history of changes for the dimensions, and I wanted to know if SSAS will help me keep that history by defining certain dimensions as a SCD. If so how do I define that rule. All tutorials I have read only skim on that topic and don't describe the steps/ways to define it in SSAS. Any help would be appreciated.

While you can use type 2 SCDs (and any other implementation of an SCD) in SSAS, it is not the thing that implements them.

You need to implement them in a Datamart/Datawarehouse and SSAS will then read them from there. What might be of interest to you is the Slowly Changing Dimension task in SSIS. It's not the quickest of components, but it does allow you to use a GUI to define the columns that you want to tracking history on.

does SQL use AWE memory whenever possible?

I've got a 32-bit server with 16GB memory. In SQL Server, AWE is enabled. Does SQL use AWE memory for everything possible? I've also got SSAS installed on that same box, and SSAS can't use AWE memory. So I want SQL to use AWE memory as much as possible to leave as much free for SSAS as possible.

As I understand it, when you set the max server memory setting for SQL Server, it prevents the sum of normal and AWE memory from going over that limit you've set. Is there any way to tell SQL server to use as much AWE memory as it wants, but be use as little normal memory as it can?

SQL Server uses AWEonly for data cache pages in the buffer pool. As far as I know this can't be changed (in any supported fashion).

Also, I could be wrong here, but I believe the 3 GB limit is per process, meaning you could have two individual processes that don't support AWE each using 3 GB on a 16 GB system - they wouldn't be limited to a grand total of 3 GB between them. Where AWE comes in is allowing a single process to use more than 3 GB by itself. I'm sure the OS guys can correct me if my assumptions about AWE are incorrect.
|||

We're on the right track to answering my question. Do all pages that it caches use AWE memory exclusively? (Does it use AWE whenever possible?) Will it ever cache pages using normal memory if AWE is on and there is still AWE memory available?

I've put a 1GB limit on SQL so as to leave plenty of room for SSAS. I'm wondering if removing that limit on SQL will cause it to use more normal memory, or just more AWE memory.

I'm pretty sure it's 3GB total across all processes, not per process.

|||Well, there's the buffer cache and the procedure cache. The buffer cache, which holds actual data, will use AWE if it's enabled. The procedure cache doesn't use that, but I don't believe it'll end up nearly as large as the buffer cache under normal circumstances.

As for whether or not non-AWE applications are limited to 3 GB each, or 3 GB total, I think we'll need a more authoritative answer from someone on that, since it has a significant effect on how to handle the situation. This KB article makes me think it's 3 GB each, but I'm not 100% certain: http://support.microsoft.com/default.aspx/kb/283037

To get an idea of how memory is being allocated, compare the Performance Monitor counter SQLServer:Memory Manager\Total Server Memory (KB) to the amount of memory that Task Manager reports (which doesn't include AWE allocations).
|||

If you set max server memory, it limits how much memory, total that the database engine can use, whether it is AWE or not. I have not heard of any way to try to force SQL Server to use AWE memory first.

If you are concerned about memory, and have 16GB of RAM, you should seriously consider running 64-bit, with the "Lock Pages in Memory" right granted to the SQL Server service account. That is assuming your processors are 64-bit capable (which they should be if they are less than about two years old). I have some good DMV queries here:

http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!526.entry

sql

documenting SSAS project

Hi

Is there an easy way of documenting a SSAS project i.e. producing a document that describes all the cubes its measure and related dimesions?

Thanks

Gary

Hi Gary,

Here's a SQL Server 2005 BI documentation tool which I've come across:

http://www.bidocumenter.com/Public/Default.aspx

>>

With BI Documenter you can...

Generate complete SQL documentation at the push of a button

Document SQL Server, Analysis Services, Integration Services and Reporting Services in the same documentation set

Capture metadata snapshots for historical analysis

Output HTML Help (*.chm) or plain HTML

Use either a graphical wizard or command line interface

Document SQL Server 2005!

Tell your own story using the built-in diagramming tool

>>

|||

I have tried the same product as Deepak describes. The product will help but the output is not suitable for business people nore end users.

It is a product that will help developers to consolidate information of their own work.

The missing part is graphical presentation of dimensions,measures and calculations that end users understand.

You will have to do this on your own.

I suggest that you buy a copy of "Microsoft Data Warehouse Tooolkit" .

HTH

Thomas Ivarsson

|||

Thanks for the suggestions. software looks pretty good.

Thomas - why do you suggest the book?

Thanks

Gary

|||

This book have several good examples of how to document your solution, from ETL to the Cubes. They can be understood by business users not only it-staff people.

HTH

Thomas

|||

Thanks alot

Will look into it.

G

Documentation

Hello,

I have a BI solution in SQL server 2005, is there a documentation tool to help me do the documentation for my project?

I use SSIS, SSAS and SSRS. I need documentation for the first two.

Thank you

http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/ssisvcs.mspx - SSIS

http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/ssasvcs.mspx - SSAS

http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/rptsvcs.mspx - SSRS

And SQL server 2005 books online too (updated Fe2007).

|||

Sorry, i must have explained wrongly.

I want to generate my solutions documentation, is there a way to do it using sql server 2005, or do i need a external tool?

If so, what tool?

Thanks