Analysis services with non normalized table

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 l开发者_运维百科onger 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.

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?

It was much more simple than I thought. Adding a measure with distinct count on the user solved the problem.