Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

SSAS CUBE PROCESSING ERROR Expand / Collapse
Author
Message
Posted Monday, January 28, 2013 3:42 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 22, 2013 3:06 AM
Points: 35, Visits: 71
Got into the same problem on my Dev machine. For me the solution was simple,
1. Goto Services.msc
2. Select SQL Server Analysis Services (sql2012) ("Your Instance") -> Properties -> Log On -> Added my Username and Password.
3. Bingo Process the cube.
Post #1412302
Posted Monday, January 28, 2013 11:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 28, 2013 11:26 AM
Points: 3, Visits: 1
Thanks - I will try that - where is the services.msc file?

Ramón
Post #1412577
Posted Monday, January 28, 2013 11:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 28, 2013 11:26 AM
Points: 3, Visits: 1
Never Mind - it is just the Services app. Thanks.
Post #1412578
Posted Friday, March 1, 2013 12:04 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 7, 2013 4:26 PM
Points: 2, Visits: 11
None of the above solutions is working for me.

I too get the error, as below, when I try to deploy the standard 'Adventure Works DW 2008R2'

"Error 22 OLE DB error: OLE DB or ODBC error: Login failed for user '<username>' .; 28000; Cannot open database "AdventureWorksDW2008R2" requested by the login. The login failed.; 42000"

I downloaded the 'Adventure Works DW 2008R2.abf' and installed via SSMS by connecting to an analysis services server instance and doing a database restore.
I copied the .abf file to
C:\Program Files\Microsoft SQL Server\MSAS10_50.MSSQLSERVER\OLAP\Backup
and restored the database from there and called it 'AdventureWorksDW2008R2'. That was all fine.

So, once the database was restored to my analysis server this was the view in SSMS. See attached SSMS - SSAS AW Db image.bmp.

I then created an Analysis Services project in BIDS, by importing an existing SQL Server Analysis Services database (namely AdventureWorksDW2008R2). The wizard completed without errors.

I then do a deploy of the project and get 20 Warnings and 14 errors and the deploy failed.

The impersonation information is set to my username and password. I am an administrator on the server.
If I go to 'connection manager' the server name defaults to localhost and the 'connect to a database name' defaults to a list of databases within the local database engine, which does not include 'AdventureWorksDW2008R2' as this database resides within the Microsoft Analysis Server only.

Both the Microsoft Analysis Server and the Microsoft database engine are local and I have sysadmin rights on the engine too.

I dont have an 'NT AUTHORITY\NETWORK SERVICE' login user. I do have a NT AUTHORITY\SYSTEM and this login has the sysadmin role and makes no difference either.

SQL Server Analysis Services (MSSQLSERVER) via Services.msc is running as me too - no problem.
The only services that are not running is SQL Server Agent (MSSQLSERVER) and SQL Server Browser. The agent I have the rights to start but the browser I don't. I haven't attempted to start either as I have had no indication that they would have any effect.

This is a very frustrating problem as it means you cant use the product at all. I have been trying fix it for 2 days now. Grrr!

Please someone enlighten me.


  Post Attachments 
SSMS - SSAS AW Db image.bmp (7 views, 1.48 MB)
Post #1425685
Posted Friday, March 1, 2013 7:03 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 28, 2014 8:51 AM
Points: 132, Visits: 581
Hey Anthony.

There's a couple questions before I can give you a good answer.

If you are trying to deploy and test the Adventure Works analysis services database, you need to make sure you the analysis services database AND the Adventure Works DW SQL database.
Once you have both the analysis services database AND the SQL database restored, you need to make sure the Data Source connection is set up correctly and pointed to the SQL database.

