Create Percentage Calculated Measure... Getting the current Dimension

Hello,

I created a cube for surveys and would like to create a measure for the percentage of people (surveys) that answered Q1, Q2, Q3, etc. I have the following calculated member:

CREATE MEMBER CURRENTCUBE.[MEASURES].[Percentage Answered Q2]

AS Case

// Test to avoid division by zero.

When IsEmpty

(

[Measures].[Surveys Count]

)

Then Null

Else ( [Q2].[Units].CurrentMember,

[Measures].[Surveys Count])

/

(

// The Root function returns the (All) value for the target dimension.

Root (), [Measures].[Surveys Count])

End ,

FORMAT_STRING = "Percent",

VISIBLE = 1 ;

Do I have to specify the dimension in the script (like [Q2].[Units].CurrentMember) ? That means I have to create one percentage calculation member for each of the questions (dimensions). Is there a way to get the current dimension?

I hope I explain my question clearly, please let me know if I can explain further.

Thank you very much,

Sincerely,

Annie

There is no such concept as "the current dimension". Any given cell in a cube is always defined by a coordinate of all attributes of all cube dimensions. From your description, it seems like you created one dimension per survey question. Then a coordinate in your cube space would consist of members from all question dimensions. You might have multiple measure groups in the cube, in which case a cube dimension may or may not relate to a given measure group.

In any case, the CurrentMember component in the tuple expression in your example is redundant and can be safely removed.

|||

Thank you so much for the prompt reply.

As I'm fairly new to MDX. Do you mean change the script to:

-

CREATE MEMBER CURRENTCUBE.[MEASURES].[Percentage Answered]

AS Case

// Test to avoid division by zero.

When IsEmpty([Measures].[Surveys Count]) Then 0

Else [Measures].[Surveys Count] /(Root (),[Measures].[Surveys Count])

End,

FORMAT_STRING = "Percent",

VISIBLE = 1 ;

-

This then gives an "#value" error.... Could you be a bit more specific?

Thank you so much,

Sincerely,

Annie

|||

The Root() function without argument returns a tuple containing members from all attribute hierarchies, including the measures hierarchy. The [Measures].[Surveys Count] component in your tuple will cause a duplicate hierarchy error.

Create new dimension member based on other members

I have a dimension named category which contains the following members:

O, A, B, C, Cd

Now I want to create a new dimension category (not necessarily in the same hierarchy named LowCaring and which should contain both O and A. How do I do this?

I already tried creating a calculated member with parent hierarchy the category but it doens't do what I need it to do.

Thanks in advance for any help or pointers;

Stijn Verrept.

dear rept,

Try to create a view in your SQL to get only the O and A, and add this view into your datasourceview of Analysis Services.

When create the cube add this object as dimension!

Helped?

regards!!

|||

Hi Pedro, thanks for your reply.

When I would do it in SQL then I need to calculate all the measures connected to that dimension as well?

I have for example a fact table occupation with

OC_Date

OC_Category

OC_Number

The OC_Date is connected to a time dimension and the OC_Category to the category. That table is already filled with facts so SSAS should be able to sum the OC_Number for O and A automatically.

Is that possible?

|||

Yes, It works...

the view you create works in Analysis Services as as other object.. you can define it as a dimension a works as the others.

In your case as you described it works! Try it!

Helped?

Regards!!

|||

Hi Pedro,

I'm trying as you suggested:

I created a new query which lists only the O and A, I added this one to the DSV. Then I created a new dimension which also deploys fine.

The trouble starts when I add this dimension to the cube, then I get this error:

Processing Measure Group 'Occupation' failed.

Start time: 21/07/2007 12:18:29; End time: 21/07/2007 12:18:30; Duration: 0:00:01
Processing Partition 'Occupation' failed. 1 rows have been read.
Start time: 21/07/2007 12:18:30; End time: 21/07/2007 12:18:30; Duration: 0:00:00
SQL queries 1
SELECT [dbo_Occupation].[OC_Number] AS [dbo_OccupationOC_Number0_0],[dbo_Occupation].[OC_HOID] AS [dbo_OccupationOC_HOID0_1],[dbo_Occupation].[OC_CAID] AS [dbo_OccupationOC_CAID0_2],[dbo_Occupation].[OC_Date] AS [dbo_OccupationOC_Date0_3]
FROM [dbo].[Occupation] AS [dbo_Occupation]
Error Messages 2
Errors in the OLAP storage engine: The attribute key cannot be found: Table: dbo_Occupation, Column: OC_CAID, Value: 3. Errors in the OLAP storage engine: The attribute key was converted to an unknown member because the attribute key was not found. Attribute Categorie of Dimension: Laagzorgbehoevenden from Database: WZM, Cube: Dossier DW, Measure Group: Occupation, Partition: Occupation, Record: 3.

Occupation is the fact table, Laagzorgbehoevenden is the new dimension with only O and A.

What did I do wrong?

|||

Occupation is your dimension or Fact?!

Regards

|||Occupation is my fact. Category is the dimension table with (O, A, B, C, CD, ...) and Laagzorgbehoevenden is the dimension table with only O and A, based on the query I make like you said.|||

And what is the factTable?

Link the Laagzorgbehoevenden to your factTable with uniqueID in datasourceview... did it?

Regards

|||

The fact table is occupation. When I link the laagzorgbehoevenden to occupation in DSV and try to deploy I get:

Warning 2 Errors in the OLAP storage engine: The attribute key cannot be found: Table: dbo_Occupation, Column: OC_CAID, Value: 3. 0 0

Which is normal because the fact table (occupation) contains values that aren't in laagzorgbehoevenden, because laagzorgbehoevenden only contains O and A. Categories contains them all.

I think this is the wrong way to go. Isn't there another easier way of getting 2 dimension members and creating a third that contains them both and sums the occupation values of those both?

|||

There are several ways to do this. Following PedroCGD's advice, create another column in the view you are using to build the Category dimension. You can do this by adding a derived column to the UDM or by modifying the underlying table. The definition for the new column would look something like this:

Code Snippet

CASE category

WHEN 'O' THEN 'LowCaring'

WHEN 'A' THEN 'LowCaring'

ELSE 'Other'

END AS CategoryType

Add the CategoryType as a new attribute hierarchy in the Category dimension. The two attribute hierarchies will act independently.

I hope I've interpreted the question correctly.

|||

Dear Friend,

Martin Mason
was more rapid than me! :-)

