easy design tool

I have fairly simple table queries, and only a few dicey things, which I can handle in access. Is their a design tool for sql as easy and convenient as access?

Can someone suggest a good beginner's book on sql server 2005?

Use Access.

Explore how an Access Data Project works (it has a SQL Server backend).

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 SQL ConnectionString for live/test environments

Hello. What I'm trying to do should be fairly simple, but after an hour of searching and trials, I can't seem to get it working.

When I run my website on my test machine, I would like it to use a certain value for my connectionString. But when it's run on the live server, it should use a different connection string. This way, when it's on the test server it'll use the test database.

In my Web.Config file, I have this:

<connectionStrings>
<add name="myConnectionString" connectionString="Data Source=TEST_DB;Initial Catalog=tester;Integrated Security=True"
providerName="System.Data.SqlClient" />
</connectionStrings>

I would like to be able to change the connectionString in something like the Global.asax file, when the application is loaded. I'd check for a certain environment variable, or some other condition, and change the value of the connectionString.

I tried this in my Global.asax, but it told me the value was read_only:

ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString = "Data Source=LIVE_DB;Initial Catalog=live;Integrated Security=True"

Does anyone have a good way of doing this, so I don't need to juggle Web.Config files when I publish my site?

Thanks.

You can have a separate app.config file for production or have a separate configuration section so that when you build the application (deploy), Visual Studio will replace the connection strings

Take a look at this post (http://msdn2.microsoft.com/en-us/library/bb164473(VS.80).aspx)

|||

After a more varied search, I came across the Web Deployment Projects tools.

http://msdn2.microsoft.com/en-us/asp.net/Aa336619.aspx

Now I've got it simply including a different piece of config code when it builds the website in either Debug or Release mode.

Thanks for the reply, though. I'm all set now.