offline OLAP Cube +MDX

I will be really very much greatful to one who can help me out as I have been struggling to solve this from 2 months.

I have a .mdb database and I want to craete a pivot from it.The size of the database is around 400 MB so it was suggested to create a cube from this database and then create pivot.The database is populated with TOTAL values for all its class variables .

When I create cube from Excel wizard it again summarises and craetes ALL level and the pivot is completely wrong.Infact I get values in the multiples of 2**classvariables.Is there a way where can I create cube by VBA and eliminate ALL LEVEL for every dimension. I have tried so many codes but nothing is working.Any sample code will be a great help.

Here I am getting Multiple Selections for page field as well .Is there any way to disable them?

The second option could be to populate the Listviews with the dimension values and then create a report in Excel using MDX but its not working as well.

I am relly looking looking forward to you great guys.

Regards

Hi,

create the cube with the Excel-Wizar. Save the to C:\

Then there is a SclicerDDL.txt with the CREATE GLOBAL Cube-Statement.

Remove the All Level

Example:

DIMENSION [CUBENAME].[DIM-NAME]
(
LEVEL [(All)], <-Delete this line
LEVEL [L1],
LEVEL [L2],
LEVEL [L3],
LEVEL [L4]
)

>

DIMENSION [CUBENAME].[DIM-NAME]
(
LEVEL [L1],
LEVEL [L2],
LEVEL [L3],
LEVEL [L4]
),

Run this edited SlicerDDL.txt in the Analysis Server MDX-Samples and the local cube will not contain the All-Level.

hth

Bernhard Saemmer

|||I only have Excel in my machine.Is it possible to do it by VBA?

offline OLAP Cube +MDX

I will be really very much greatful to one who can help me out as I have been struggling to solve this from 2 months.

I have a .mdb database and I want to craete a pivot from it.The size of the database is around 400 MB so it was suggested to create a cube from this database and then create pivot.The database is populated with TOTAL values for all its class variables .

When I create cube from Excel wizard it again summarises and craetes ALL level and the pivot is completely wrong.Infact I get values in the multiples of 2**classvariables.Is there a way where can I create cube by VBA and eliminate ALL LEVEL for every dimension. I have tried so many codes but nothing is working.Any sample code will be a great help.

Here I am getting Multiple Selections for page field as well .Is there any way to disable them?

The second option could be to populate the Listviews with the dimension values and then create a report in Excel using MDX but its not working as well.

I am relly looking looking forward to you great guys.

Regards

Hi,

create the cube with the Excel-Wizar. Save the to C:\

Then there is a SclicerDDL.txt with the CREATE GLOBAL Cube-Statement.

Remove the All Level

Example:

DIMENSION [CUBENAME].[DIM-NAME]
(
LEVEL [(All)], <-Delete this line
LEVEL [L1],
LEVEL [L2],
LEVEL [L3],
LEVEL [L4]
)

>

DIMENSION [CUBENAME].[DIM-NAME]
(
LEVEL [L1],
LEVEL [L2],
LEVEL [L3],
LEVEL [L4]
),

Run this edited SlicerDDL.txt in the Analysis Server MDX-Samples and the local cube will not contain the All-Level.

hth

Bernhard Saemmer

|||I only have Excel in my machine.Is it possible to do it by VBA?

ODBC--update on a linked table table_name failed

Hi all,

Hope someone can help me with this one. Thanks in advance.

We have several Access 97 .mdb front-end applications running against an SQL Server 7 database on NT 4.0. I know, welcome to the way back machine. Any way, all was well and running great. Then the user attempted to run the application, it had been successfully run several times earlier in the day, and received the following error: "ODBC--update on linked table 'table_name' failed (Error 3157)". Everything I know suggests that this would be an MDAC version issue except for the abrupt change. From everything I can tell nothing was altered on the hosting server and we are running MDAC 2.5. I have performed the following tests using several different client systems, 98 and 2K, to no avail.

1. Cycled the power on the client and server systems.

2. Opened the linked table in data view mode and attempted to update a single column within a single row.

3. Created new DSN and re-linked tables.

4. Reloaded MDAC 2.5 on both client and server systems.

This only affects Access 97 clients though. If I create an Access 2K database and link to the same server/database/table the updates work just fine.

Any suggestions would be greatly appreciated.

Thanks,
RickHave you tried to update it to Mdac 2.7. This might resolve the issue.|||Originally posted by oj_n
Have you tried to update it to Mdac 2.7. This might resolve the issue.

I'm in the process of testing with the latest MDAC and ODBC drivers now. What I'm realy curious about though is the fact that this was running fine and then just quit. I have suspicions that something was altered on the server but I can't discover what it was and can't get anyone to admit to it. {;^>}

I know when I talk about Access 97, SQL 7 and MDAC 2.5 that it's all history. It's just the nature of the company I'm doing the work for. I'm just a contractor here. We have just migrated their, near 1 GB, Access 97 application/database to SQL server 7, 7 due to licensing issues, and Access 2000. However some front-end apps are still in Access 97. So they are pretty far behind the technology curve.

Thanks for the suggestion; I'll let you know how it turns out.

Thanks,
Rick|||Just in case anyone has the same problem described in my original post:

I discovered the cause and, as I suspected, it was from someone making a change to the SQL Server configuration. The User Option option had been modified to turn the NOCOUNT attribute on. Once I found this I was able to duplicate or fix the problem on a test SQL Server instance. I assume it has to do with Access 97 requireing a row count be returned from SQL Server through ODBC. Don't know exactly why.... yet.

Thanks,
Rick|||you can have better peformance, better flexibility, easier development

and a solution that will work for the next 10 years

if you just throw out Access 97 and convert everything to Access 2002 ADP Format.

Its a thin client to SQL Server.

and it ROCKS!!!