When you import the Analysis Services database in BIDS, make sure you set the deployment properties of the project and point it to the server you want to deploy this too. Also make sure you verify the Data Source connection is valid and the impersonation is set to a login that has access to the SQL database.
To deploy, make sure you have access on the Analysis Services server and I'd recommend trying to deploy using the Analysis Services Deployment Wizard (Start>SQL Server> Analysis Services> Deployment Wizard). Follow the wizard and be sure to choose to Not Process. If you get an error at this point, it's probably because you don't have access to the Analysis Services server you're trying to deploy to.

Once the Analysis Services database is deployed, connect to it through SSMS. Open up the data source and check again to make sure the connection is valid and is using the impersonation information. Right click a dimension and Process Full.

I've noticed that the data source has to be verified/updated after deployment, especially if you're using a login. Initial deployment through BIDS seems to attempt to deploy and process which may fail the deployment if the data source needs updating.
Post #1425804
Posted Saturday, March 2, 2013 6:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 7, 2013 4:26 PM
Points: 2, Visits: 11
Hi Richykong
thanks for the feedback.

Are you saying that I need to have a database on my database engine? As you may know, I already have 2 databases on the analysis server. For example, Adventure Works DW 2008R2.abf was downloaded and restored to AdventureWorksDW2008R2 on my analysis server(first database), as per the previous attachment. Then, a project was created to import the database by using the 'import an existing SQL Server Analysis Services database' (namely AdventureWorksDW2008R2). This created a database called 'Analysis Services Project 2' on the analysis server (also visible in the attachment). So that is a total of 2 identical databases on the analysis server. I think you are suggesting that I only need one database on the analysis server, which you refer to as the 'analysis services database'.

I think you are suggesting that I create a 2nd database, that resides on the database engine and that is what you refer to as the 'Adventure Works DW SQL database'.

Q1: Where do I find/download the 'Adventure Works DW SQL database'. Does it need to be 'paired' with a particular Adventure Works DW 2008R2.abf. By 'paired', I mean, for the Adventure Works DW 2008R2.abf I have restored to the analysis server, is there a specific 'Adventure Works DW SQL database'? I found http://msftdbprodsamples.codeplex.com/releases/view/59211 which lists lots of different download links but I think I need the link :-
AdventureWorksDW2008R2 Data File source code, 74944K, uploaded Apr 3, 2012
-which downloads Adventure Works DW 2008R2.mdf
Please confirm?
Q2: I am not clear on what you mean by 'When you import the Analysis Services database in BIDS, make sure you set the deployment properties of the project and point it to the server you want to deploy this too. Also make sure you verify the Data Source connection is valid and the impersonation is set to a login that has access to the SQL database.'. I think you are saying, once the 'Adventure Works DW SQL database' is installed on my database engine, the data source connection should be pointing to this database and NOT pointing to the Adventure Works DW 2008R2.abf that I have restored to the analysis server. Please confirm?
Q3: Currently, I have 2 identical databases on the analysis server. Do I need both the identical databases on the analysis server? I am struggling to understand why the project 'Analysis Services Project 2' needed to duplicate the original database - I suspect I didnt need to create a project in this manner but my thinking was that this was how you gained access to the cube definitions that come out of the box with the .abf file i.e by creating a project. I suspect that I should just rename 'Analysis Services Project 2', something like 'out of the box AW DW', so that I will always have a project that works, (assuming that I can get it to deploy without errors).

It should be noted that all the databases and servers (engine and analysis) reside on a single pc, to which I have administrator rights and I intend to impersonate as myself in all data source connections. This is a development environment and so I have scope to configure all aspects of the environment, if required.

I am new to SSAS but my understanding NOW is that the way it works is - that if you were starting from scratch trying to create a cube for say a regular production database in the engine(DB1), you then use BIDS to create an analysis server project that uses the DB1 as a data source. You then create cubes, dimensions etc within the project whereby the data source, cubes, dimensions etc get stored in a database on the analysis server (DB2). When data changes occur in the DB1 database and an individual subsequently runs a cube report, it will use the properties of the cube in the DB2 database to drive the functionality of the report and connect to DB1 (using an impersonating account) to retrieve the raw data from DB1.

