Saturday, April 2, 2022

OLAP Cubes problem

OLAP Cubes problem

Hi,

I have a fact table where two of its columns refer to the same dimension. I wanted to analyze the movement between these two columns.

Example:

columns:

ID region1 region2
1 tor ny
2 tor tor
3 tor miss
4 miss miss
5 miss miss
6 miss tor
7 ny miss
8 ny ny
9 ny ny

My cube should look like this:
region2
region1 tor miss ny
tor 1 1 1
miss 1 2
ny 1 2

What do i need to do?

Thanks a lot for all your help.


Reply:

Posting this on the Analysis Services forum should be step 1.


------------------------------------

Creating a dimension based on uniqueidentifier

Is it possible to base a dimension on a uniqueidentifier?  My company uses uniqueidentifiers for all primary keys in our tables.  However, I am now trying to create a parent-child dimension in Analysis Manager using one of our tables but I always get the error

Unable to count the members of the 'PK' level.

Unable to open the record set.

Error: The count-unique aggregate operation cannot take a uniqueidentifier data type as an argument.

Any suggestions?

Reply:
Note:  this is in AS 2000. 

Anyone know if this is possible in 2005?

------------------------------------

Connecting to Analysis Services 2000

Hi,

I am facing a problem to connect to AS 2000 (Analysis Services) on remote machine. I  have built a DLL "connection.dll" on .net framework using ADOMD.net libraries. I am exporting some functions in  "connection.dll" library (which is .net dll ) to connect and read data from  AS 2000.

Now problem is that whenever I try to connect to remote machine using "connection.dll" by calling it through other dll called "libReader.dll" (which is a regular C++ DLL), it gives error "database 'Foodmart 2000' does not exist'(Foodmart 2000 is a sample database and gets deployed with AS2000 by default). However connection is successful if we are connecting to local AS 2000.

The following scenario is not working:-

 libReader.dll -------------------> (Connect (a,b,c) - Function call) -----------------> connection.dll --------------------------> ADOMD.net ---------------------> AS 2000 (on Remote m/c)

Now whenever I use "connection.dll" in Win32 App "Disp.exe" for connecting to remote machines, It is not giving any error and is able to connect to AS 2000 -> FoodMart 2000 database.

The following scenario is working:-

Win32 Console App --------------------> (Connect (a,b,c) - Function call) -----------------> connection.dll --------------------------> ADOMD.net ---------------------> AS 2000 (on Remote m/c)

The calling mechanism should as described here.

In both cases "connection.dll" is very same still it is not able to connect to AS 2000 server if called from "libReader.dll" on other hand it is not giving any error if called from W32 app. Could you please let me know if I am missing anything or you would like to have more inputs?

Thanks
neoonwheels

SQL Server 2005 has shipped!

This morning at 9 AM in shiproom, we did official signoffs for SQL Server 2005!

SQL Server 2005 is in the bag!


Reply:
Well Done!

------------------------------------
Reply:
That is very exciting news! :)

------------------------------------
Reply:
Congratulations !
I am eager to get it going.

Does it require to uninstall June CTP / Sept CTP before installation?

thanks,
Nitesh

------------------------------------
Reply:

------------------------------------
Reply:
Muy buenos dias
con quien tengo el gusto
 Leonardo Rodriguez wrote:

------------------------------------

Pivot table published in a web page : Error (Data mining)

Hello,

I've created an OLAP cube in SQL server 2005 Analysis Services, i browse it successfully, and i was able to use pivot table in Excel 2000 with no problems. But when i try to save the result in a web page in order to publish it in a html page, i get this error:

Impossible de connecter la liste de tableau croisé dynamique à la source de données "DataBase". Pour  plus d'informations sur la source de données, consultez le créateur du fichier.
0x80004005: "Error (Data mining): Either the user, "My profile", does not have permission to access the referenced mining model, "DataBase", or the object does not exist."


