Dynamically setting maximum server date dimension

Hello everyone. I have what should hopefully be a simple problem. I have a fairly complicated cube created in SSAS 2005. One of my dimensions is a Server Time dimension. I have this linked to my fact table with a Regular relationship. Now, I would ideally like to be able to set the CalendarEndDate property in the Source property group of my dimension dynamically, based on the most recent date in the fact table. I will never need to see information for a future date using this dimension. Any ideas would be greatly appreciated. Thank you.

Alex Levin

Principal Consultant

Fifth Marker Consulting, LLC

alex.levin@.fifthmarker.com

Why would you want to do that? Which goals of yours would it allow to reach?|||

I have a cube which is being used by analysts who are using Excel pivottables as a frontend. When sorting by time, they do not wish to see any options which will return no data. New data is only added about once a month, so reprocessing the cube at that time is not an issue. Does this make sense? Is this the appropriate way of handling this situation?

Alex Levin

Principal Consultant

Fifth Marker Consulting, LLC

alex.levin@.fifthmarker.com

|||Usually the tools are capable to show only cells with data. For instance, Browser page in Visual Studio does not show the cells with no data by default. You can control it with "Show Empty Cells" button on the toolbar. I think Excel has the same functionality.

Suppose you have 2 measure groups. For one measure group the fact data gives you "last known date1", for another - "last known date2". What would you do?

The time span for the time dimension should cover all the facts with which the dimension will be used. Otherwise the processing of the cube will fail.|||

you can disable cells in excel where you do't have any data. in the Pivot table you have a button called "Always display items"

-- Good Luck

|||

Here is another way to skin the cat.

I have mutiple measure groups using the time dimension. The way I find the min and max dates is store them in a single record table DAY_DIM_RANGE using a stored procedure. This goes through the multiple fact tables and retains the min and max dates and writes them to the single row table.

The date dimension is then built using a view with a where clause such as

WHERE DAY_KEY BETWEEN (SELECT minDayKey FROM dbo.DAY_DIM_RANGE) AND
(SELECT maxDayKey FROM dbo.DAY_DIM_RANGE)

When you add additional measure groups, just adjust the stored procedure to handle the new fact table min and max dates. The view does not change. The stored procedure needs to run just before the cube is processed or at end of ETL.

Dynamically setting maximum server date dimension

Hello everyone. I have what should hopefully be a simple problem. I have a fairly complicated cube created in SSAS 2005. One of my dimensions is a Server Time dimension. I have this linked to my fact table with a Regular relationship. Now, I would ideally like to be able to set the CalendarEndDate property in the Source property group of my dimension dynamically, based on the most recent date in the fact table. I will never need to see information for a future date using this dimension. Any ideas would be greatly appreciated. Thank you.

Alex Levin

Principal Consultant

Fifth Marker Consulting, LLC

alex.levin@.fifthmarker.com

Why would you want to do that? Which goals of yours would it allow to reach?|||

I have a cube which is being used by analysts who are using Excel pivottables as a frontend. When sorting by time, they do not wish to see any options which will return no data. New data is only added about once a month, so reprocessing the cube at that time is not an issue. Does this make sense? Is this the appropriate way of handling this situation?

Alex Levin

Principal Consultant

Fifth Marker Consulting, LLC

alex.levin@.fifthmarker.com

|||Usually the tools are capable to show only cells with data. For instance, Browser page in Visual Studio does not show the cells with no data by default. You can control it with "Show Empty Cells" button on the toolbar. I think Excel has the same functionality.

Suppose you have 2 measure groups. For one measure group the fact data gives you "last known date1", for another - "last known date2". What would you do?

The time span for the time dimension should cover all the facts with which the dimension will be used. Otherwise the processing of the cube will fail.|||

you can disable cells in excel where you do't have any data. in the Pivot table you have a button called "Always display items"

-- Good Luck

|||

Here is another way to skin the cat.

I have mutiple measure groups using the time dimension. The way I find the min and max dates is store them in a single record table DAY_DIM_RANGE using a stored procedure. This goes through the multiple fact tables and retains the min and max dates and writes them to the single row table.

The date dimension is then built using a view with a where clause such as

WHERE DAY_KEY BETWEEN (SELECT minDayKey FROM dbo.DAY_DIM_RANGE) AND
(SELECT maxDayKey FROM dbo.DAY_DIM_RANGE)

When you add additional measure groups, just adjust the stored procedure to handle the new fact table min and max dates. The view does not change. The stored procedure needs to run just before the cube is processed or at end of ETL.

Dynamically change the servername

Hi all,

I've created 1 solution and added all my packages in different projects (like DIMENSIONS, SOURCES_SAP, ...).

For each project I have a Data Source that connects to the server. The problem is that when I want to deploy a package to the server that I always need to change the Data Source before deployment.

Before SQL Server 2005 we used a connection file (which was located as well on the server as on the development pc's in the same locations) within our DTS packages. This way we didn't had to change the connections when deploying to the server.

My intention was to use the current configuration from the configuration manager(development / production) to select the servername. Unfortunately, I didn't succeed to retrieve it's value from a variable script.

I need to have a solution that dynamically changes the datasources for multiple packages depending on a specific action.

How can I achieve this the easiest way ?

Thanks in advance !

Geert

You need to take a look at creating "package configurations". Try doing a search in Books Online for this. It helps you to do exactly what you are looking for.