Any insights greatly appreciated!

Regards Anthony
Post #1425848
Posted Saturday, March 2, 2013 12:22 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 28, 2014 8:51 AM
Points: 132, Visits: 581
Q1. This should give you the mdf and ldf you need for the SQL database.
http://adventureworksdw2008.codeplex.com/

Q2. Yes, the datasource should be connecting to the SQL database that was just attached. If you right click the project and go to the properties, you should see an option for deployment where you can define the server and Analysis Services database name you want to the deploy this project as.

Q3. Importing is the way to get a copy of a deployed SSAS database. If you just wanted to look at the code, you can connect directly to an existing SSAS database. In BIDS, File > Open > Analysis Services Database (or something named similar) and it'll ask for the server and database you want to connect to. This will open the live SSAS database. Any changes you save will be applied directly to the database on the server.

Overview of SSAS (MOLAP). (Molap is the storage setting in which structure and aggregate data is stored in the SSAS databse.)

SSAS is essentially an aggregated view of underlying SQL data. The storage of data in SSAS when using MOLAP can be thought of as massive complex indexes. All the definitions you create in the SSAS database are just indexes. To process and update these indexes, you have connect the SSAS objects (Measures and Dimensions) to the underlying data tables from the SQL database. The SQL table DimDate has all the dates in all the different formats you would want to see it. In SSAS, the Date(Time) dimension references the DimDate table and defines which columns are used and the format. SSAS dimension also allows you to define a hierarchy of the data. The SQL fact tables are used in the MeasureGroups of the SSAS database and the aggregation logic (SUM,MIN,...) is used to determine how that field is used. You can also define the relationship between the fact data and the dim data in the Cube.

The first step of a SSAS solution would be define the SQL data warehouse. The SQL data warehouse would usually be a star schema dimensional data model populated from your source system data. Once you create the data warehouse with Fact tables and Dimension tables, you can start creating your SSAS database.
1. Create an SSAS solution. Right click on the projcet (databse) and you should have a popup with Deployment configuration. Make sure you are deploying to the SSAS server you have access to and define the SSAS database name you want it deployed as.
2. Create a data source connecting to the data warehouse. Make sure you configure the impersonation settings correctly for processing after deployment.
3. Create a data source view from the data source you just created and import the fact and dimension tables.
4. Create a dimension for each of the dimension tables.
5. Create a Cube with measure groups using the fact tables.
6. Go to the Dimensions tab in the Cube and make sure dimensions are mapped to the fact. (If the relationship between fact and dimension are defined in the dsv, the connection should have been automatically created.) If a dimension is missing, right click and add the dimension. Make sure fact and dimension relationships are defined.
7. Deploy and process.

What happens at this point is:
The SSAS database definition will attempt create/alter objects on the SSAS server and database you defined in step 1.
Once the objects are deployed, it will attempt to process the data. Processing of the data entails selecting the fact and dimension data from the data warehouse tables and aggregating and storing it in the SSAS database.

I hope this all makes sense and helps. It's a Saturday and my mind is not at 100%.
Post #1425888
Posted Thursday, May 30, 2013 10:26 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 4, 2013 9:41 AM
Points: 20, Visits: 201
its very help full to me

i was trying from long time

Thanks ,,,,

Regards
Arjun
Post #1458318
Posted Thursday, October 10, 2013 10:43 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 10, 2013 10:41 PM
Points: 1, Visits: 0
it works!!! thanks yulia, you are a life saver.
Post #1503863
Posted Monday, October 14, 2013 2:20 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, October 14, 2013 2:15 AM
Points: 32, Visits: 34
The answer to grant blanco rights to Network Service may still help people, but it is still wrong. On production servers, this is definitely NOT best practice. You should start with non-builtin service accounts and then grant minimum rights to the SSAS data sources either via that service account or individually via impersonation, like hinted at before.
Post #1504347
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse