Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)
Course Of The Month
10 days, 19 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Create an OLAP Cube for Project Server
Posted on 2008-06-16
Can someone point me to a guide about creating an OLAP cube that I will later be able to use in Project Server to get data for reports?
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
5
-
3
-
2
10 Comments
Active 5 days ago
Which version of Project Server are you using, and are you using Project Web Access?
If you are looking to use a cube through the application you can use the standard cube in Web Access, which can also be extended.
Project Web Access -> Projects -> Analyze projects in Portfolio Analyzer
This cube already includes a lot of information, and also the Enterprise Outline fields, but if you want any other information or other Enterprise fields you will need to extend the cube. Here are a few links:
http://www.projectserverexperts.com/Downloads/Extending%20the%20OLAP%20Cube%20White%20Paper%20by%20Bob%20Segrest.pdf
http://office.microsoft.com/en-us/projservadmin/HA011652911033.aspx
http://msdn.microsoft.com/en-us/library/aa168479(office.11).aspx
http://msdn.microsoft.com/en-us/library/aa208428(office.11).aspx
If you only want to create a new cube, this can be easier then extending, but also more difficult as you need to deal with the data structure. So probably still worth reading the articles anyway.
In this case you could use the MSP_ tables and the MSP_VIEW_ tables.
If you are looking to use a cube through the application you can use the standard cube in Web Access, which can also be extended.
Project Web Access -> Projects -> Analyze projects in Portfolio Analyzer
This cube already includes a lot of information, and also the Enterprise Outline fields, but if you want any other information or other Enterprise fields you will need to extend the cube. Here are a few links:
http://www.projectserverexperts.com/Downloads/Extending%20the%20OLAP%20Cube%20White%20Paper%20by%20Bob%20Segrest.pdf
http://office.microsoft.com/en-us/projservadmin/HA011652911033.aspx
http://msdn.microsoft.com/en-us/library/aa168479(office.11).aspx
http://msdn.microsoft.com/en-us/library/aa208428(office.11).aspx
If you only want to create a new cube, this can be easier then extending, but also more difficult as you need to deal with the data structure. So probably still worth reading the articles anyway.
In this case you could use the MSP_ tables and the MSP_VIEW_ tables.
I'm using Project Server 2007 and PWA. The information I'm developing an application that integrates with Project Server and has it's own database. I want the OLAP Cube to use the information from my database.
Active 5 days ago
OK so as it's your own database that should be easier as it is your data structure.
If you are running SQL2005 you will need SSAS (SQL Server Analysis Services) installed, and then you can use the Business Intelligence Development Studio to create the new SSAS Project.
If you are using SQL2000 then the equivalent would be Analysis Manager.
Your data should be copied into holding tables in the Star Database Schema (1x Fact Table to Many DIM tables), then you can just use the new cube wizard to create your cube.
Not sure how to access it from Project, as I have only looked into extending the existing cube before.
If you are running SQL2005 you will need SSAS (SQL Server Analysis Services) installed, and then you can use the Business Intelligence Development Studio to create the new SSAS Project.
If you are using SQL2000 then the equivalent would be Analysis Manager.
Your data should be copied into holding tables in the Star Database Schema (1x Fact Table to Many DIM tables), then you can just use the new cube wizard to create your cube.
Not sure how to access it from Project, as I have only looked into extending the existing cube before.
I know about SSAS and Business Intelligence Development Studio. That is exactly what I'm looking for. How can I access it from Project Server.
Active 5 days ago
Ah OK, sorry I have not done this in 2007 before.
In 2003 the only way to do this is to extend the existing cube, unless your application is written to access a custom cube.
But in 2007 you can add Calculated Measures to default cubes, to enable you to report on custom fields such as your database.
In 2003 the only way to do this is to extend the existing cube, unless your application is written to access a custom cube.
But in 2007 you can add Calculated Measures to default cubes, to enable you to report on custom fields such as your database.
Hello iulianchira,
What you need to do in Project Server 2007 is create a data analysis view.
Log into PWA as a Project Server administrator.
Select the Server Settings option from the quick launch menu (left side of the page towards the bottom).
Select the Manage Views option in the Look And Feel section.
Click the New View link on the view table menu bar.
Pull down the View Type list and select the Data Analysis option.
Now you will need to specify the name of your data analysis server and the cube you want to use...
There is a page in TechNet with the title "Create Data Analysis views" that may also be of some assistance. The URL is:
http://technet.microsoft.com/en-us/library/cc197306(TechNet.10).aspx
Does this answer your question?
Bob Segrest, PMP
Microsoft Project Blackbelt
What you need to do in Project Server 2007 is create a data analysis view.
Log into PWA as a Project Server administrator.
Select the Server Settings option from the quick launch menu (left side of the page towards the bottom).
Select the Manage Views option in the Look And Feel section.
Click the New View link on the view table menu bar.
Pull down the View Type list and select the Data Analysis option.
Now you will need to specify the name of your data analysis server and the cube you want to use...
There is a page in TechNet with the title "Create Data Analysis views" that may also be of some assistance. The URL is:
http://technet.microsoft.com/en-us/library/cc197306(TechNet.10).aspx
Does this answer your question?
Bob Segrest, PMP
Microsoft Project Blackbelt
Active 5 days ago
Bob: That looks much better than 2003. Think I will push for the 2007 upgrade rather than spending any more time extending the Standard Cube in 2003.
Would I be right in thinking that 2007 can now include any Enterprise custom fields, rather than Just the Enterprise Option fields as in 2003?
Would I be right in thinking that 2007 can now include any Enterprise custom fields, rather than Just the Enterprise Option fields as in 2003?
Hi rickchild,
Keep in mind that to do data analysis on Project Server 2007 you need SQL 2005.
Clearly, SQL 2005 solves a whole relm of problems compared to SQL 2000 Analysis Services. Given the opportunity to choose, there is no choice. Move forward.
Bob Segrest, PMP
Microsoft Project Blackbelt
Keep in mind that to do data analysis on Project Server 2007 you need SQL 2005.
Clearly, SQL 2005 solves a whole relm of problems compared to SQL 2000 Analysis Services. Given the opportunity to choose, there is no choice. Move forward.
Bob Segrest, PMP
Microsoft Project Blackbelt
Active 5 days ago
Hi, yes we are already on SQL2005 64bit with a 2005 consolidated cluster, and a 2005 Report / Analysis server.
Just catching up now on Project and CRM etc. I will have a play with Project 2007, but from what I have seen it will solve the problems we have with analysis on the custom enterprise fields. Currently running customised cubes out of project through Excel pivots. Thanks.
Just catching up now on Project and CRM etc. I will have a play with Project 2007, but from what I have seen it will solve the problems we have with analysis on the custom enterprise fields. Currently running customised cubes out of project through Excel pivots. Thanks.
Featured Post
PeopleSoft Adoption Made Smooth & Simple!
On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool. Claim Your Free WalkMe Account Now
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
- Databases
- AWS
- MySQL Server
- MongoDB
- NoSQL Databases
- Percona
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month10 days, 19 hours left to enroll
Earn Certification
Managing Projects with Microsoft Project 2013 (Exam 74-343)
$49.00$44.10
628 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.