I have a table with several million rows. Each row represents a user session. There is a column called user which is not unique. There can be multiple sessions per user. I want to use Analysis services to get me the additional properties per user. Example: How many users (unique!) had a session longer than x minutes. How is that possible without changing the database. Note: there is no lookup-table and I cannot create one.
What I am able of at the moment is to ask how many sessions were longer then x minutes.
views:
99answers:
2
+1
A:
Do you already have an Analysis Services cube on top of this table? If you do, you could make the User column into a Fact Dimension, then use the User dimension as the basis of the unique calculation.
What structure do you have in place already?
Edit: OK, so you've created a Fact Dimension of User, MDX for the calculated member could look like:
WITH MEMBER UserCount AS Count(Filter([User].[User].[User], [Measures].[YourMeasure] > 10)),
NON_EMPTY_BEHAVIOR = { [Measures].[YourMeasure] }
SELECT UserCount ON 0 FROM [Cube]
You could put that into the cube as a calculated member, see the calculations tab in the cube designer in Visual Studio.
You may need to change "Count" to "DistinctCount" depending on your setup.
Note that the "NON_EMPTY_BEHAVIOR " flag should be set correctly, I've assumed you have a measure based on the session length?
Meff
2010-05-12 14:22:19
Yes, I have a a cube already. This is a single table I want to analyze. How do I create the fact dimension?
Uwe
2010-05-12 15:13:46
@Uwe Just create a new dimension, and use the fact table as the source. It should prompt you for which attributes to use, select the one you're interested in (User). It should automatically fix up the dimension usage tab (Fact Relationship) for you.
Meff
2010-05-12 15:54:11
@Meff, thank you. I did that. With the result, that I could get some numbers based on each user. But as there are several million users in the table, I just want to know "how many of them" and not "who" had a certain property set to a certain value. Is that possible?
Uwe
2010-05-14 06:41:32
Meff
2010-05-14 07:21:50
I could not get this type of statement running as a calculated member in the cubes calculations tab in any way. It always said something about wrong syntax for 'WITH'. So I put the statement in the measureExpression property of the measure, but it seems like this is only supported in enterprise edition of sqlserver/Analysis services. So this won't work, will it?
Uwe
2010-05-14 09:00:55
Yoou'll need to use the Create calculated member syntax to add this calculation to the cube. The "With" syntax is for a query. You should be able to run Uwe's query in Management Studio and if you're happy with results then add it to cube.
Craig
2010-06-02 08:00:08
A:
It was much more simple than I thought. Adding a measure with distinct count on the user solved the problem.
Uwe
2010-05-17 07:58:41