This post is related to this tip: Using the Built-In Time Dimension and Time Intelligence in SQL Server Analysis

http://www.mssqltips.com/tip.asp?tip=1454

I tried this out and it works fine when I browse using the Business Intelligence Development Studio or Management Studio but the percent growth, etc calcuations don't show up when I try to access the cube using Excel 2007.  How can I make them show up?

 Check your PivotTable Options and make sure Show calculated members from OLAP server is checked; by default it is not checked.  Once checked you should see the calculated members.

Thanks.  I can now bring them in but I have another problem.  It brings in all the different calcuations but I only want to show to the quarter over quarter growth %.  What I really want to do is pick two consecutive quarters and be able to show several measures for the two quarters and then shower the quarter over quarter growth % for the two quarters (only show it once).  Any ideas of how I can do this?

There is some sort of limitation with Excel which doesn't allow this.  You can take a look at this blog entry and see if this work around would be acceptable: http://sqlblog.com/blogs/marco_russo/archive/2007/09/02/datetool-dimension-an-alternative-time-intelligence-implementation.aspx

If you're considering Performance Point, you could use the Analytic Grid report.  It doesn't have the limitation that you have found in Excel. 

This worked well.  Thanks.  However, I have an additional need using Xcel 2007 to do the following shown below for the most recent quarter with data (I have a column on my date dimension that can identify this if necessary) without having to add the date dimension.  I would like to add calcuations to the cube since this is information that is used by alot of production reports and reports that will be built by end-users using Xcel 2007.  Is this possible?  I'm really new to this so excuse my ignorance.  Also, does anyone know of a good book about MDX and Analysis Services that would have examples.  I would also like to find a user group in the Portland Oregon area.

Measure   Curr Value   Prior Qtr Value  Prior Qtr % Chg

Clm Cnt

Clm Pd

Avg Pd

etc.

Portland SQL Server User Group: http://www.pdxvbug.com/pdxuser.asp

I think

MDX Solutions: With Microsoft SQL Server Analysis Services 2005 and Hyperion Essbase

is a good book

http://www.amazon.com/MDX-Solutions-Microsoft-Analysis-Services/dp/0471748080

 

Thanks for the info.  Any suggestions on the problem I'm trying to solve?

I have tried this many times but still not working, I just tell you my situation then please suggest me what to do.

I have Generated Cube with OLTP Data Source already in that cube i have one Fact Table ( Billing ) and two Dimensional Table ( Customer And Office Location ) now i want to apply built in time intelligence in that cube. I have tried your solution, it is creating time dimension table in OLTP database and creating one Calendar Dimension as well but i m unable to link it with Billing Fact Table and unable to Define Time Intelligence in my CUBE.

 

Did you add the time dimension to the Data Source View then add the foreign key relationships between the fact tables and time dimension?  Then you add the time dimension to the cube.

The article was written based on creating a cube without a data source.  In order to add time intelligence to an existing cube, you open the project, select the cube, click the Cube menu, then select Add Business Intelligence which will launch the Business Intelligence Wizard.  Select Define Time Intelligence and complete the wizard.

It would probably be worth following the steps in the article and setting up the time intelligence in a test cube to get familiar with all of the settings.

 

 

 

I have added a Dimension which type is TIME, when I am trying it to add in my data source view, I am unable to find the relationship key, because in my Fact Table BillDate format is dd/mm/yy hh:mm:ss and in the Calendar Dimension it is only dd/mm/yyyy. How can I create the KEY is Fact Table.

Time Type Dimension is not in the CUBE thats why this Time Intelligence Wizard is not working here. 

 

 You could add a Named Calculation to the fact table in the Data Source View to truncate the time portion of the date and expose that as a new column that you can use as a foreigh key to the time dimension.

If your fact table is in SQL Server you could use an expression like this for the named calculation:

convert(datetime, convert(varchar(10), <your datetime column goes here>, 120)) 

 

 

I guess I am misssing something because, how can your table be populated if you created it without a datasource? When I follow the directions, I get the Calendar that is populated with dates but the fact table is empty.

Am I missing a step?

 Thank you.

 John

The tip doesn't explicitly say that you have to populate the schema that is generated but as you have pointed out, you do. There is a script file in the download which will copy some data out of the AdventureWorksDW database into the mssqltips_dw database. The script file is populate_fact_sales.sql.

Great, thank you!

 

John