I used to publish the results in a web page using OLAP services for SQL Server 2000 with no problems.

Any help is appreciated.

Regards,
Fadoua

calculations scripts for aggregating on a dimension with several hierarchies

Hi everybody,
I am using the September SQL Server 2005 CTP version.
I have a cube with five dimensions and several measures. I want to aggregate all the measures except one through the "sum" function along all the dimensions.
On the contrary I have a measure (called [Valore]) which I want to aggregate with the Sum function along all the dimensions except one dimension called [DomMese]. Along all the hierarchies of this dimension, in fact, I would like to aggregate that measure using the firstchild (or, even, using "average").
I tried to add in the Calculations script the following piece of code, using the "Scope" statement:

SCOPE ([Measures].[Valore]);

  /*"DomMese" dimension has to hierarchies: [Hierarchy] and [Hierarchy1]*/
   SCOPE([DomMese].[Hierarchy].allmembers);

 /*"Hierarchy" hierarchy has three levels, from highest to lowest: [Livello03]->[Livello02]->[Dom Mese], where the [Dom Mese] is the attribute with Key Usage*/
        SCOPE     ([DomMese].[Hierarchy].[Livello02].members);

            this =      [DomMese].[Hierarchy].CurrentMember.firstchild;    

        END SCOPE;

        SCOPE     ([DomMese].[Hierarchy].[Livello03].members);

            this =      [DomMese].[Hierarchy].CurrentMember.firstchild; 

        END SCOPE;

    END SCOPE;

   SCOPE ([DomMese].[Hierarchy1].allmembers);

/*"Hierarchy1" hierarchy has two levels, from highest to lowest: [Livello01]-> [Dom Mese], where the [Dom Mese] is the attribute with Key Usage*/
        SCOPE ([DomMese].[Hierarchy1].[Livello01].members);

            this = [DomMese].[Hierarchy1].CurrentMember.firstchild;

        END SCOPE;

   END SCOPE;

END SCOPE

 

The results I can see after processing are not correct, in fact I get the right aggregated values only for the second hierarchy (Hierarchy1), whilst I loose the right results for the first Hierarchy, which is aggregated using the Sum functions.

