Creating two dimensions referencing the same table

Hi,

I am building a cube in the 2005 Analysis Services. I built the cube successfully when each of my dimensions referenced exactly one table. Now I want to extend that to build two dimensions based on one table. I can build the 2nd dimension but I cannot use it within the cube.

Any suggestions on how to do that?

Thanks in advance,

Aref

Any reason why you can't build 1 dimension and use it in 2 roles within your cube (eg: Date dimension in Adventure Works plays 3 roles)?

Creating Time Dimension for seconds

I'm creating analysis cubes on data that needs to reflect it's distribution over time during a 4 hour period.

I'd like to create a Time Dimension for "Every 15 seconds"

I've already got a DIM_Time table that includes actual event times (down to milliseconds)

Do I just need to create fields on that data for the 0:15 block that the particular event falls in, and create records for every 0:15 even if there isn't a related row in the measures table?

So that...

Measures
ID TimeId Measure
1 1 100
2 2 200

DIM_Time
TimeId Time
1 0:01.1234
2 0:31.2345
3 0:15
4 0:30
5 0:45
6 1:00
7 1:15
etc...

Hi Greg,

You're on the right track. The reason you would put the 15 second block even if there isn't an actual time for that is later on down the road there may be a fact for that and you would simply add the fact without have to also add the dimension member. You also may want to see for some reason the times that don't have data.

Depending on the requirements, I might break the Min and Seconds out in seperate fields. Like

TimeID Hour Min Sec Actual Time

1 0 0 0 0:10:00

2 0 0 15

3 0 0 45

4 0 1 0

Depending if the facts needs to roll up to a Min. and Hour.

Hope this helps,

David Botzenhart

|||Thanks! I appreciate your clarifications on the table structure

Creating Named Query in Analysis Services Using AMO

Hello,

Is there an example somewhere that shows how to create a named query in DSV based on an existing table in the DSV using AMO?

Thank you so much,

Sincerely,

Annie

This may help:

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=727220&SiteID=17

Also, if you install the SQL Server Samples, the following path will have a bit of a more comprehensive example of creating a DSV. Though it doesn't create any named queries, it may be helpful:

C:\program files\Microsoft SQL Server\90\Samples\Analysis Services\Programmability\AMO\AMOAdventureWorks\CS\AmoAdventureWorks

Creating Local cube Using SQL Server / Analysis Services 2005

I am creating a local cube through a web page and everything is working properly when I am debugging but if I am not debugging, the following exception is returned:

x= Exception:File system error: Error opening file; \\?\C:\Program Files\Common Files\System\Ole DB\MDTempStore_1088_1_fm3by.tmp is not a disk file or file is not accessible.

StackTrace: at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.HandleCreateLocalCube(AdomdErrorResponseException ex) at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Microsoft.AnalysisServices.AdomdClient.IExecuteProvider.Execute(ICommandContentProvider contentProvider, AdomdPropertyCollection commandProperties, IDataParameterCollection parameters) at Microsoft.AnalysisServices.AdomdClient.AdomdCommand.Execute()

The role defined for the cube is:

General tab: Read definition

Cubes tab: Access: read

Cubes: Local Cube/Drillthrough Access

Thanks,

Yones

Looks like a problem with security. Make sure the process you running within IIS has sufficient privileges to create a files local cube files.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi Edward,

Yes, it seems like it is a security problem. I have just found that it is working properly when using AS 2005 installed with (local) SQL server 2005 but it does not work with a named instance such as (ServerName\SQLServerInstanceName)

Could please be more specific about what privileges should be given?

Thanks,

Yones

|||

Not sure how named instance plays the role here.

Are you talking about SQL Server relational engine named instance or about Analysis Server named instance here?

If it is working with default instance, try and see what are the differences in the way named instance is setup.

There should be a little difference in the way you work against named instance.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi Edward,

Concerning named instance (MachineName\InstanceName) and default instance ((local)), I think if we have a named instance, it is for both SQL Server relational engine and Analysis Services. I do not think we can have a named instance for SQL Server relational engine and a default instance for Analysis Services (Please correct me if I am wrong)

It is working for the default instance (local) because the Web Server (IIS) and Analysis Services are on the same machine but for a named instance (MachineName\InstanceName), it is not working even the “MachineName” is the same for the Web Server (IIS)

Thanks,

Yones

|||

Here is a similar thread :

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=686728&SiteID=1

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Creating dynamic reports

Hello.

I wanted to know a way to create dynamic reports using reporting / analysis services in SQL Server 2005 / SSIS tool.

I want user to give inputs and generate reports based on those inputs.

Please guide me on this issue.

Thank you.

Regards,

Prathamesh

Hello Domnick....

In Report Services, you have basically two ways to do this....

1- The Input Paremeter can stay in the Report Services

2- The Input Parameter can stay in application and can be sent to Report Services by the Url ( Named Url Access )

1--> If in Query of your DataSet, you have a parameter (Ex: @.ID), Automatically it will be detected by Report Services, and it can be edited by the Report Parematers -->In Layout View / Right Click at the Fund Plane of Your Report / Report Parameters

Or the input parameter can be added manually in this same location (I don′t recomended)

2--> Sample :

http://server/reportserver?/Sales/Northwest/Employee Sales Report&rs:Command=Render&ID=1234

Is this your problem?