how to find costly CUBE query and kill it

 

often times, you will find that SSAS server gets super busy because of weird request from CUBE client (proclarity, excel, report builider ), as the SSAS Administrator,

you need to find the culprit and kill . Here is how to do so

1. Find it

 

<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">
  <RequestType>DISCOVER_SESSIONS</RequestType>
  <Restrictions>
    <RestrictionList />
  </Restrictions>
  <Properties>
    <PropertyList />
  </Properties>
</Discover>

 

2. Kill it

 

<Cancel xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

<SPID>121672</SPID>

<CancelAssociated>1</CancelAssociated>

</Cancel>

 

Now the challenge is to find out the culprit as step 1 will return a list of SPIDs, how do you sift through???

Logging failed when trying to process cube using default impersonation mode

Problem: When I was trying to process the cube whose source data is on staging .18 and target deployment server is localservice, BIDS reported that service account used

can not access the source.  The service account is the local service account

 

Solution: Go to Control panel on local machine –> services-> find out SSAS service-> Log On tab  and change “This account ” to my windows credentials with passwd

OLE DB error: OLE DB or ODBC error: Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.; 28000; Cannot open database "DW_PROD" requested by the login. The login failed.; 42000.

 

Reason:   this user 'NT AUTHORITY\NETWORK SERVICE' does not have read access to DW_PROD

Solution:  Connect to the server via SSMS. in Security section, create this user if doesn’t exist.

Then go to login properties –> User Mapping and choose the database you want to grant permission, grant db_datareader role

Closure of the MS BI project

will write some experience through this project
Several items that I’d like to touch are listed below:

1. SSIS tips
Development
We have 3 types of packages: daily, weekly and monthly.
Daily pkgs will pick up new records everyday and aggregate into the summary table.
For daily changed reference data, we do not pick up untill the monthly pkgs kick in.
That’s our version to Slowly Changing Dimension (ideally, SCD should be used)

weekly pkgs will truncate the fact table and bring 3 month new records

monthly pkgs will truncate dimension tables and bring all records

Deployment
So far, the deployment is using file system and import pkgs via SSMS. That’s not the best way though but DBA feels more comfortable in this way

Debugging
We have created a sysssislog table which contains all log information. If data error or pkg error occurs , dba or am will get an email immediately.

When we direct error output to another table in OLE DB destination, make sure it’s using table/view instead of fast load. If fast load is used, if the stream hits any bad data, ssis will direct all data after the bad row into the error table no matter if it’s bad or not. Be careful!

2. SSAS tips
Development
Deployment
Debugging

3. Version Control (SVN)

Lessons learned from pilot BI project

In order to build this pilot BI project, we utilized SSIS 2008 to create the datawarehouse and used SSAS to create the OLAP cube on top of that.  In the next few blogs, I’ll post the tips and lessons learnt from the process in order to share with those who are interested and to remind myself from time to time…

Excel 2007 can not connect to SSAS cube

Weird problem! My excel keeps saying the server is either busy or not started…why?

 

Finally, the helpdesk solved this mystery by……reinstalling MS Office….