If I put only the following code in the calculations script (I cut the part related to hierarchy [Hierarchy1], I get the right results for the first hierarchy [Hierarchy], but results completely wrong for the other Hierarchy (neither FirstChild, neither Sum, but some wrong and repeated aggregated values and some empty aggregated values)

SCOPE ([Measures].[Valore]);

    SCOPE([DomMese].[Hierarchy].allmembers);

        SCOPE     ([DomMese].[Hierarchy].[Livello02].members);

        this =    [DomMese].[Hierarchy].CurrentMember.firstchild;    

        END SCOPE;

        SCOPE     ([DomMese].[Hierarchy].[Livello03].members);

        this =    [DomMese].[Hierarchy].CurrentMember.firstchild; 

        END SCOPE;

    END SCOPE;

END SCOPE

It seems I am not using the right way to aggregated through several hierarchies, can anybody help me?

I would appreciate it very much. Thank you.

 

Excel Pivot Tbl-AS2005

Hi,

I have a cube that I created in AS2005, I have a hierarchy and drillthrough action defined.  When I create a pivot table based on that cube, I can not drill down to the next level.  My hierarchy is based on Dimension Facility. Facility State is root level, facility zip is next level. Can anyone lend a hand?
thanks!

aggregations on cube dimensions

Hi everybody.
I am trying to set up a cube (in sql server 2005 Sep CTP) where one of the measures has to be aggregated through the "Min" function.
If I set up the "AggregationFunction" property of that Measure in the Cube to "Min" and process it, it just collects the maximum value from the records of my fact table for each cell of the cube, and then aggregates with the "Min" function all the hierarchies of all the dimensions, but this is not what I need.
In fact I should collect through "sum" the records at atomic level, and still aggregate through "sum" along all the hierarchies of all the dimensions except one, where I would like to aggregate on all the hierarchies of that dimension through the "Min".
Which is the best way to achieve such a results? Should I use the CalculationScripts? If yes, could someone write me a sample code? In fact I could not find any examples helping me to achieve this goal...
Thanks you very much.

Using a cube as a data source

Hi all:

Is there a way in SSAS 2005 to create a cube/measure group from another cube/measure group ?

Thanks.

Suranjan

how to connect sql server 2005 cube using visual studio 2005

May i know how to connect sql server 2005 cube using visual studio 2005 ?
May i have some examples and source code to solve this problem ?

Thanks a lot.

Analysis Services Project Template Failed installation... a "Project Template" issue

I installed SQL 2005 AS (Analysis Services Proj.) and it's installation failed on the moment of adding VS2005 support therefore it failed to properly add the project template resources into VS.  I remove and reinstalled VS2005 and SQL2005 various times (including all related resources and directories) trying to get the issue resolved and still I am getting the same issue.

I am using another approach now, how can I setup the SQL 2005 integration tools into VS2005?

When I try to create a new AS project it tells me that it can't find the ".pwproj" file and shows me the path where it is located, still even that the file is actually where it is trying to find it.  My guess is that project template or integration package installation didn't complete it is broken and needed resources are not set appropiately.

Again, how can I reran the SQL VS2005 integration tools?
 
If this is the wrong group to ask this question, can you tell me where I could get my answer?

Analysis services: Locking partitions

Table A in SQL Server 2005 is split in partitions where the oldest partition will be deleted when the new partition fills up. Data aggregation using Analysis Service is powerful, but when syncronizing against the database, source data from the deleted SQL Server partition/file group is removed and aggregations in Analysis Service will be updated.

Is there any possibility to avoid updating the data for certain partitions and lock the data in partitions in Analysis Service?


Referenced Table Dimension worked in June SQL Release but fails in Sept Release

I have a SQL table named PkgGroup that is related to the Item table, the Item table is then related to the MonthlyTrans fact table with the measures I want to aggregate.  I used the Dimension Usage Tab of the Cube Designer to designate that the PkgGroup table was related by reference to the Item table.  In the June release this worked fine and my table processed correctly.  When I use the same Analysis Services Project that had been created with the June release against the same SQL Server database that I had Backed Up and then Restored with the installation of the Sept release, I get an error message that includes this line - "The attribute key cannot be found: Table: dbo_PkgGroup, Column: PkGrpNo, Value: 73087".  I have rechecked the Tables, the Relationships, and the underlying data and it all seems to be in order.  Is the  new release not working correctly or is it something I'm doing wrong?

Sept CTP: Distinct count agg differs when using WHERE clause and Aggregate(set)

Our cube has 9 dimensions, and several measure groups.  The measure group involved in this issue, is a distinct count of customers.  One of the dimensions is a time dimension, and it is marked as such since we have a semi-additive measure in another measure group.  More on that later.

We've been using 2005 June CTP for our test environment, and recently installed the newer 2005 Sept CTP.  That's when our problems started.  Our client needs the ability to place several groups of members from one dimension on rows/columns.  To meet this requirement, our webclient generates MDX that uses the Aggregate function.  (See below)

When testing data correctness from the cube, we quickly found that the Aggregate function has changed from the June CTP to the Sept CTP, and this is also indicated in Mosha's blog. 

Our understanding has been that, given max one member group from each dimension, the aggregation should be exactly the same using the Aggregate function compared to putting the member set in the WHERE clause of the SELECT statement.  And in the June CTP, this is indeed the case.  In the Sept CTP, however, it is not. 

We know the correct query results, both from our source SQL database, as well as from the fact table for the cube.  Our findings (compared to known correct numbers) are summarized below:

SQL Server version Aggregate query WHERE query
CTP June Correct Correct
CTP Sept WRONG! Correct


It did not matter, for the Sept CTP, whether or not the Time dimension was marked as type=Time or type=Regular; the results were the same.

The cube project used to deploy the cube onto the Sept 2005 CTP was originally developed on June 2005 CTP.  Before deployment and processing it was opened and edited somewhat in the Sept 2005 CTP SQL Server Business Intelligence Development Studio.

If anyone has any insights, we'd be grateful.

-Christian

-----------------------------------------------
MDX Aggregate version:

WITH 

MEMBER [Products].[several products] AS 'Aggregate({[Products].prod1, [Products].prod2, etc.})'

MEMBER [Time].[much time] AS 'Aggregate({[Time].[Q1], [Time].[Q2], etc.})'

SELECT

{ [Measures].[number of unique customers] } ON COLUMNS,

{ {[Location].&[112], [Location].&[115]}* {[Products].[several products]}* {[Time].[much time]}* {[CustomerType]&.[0]} } ON ROWS

FROM [Cube1]

-----------------------------------------------
MDX WHERE version:


SELECT

{ [Measures].[number of unique customers] } ON COLUMNS,

{ {[Location].&[112], [Location].&[115]}* {[CustomerType]&.[0]} } ON ROWS

FROM [Cube1]
where
(
 {[Products].prod1, [Products].prod2, etc.},
 {[Time].[Q1], [Time].[Q2], etc.}
)

Issues with .dwproj file needs resolution

I am trying to create a new "Analysis Services / BI" project and when I do it says that I can't since the "<Analysis Services...>.dwproj" is not installed.  I search for issues related to fixing this problem and the recommendations are to reinstall.

I think reinstallation is rather nasty.  There must be a way that I can add the "SQL Application VS tools" into VS2005 without spending an all the amount of time of a de/reinstallation.

So how can I install from VS2005 the MS-SQL VS200 tools without reinstalling the product?

My guess is that I can get .dwproj installed (that I don't have a clue on doing it or have seen/found any documenation on how to do it) I will do ok.

Ideas suggestions...

Deploying Excel Localcube

 

Hi friends,

 

Whenever I have created a local cube and have created an excel report from the local cube and after I send this report + the local cube to a client. I find that they often save the report + local cube to a different directory.

 

The problem is that the excel actually stores an absolute location that points to the cube. So when I've created the cube on my computer it could be c:\my.cub but when the client has unziped the report it could be in d:\reports\jan\my.cub . as a result Excel often complains of not being able to find the cube.

 

What can I do about this ?

 

Thanks

Tom

Analysis Services 2000, deleting a cube

    When I am in Analysis Manager and I try to delete a cube/dimension/database/measure by right clicking and selecting 'delete', Analysis Manager freezes up and mmc.exe uses 50 percent cpu (it stays like this forever till I 'end process'). 

I have tried reinstalling Analysis Services as well as Sql Server altogether but I still get the same problem whenever I try and delete something.  Any ideas?

SSAS newbie/simple cube/dimension creation question

I am starting with SSAS September CTP and am trying to build my first cube but it is not working at all.
I have 1 table named FACTtable with 4 fields, CustCode, Country,TranDate and Amount.
I am trying to produce a cube that will sum Amount by the dimensions CustCode and Country and also to have the TranDate as a dimension (visually along the top)with the ability to automatically sum up to month/quarter/year etc.
There is only 1 table involved so it is the fact and the dimension table.
I get errors about logical keys and if I do not use the trandDate it works fine.
The BOL are not very good as a tutorial, and I think that my example here is a very simple one.  I must be missing a very obvious base understanding of the tool set in SS2005.
Can anyone advise how I should create my cube/dimensions through the wizards to acheive the desired result?  Are there any documents that explain this in a clear way with no assumption of previous SSAS knowledge?
I know in SS2000 their is a 5-day course for this, but I am waiting for the SS2005 version of the course, but I cannot find the new course yet.

thanks in advance.

Sept CTP -- Interaction of calculated members in queries and cubes -- seems like a big regression

We frequently put calculated members and/or calculated cells in our cubes with solve orders greater than 1.  Our standard is that additive calculations are at 10, Share computations at 20 and Change/%change at 30.

In particular, we have a "market share" calculated member of a "companies" dimension that divides a company's data by the market total to arrive at a "share".  That Share is calculated at a solve order of 20 -- in the cube.

In our queries we frequently make local calculations at lower solve orders.  For example we might calcilate profit = sales-costs with a solve order of 0.  We would expect a querry of (Share,Profit) to (1) calculate profit for company and profit for market and (2) divide company profit/market profit to get a market share.  (This was the behavior in AS2K.)  However, what we find is that we instead get (1) calculate ( cost ,share) and (cost,share) and then (2) subtract to get (profit,share).  Clearely an incorrect answer.

Please tell me this is a bug that will be fixed!!

If not, the only way we can use SSAS is to either (1) Put ALL calculations in the queries or (2) put ALL calculations in the Cube.  But we can't mix them.

I think this is a show stopper for me.


Importance of the New SQL Server Windows Groups

Those of you who have installed SQL Server 2005 may have noticed that the installation creates several new Windows groups on the server.  Do not underestimate the importance of these groups.

I had been unable to configure Transactional Replication.  Everything worked fine until I tried to start the Distribution Agent.  The agent started just fine, but it reported the following error:



Date  10/19/2005 08:29:50 AM
Log  Job History (YKOADS3-CLGMMCADHOC-ADHOC_Pub-YKCLNSD-3)

Step ID  2
Server  YKOADS3
Job Name  YKOADS3-CLGMMCADHOC-ADHOC_Pub-YKCLNSD-3
Step Name  Run agent.
Duration  00:03:19
Sql Severity  0
Sql Message ID  0
Operator Emailed  
Operator Net sent  
Operator Paged  
Retries Attempted  0

Message
-XSTEPID 2
   -XSUBSYSTEM Distribution
   -XSERVER YKOADS3
   -XCMDLINE 0
   -XCancelEventHandle 000006A8
2005-10-19 12:33:04.568 Startup Delay: 4315 (msecs)
2005-10-19 12:33:08.896 Connecting to Distributor 'YKOADS3'
2005-10-19 12:33:09.021 Initializing
2005-10-19 12:33:09.037 Parameter values obtained from agent profile:
   -bcpbatchsize 2147473647
   -commitbatchsize 100
   -commitbatchthreshold 1000
   -historyverboselevel 1
   -keepalivemessageinterval 300
   -logintimeout 15
   -maxbcpthreads 1
   -maxdeliveredtransactions 0
   -pollinginterval 5000
   -querytimeout 1800
   -skiperrors
   -transactionsperhistory 100
2005-10-19 12:33:09.037 Connecting to Subscriber 'YKCLNSD'
2005-10-19 12:33:09.037 Agent message code 20084. The process could not connect to Subscriber 'YKCLNSD'.
2005-10-19 12:33:09.053 Category:NULL
Source:  Microsoft SQL Native Client
Number:  18456
Message: Login failed for user 'DM_MONTYNT\CLG_Distributor'.

 



I couldn't understand what I was doing wrong.  "DM_MONTYNT\CLG_Distributor" was a member of the "db_owner" role in both the "distribution" database and the Subscriber database.  (I am using a PUSH Subscription.)

I found a clue when I re-read the Books Online topics about Replication security.  "DM_MONTYNT\CLG_Distributor" also needed to be a member of the Publication Access List (PAL).  The problem was, the only way you can add a user account to the PAL is to (1) grant the account access to the Publisher database and (2) define the account on the Publisher server and the Distributor server.  (In my case, the Publisher and Distributor are the same server.)  Since I had not granted this account such access, I could not add this account to the PAL.

This created a problem, because I didn't want to grant "DM_MONTYNT\CLG_Distributor" access to the Publisher database.  I figured if Microsoft wanted me to grant the account for the Distribution Agent access to the Publisher database, they would have said so in the "Replication Agent Security Model" article in Books Online.  I guess I'm just naive.  ;-)

However, when I took a closer look at the PAL, I noticed that one of those new Windows groups ("SQLServer2005MSSQLUser$YKCLNSD$MSSQLSERVER") was already a member of the PAL.  "DM_MONTYNT\CLG_Distributor" was not currently a member of that group, so I decided to add it.  As soon as I added this account to this Windows group, replication worked.  It has continued to work ever since.

So if you're having security problems, take a look at these new Windows groups and see if they are tied into the feature you are working with.  They may be the key to unlocking the problem.


Reply:
Hi Ken,

1) It is not recommended to put the 'normal' account into the new Windows groups such as "SQLServer2005MSSQLUser$YKCLNSD$MSSQLSERVER" or SQLServer2005SQLAgentUser$YKCLNSD$MSSQLSERVER. These account are used to hold the SQL or SQLAgent service account. These groups are in sysadmin fixed server role. So in your case, you essential assign "DM_MONTYNT\CLG_Distributor" as the admin of the SQL server. (actually, it is more powerful than SQL sysadmin because it has the same Windows permission as the service account. see http://msdn2.microsoft.com/en-us/library/ms143504(en-US,SQL.90).aspx#Review_NT_rights for more information)

2) For PAL, all logins that are members of the sysadmin fixed server role or the db_owner fixed database role on the publication database can subscribe to a publication by default without being explicitly added to the PAL. And the logins in sysadmin role are by default in PAL (see http://msdn2.microsoft.com/en-us/library/ms151153(en-US,SQL.90).aspx). That is why replication works in your case.

3) You need to grant (and only grant) "DM_MONTYNT\CLG_Distributor" access to the Publisher database to add this account to PAL. This gives this account minimal access to the publisher DB while still make replication works.

