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 changing default parameter?

I am creating SSRS reports on top of SSAS cubes. I want the default value of parameter to change dynamically based on the current year or it should select the last of the parameter values.

Can this be done?

here is how i'm setting my "from date" (datetime) param to be the first of the current month
=DateSerial(Year(Now()), Month(Now()),1)

for today, just use
=Today()

this is for a whole date, you can just pick the year part

Dynamically Add Partitions to a SSAS Cube

The environment here is SSIS ETL feeding a Fact Table. The Fact Table is pulled into SSAS as a cube and reporting services are handled there. I am on the ETL side and don't pretend to know all the processing that happens with cubes, etc.

What we are trying to accomplish is to add partitions to a Cube via the ETL processing. The partitions should be incremented by Day, i.e. 20070501, 20070502, etc.

This is currently processed manually by the Reporting developer and we are looking for an automated process to reduce errors and hand work.

I have explored the following objects: Partition Processing DataFlow Destination and did not find much documentation or examples on it's use. If you have any information on this stage, please reply with such.

The other option is the Analysis Services Processing Control Flow. I understand that we can process Analysis Server objects as part of our package. Is there a way to incorporate a Partitioning Script in this object? If so, how. Again, I did not find detailed examples on the use of this object.

If you have experience with either of these, please feel free to reply. I appreciate any and all comments.

Use the "Analysis Services Execute DDL Task". This allows you to fire XMLA statements against Analysis Services.

I can't help you with what the XMLA statement needs to be I'm afraid, you'll need to go for the Analysis Services forum for that.

You may wish to check out these two guys for assistance:

Darren Gosbel - http://geekswithblogs.net/darrengosbell/Default.aspx

Chris Webb - http://cwebbbi.spaces.live.com/

-Jamie

|||

Jamie, thanks I will explore this task...our SSAS developer is checking out how to generate the XML so I will forward these links to him. Our research was leading us toward the XMLA approach but we were lacking in how to fire it off.

Thanks again!

|||

I am also looking into this and have my xmla created and all, but when firing the task i get this error:

[Analysis Services Execute DDL Task] Error: Errors in the metadata manager. Either the cube with the ID of 'xxxx' does not exist in the database with the ID of xxxx', or the user does not have permissions to access the object.

Anyone can help with this error?

|||

Hi JOER_DK,

CUBE ID is nothing but the name of the cube you want to process from an Analysis Services Database. You can generate the code for processing a cube by right clicking the cube -> Process-> This open a window for processing -> In this window, you have a button to script out the xmla for processing.

Thanks

Subhash Subramanyam