I hope I get it!!

If not post here, I will try to help until you get it!

Regards!!

|||

Thanks Martin,

This works great! A followup question, is it somehow possible to hide the Other value for the users (in the browser for example)?

Create dimension without member

Hi
Analysis manager wont let me create dimension unless fact table filed has value init. Other words, how do I create MT dimension(dimension without member)
thanksI'm confused. Is this really a dimension? Or is this a dimension that doesn't exist now, but will in the future? (i.e. you're rolling out a new type of product and you've created new product dimension, but the fact table has no corresponding records). Can you plug an 'unknown' attribute into your fact and dimension?

Let me know if I'm way off.|||Fact table has field called Due date which contains null values. When I create Due_date dimension, it won’t let me create, gives a message unable to count the members. I have to create dimensions out of all the fields in the fact table and send the cub to this person. Later this person will load the cub into his server and change data source and run the cub.
But my question is , If in his source table due_Date has null values(no data) , when this person runs the cube , it will give the error right?

Right now his source table due_date doen’t have values, but later he will had value into it.

He wants me to create each field in the fact table one dimension, doesn’t matter the fact table filed contains data or not? Can I do that?|||Is this 2000 or 2005? I just tried this in 2000 and it worked. No errors.|||It's 2000, you mean , it let you create a dimension with out a member.

Create Dimension Table from Fact Table!!

I have picked an exmple from this forum, to help me explain my current problem...

"I'm looking for a solution to import data from a flat file into an normalized data modell. To explain it a little simpler think about to following:

The Data Souce is a CSV-File with FirstName, LastName and Category. Sample data could be

Dirk; Bauer; sailing
Peter; Bauer; fishing
Marc; Bauer; reading

In my data modell I have defined the 2 tables "Person" and "Category":

Table "Person"
-
[PersonID] [int] IDENTITY(1,1) NOT NULL
[CategoryID] [int] NOT NULL
[FirstName] [nvarchar](50)
[LastName] [nvarchar](50)

Table "Category"
-
[CategoryID] [int] IDENTITY(1,1) NOT NULL
[CategoryName] [nvarchar](50)

Now I like to read my first row from the source and lookup a value for the CategoryID "sailing". As my data tables are empty right now, the lookup is not able to read a value for "sailing". Now I like to insert a new row in the table "Category" for the value "sailing" and receive the new "CategoryID" to insert my values in the table "Person" INCLUDING the new "CategoryID".

I think this is a normal way of reading data from a source and performing some lookups. In my "real world" scenario I have to lookup about 20 foreign keys before I'm able to insert the row read from the flat file source.

I really can't belief that this is a "special" case and I also can't belief that there is no easy and simple way to solve this with SSIS. Ok, the solution from Thomas is working but it is a very complex solution for this small problem. So, any help would be appreciated...

Thanks,
Dirk"

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=74752&SiteID=1

Could someone help me creating the dimension table?

Thanks!!

It's very common to derive dimensions from source data - that is the purpose of a data warehouse after all.

What you need to do is perform the dimension lookup with your source data. For values not found (New dimensions), you can run the records down the error output into a Derived Column derivation to create your new dimension table complete with keys, then merge them back into the flow to update the dimension tables. Your methodology will determine how this is done.

|||How about seperate packages for dimensions and facts. The dimensions get built first from the source data so there will always be a lookup match when building the fact|||That will work as well. Always transform dimensions before facts.

Wes|||

wesd wrote:

That will work as well. Always transform dimensions before facts.

Wes

That's what I would do.

|||Please mark this thread as answered, the op has the information he needs. Email me if you need more information.|||

I will Try!!

Thanks!!!