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 Drill Through work fine in AS2005?

Hi,

I had designed several cube in AS2005. And I counfused by some problem of drill through.

1. The large amount of dimensions makes drill through broken?

Drill Through works fine in the cube that includes 20 dimensions. But, In the same cube, the amount of dimensions is increase to 25. The drill through works no response...

2. In some case, the drill through do not response correct row data.

A MDX syntax that response measure is 8 (In my project, that means there would be 8 row data in the fact table.).
And I use the same mdx syntax to drill through, that only response 1 row data!

Any idea? or it's the bug?

thanks,

Drillthrough is certainly very different in AS 2005 compared to AS 2000.

It returns you the rows at the deepest granularity of the measure group. If the rows in the measure group are for the same cell coordinate, then they will aggregate up to a value of 8 in 1 row.

If you need to see all 8 rows, then you should define a degenerate dimension (could also be ROLAP) which is at that deepest granularity -- e.g. at TransactionID. You can hide this dimension so normal cube browsing operations will not see this deepest granularity.

When you define the default drillthrough action for the cube you can specify this granularity attribute in the RETURN clause and fetch the data for the 8 unique rows.

Its difficult to say what may cause the different behavior when you move from 20 dimensions to 25. Perhaps looking at profiler would help to see what is happening. Also, you need to make sure that drillthrough is being done on a reasonable size of dataset -- drilling through on a cell that has millions of fact rows will perform very badly.

HTH,

Akshai

Does creating KPI require cube reprocess?

The Analysis Service help says that after creating a KPI you need to process the cube in order to browse it. However, that's not the behavior I'm seeing - it looks like the KPI becomes available immediately. What behavior should I be expecting here?

The Books Online is being incorrect in this case. You dont need to re-process your cube after creating KPI's.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Does anybody know how to create a cube with MDX?

I know how to use MDX for query, but i didn't find a way to create a cube with MDX. Can anybody help?
Thanks in advance
FisheyIf what you are asking is how to create Data Cubes, then the answer is that cubes are created using Analysis Manager after installing Analysis Services.

If your question is how to automate the creation of a cube so that users can do it, I don't think I have a good answer.

Finally, if your question is how to automate the update of a cube so that a job can do it on SQL server, there are DTS objects in DTS designer to do this for you.

And if I haven't answered your question, then I'll just go duck around this corner and hide for being so dull as to not understand the question.

;-)

Hugh Scott

Originally posted by fishey
I know how to use MDX for query, but i didn't find a way to create a cube with MDX. Can anybody help?

Thanks in advance

Fishey|||Thank you very much, Hugh.
Actually, I know how to create a cube in Analysis Manager and i also
can use DSO to create a cube in vb application. But now what i want to do is using MDX syntax to create a cube, just like using SQL script syntax to create a table. Do you know any workarounds about it?

Thanks again.

Fishey|||[peeks out from around the corner]

I'm sorry no, I have no clue. I should have figured out what you were really asking for from the context, but I guess I read your post wrong. I am very weak when it comes to MDX and data cubes. One day maybe I will learn...

The only thing that I can really offer is using the DSO object model and creating a shell around that...

Best of luck.

Hugh Scott

Originally posted by fishey
Thank you very much, Hugh.
Actually, I know how to create a cube in Analysis Manager and i also
can use DSO to create a cube in vb application. But now what i want to do is using MDX syntax to create a cube, just like using SQL script syntax to create a table. Do you know any workarounds about it?

Thanks again.

Fishey