Please let me know if you have further questions.
-Peng

------------------------------------
Reply:

Thanks for the additional information on these groups. 

I now want to clarify the steps you had me take for the benefit of other people reading this message:
1) We are now granting the Distribution Agent account access to the published database
2) The account's access is limited to membership in the "public" group only
3) Once the account was granted access to the database, I was able to add it to the PAL
4) I removed the account from the Windows group
5) I restarted the Distribution Agent just to be sure there were no problems

So far so good.  However, I strongly recommend that Microsoft revise it's configuration instructions.

Here's what the article currently says:

Distribution Agent for a Push Subscription
The Windows account under which the agent runs is used when making connections to the Distributor. This account must:

  • At minimum be a member of the db_owner fixed database role in the distribution database.
  • Be a member of the PAL.
  • Have read permissions on the snapshot share.
  • Have read permissions on the install directory of the OLE DB provider for the Subscriber if the subscription is for a non-SQL Server Subscriber.
  • The account used to connect to the Subscriber must at minimum be a member of the db_owner fixed database role in the subscription database (or have equivalent permissions if the subscription is for a non-SQL Server Subscriber).

Distribution Agent for a Pull Subscription
The Windows account under which the agent runs is used when making connections to the Subscriber. This account must at minimum be a member of the db_owner fixed database role in the subscription database.

The account used to connect to the Distributor must:

  • Be a member of the PAL.
  • Have read permissions on the snapshot share.

I would revise "Be a member of the PAL." by adding a note that you need only make the account a member of the "public" role in the published database in order to add the account to the PAL.  Even if this information exists elsewhere in Books Online, there should be a reference here just so people don't have to go crazy searching for it.


------------------------------------
Reply:
Thanks for the feedback. I will forward your suggestion to the technical writer so he can use it to improve BOL.

-Peng

------------------------------------
at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Setup is Split Across Multiple CDs

Setup is Split Across Multiple CDs Lately I've seen a bunch of people hitting installation errors that have to do with the fact th...