Showing posts with label existing. Show all posts
Showing posts with label existing. Show all posts

Friday, March 30, 2012

Named sets and Existing

I have a very large Account dimension (> 2,000,000 members). I would like to create a named set for the top most profitable accounts. I plan to use this named set in the SSRS Report Builder to compensate for its lack of aTop N feature. The problem is that the user can change the fiscal period and SSAS needs to reevaluate the set. Using Generate function won't work for performance reasons. So, I'm trying to use Existing to force the set generation. e.g.

CREATE SET CURRENTCUBE.[Top 5 Profitable Accounts]

AS

Order(TopCount(

(EXISTING [Account].[Account].[Account].Members, [Period].[Period].CurrentMember), 5, [Measures].[Profit]),

[Measures].[Profit], DESC);

However, this gives me "The Period hierarchy already appears in the Axis1 axis." error when I test the set with the following query

select non empty [Measures].[Profit] on 0,

non empty [Top 5 Profitable Accounts] on 1

from [RPM]

WHERE [Period].[Period].&[20030228]

and duplicated dimensionality error in the Report Builder.

Does anyone know how this could be implemented?

There are a couple of issues in the Named Set:

The error can be eliminated by not explicitly specifying [Period].[Period].CurrentMember with Existing.|||

Deepak,

Thank you for helping. True, the modified set doesn't error out. However, as you pointed out, it is not dynamic. It only works with the default time period which is where I started. In other words, EXISTING doesn't help here. Changing the query slicer to a different time period doesn't produce any results. It looks like we cannot change the context of standard named sets defined with CREATE SET in the cube script. This pretty much leaves me with no options to simulate TopN with the Report Builder.

Wednesday, March 7, 2012

My Table that uses aspnet foreign Keys wont display anything - CAUTION - Noob at work

I've created a new table in ASPNETDB.mdf called 'customerInfo'. Most of the fields are, for the moment, data that exists in one of the existing ASPNET tables eg Membership_UserId (primary key), Membership_Email, Users_UserName etc.; the list goes on. I have put a new column 'amountspent' - just to test that I can do what I think I can do, however the table doesn't display any data. Am I OK in thinking that I can use the aspnet data in my own tables and reference through foreign keys to them. I've made sure that the procedure that I used to put the table into a gridview on the webpage is OK as I did the same thing to the membership table and that displays all of its data. Can someone either explain the steps I need to make this happen correctly or point me in the direction of a noob type walkthrough.

Thanks

ps I've used several different ways to link the tables. First I tried building the relationships through the SqlMembershipProvider, created tables and added the foreign keys. Not sure of whether I'd done this correctly, I then used the diagram method which is pretty straightforward, however the result is the same. Surely now these tables are keyed to the UserId of aspnet_Users they should build and display a table of my existing test membership - even if the other fields, for the moment, are not carrying any data?

my sqlsp.log

this is the end of my sqlsp.log after sp3a install to existing SQL 200
11:03:54 Begin: SetupRegistry(
11:03:54 Register this installation of SQL Server with MDA
11:03:54 Completed registering this installation of SQL Server with MDA
11:03:55 Start Process...updating reg entries for S
11:03:55 No Server -> Tool
11:03:55 End: SetupRegistry(
11:03:55 Begin: PostSetupRegistry(
11:03:55 C:\PROGRA~1\COMMON~1\MICROS~1\SQLDEB~1\SQLDBR~1.EXE sqldbreg2.exe -RegServe
11:03:56 End: PostSetupRegistry(
11:04:07 You should now backup your master and msdb databases since this installation has updated their content
11:04:07 Action CleanUpInstall
11:04:09 Installation Completed with Errors (number of nonfatal errors: 1
But when I check @.@.version I am still on sp2. Did not see indication of what the nonfatal error was. Idea what I should be looking for?The last line says: 11:04:09 Installation Completed with
Errors (number of nonfatal errors: 1)
Your SQL SP# installation was not succesfull, please fix
the issue and re-install em
Thank you,
Saleem Hakani
>--Original Message--
>this is the end of my sqlsp.log after sp3a install to
existing SQL 2000
>11:03:54 Begin: SetupRegistry()
>11:03:54 Register this installation of SQL Server with
MDAC
>11:03:54 Completed registering this installation of SQL
Server with MDAC
>11:03:55 Start Process...updating reg entries for SP
>11:03:55 No Server -> Tools
>11:03:55 End: SetupRegistry()
>11:03:55 Begin: PostSetupRegistry()
>11:03:55 C:\PROGRA~1\COMMON~1\MICROS~1\SQLDEB~1
\SQLDBR~1.EXE sqldbreg2.exe -RegServer
>11:03:56 End: PostSetupRegistry()
>11:04:07 You should now backup your master and msdb
databases since this installation has updated their
content.
>11:04:07 Action CleanUpInstall:
>11:04:09 Installation Completed with Errors (number of
nonfatal errors: 1)
>But when I check @.@.version I am still on sp2. Did not
see indication of what the nonfatal error was. Idea what
I should be looking for?
>.
>