Excel OLAP Report losing layout after migration

I have big numbers of OLAP Excel (Pivot tables) report and very complex.

Finally I am able to move my olap database from AS 2000 to AS 2005 after fixing some know issues some specific to my database.

Now the big challenge is all Excel 2002 reports should work un touched. I am using Excel Macro to update connection string in all Excel file but I can not afford to redesign all of them. Redesigning Excel report will almost kill my AS 2005 migration project.

After changing pivot table connection from 2000 database to 2005 database, when I refresh pivot table I am losing my reports layout. In my "Field List" all shared dimensions and measures has exact same names after refresh.

Looks it's ok for most of the dimensions but all shared dimensions had Hierarchy name like Company.Vendor , Company.Employee in AS 2000 has this issue in AS 2005.

After migration I do had to fix all Hierarchy shared dimensions because all came in AS 2005 as Company 1, Company 2.

Any idea what I should try to do fix them.

Thank you - Ashok

Macro to change Excel PivotTable connection from AS 2000 to AS 2005

With ActiveCell.PivotTable.PivotCache
Response = MsgBox("Current Connection: " & .Connection)
.Connection = Replace(.Connection, "MSOLAP.2", "MSOLAP.3")
.Connection = Replace(.Connection, "AS2000DB_Name", "AS2005DB_Name")
Response = MsgBox("New Connection: " & .Connection)
End With

Hello. Most respect to your situation but we have had this discusssion a few days ago here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1061202&SiteID=1

Kind regards

Thomas Ivarsson

|||

Thanks Thomas.

There is one way to fix this. It worked for me however still there are some open issues but may be it's in my case only.

Let's say we have Hierarchy shared dimensions in AS 2000

Company.Vendor

Company.Employee

Company.Location

Once we run the migration in AS 2005 we see dimensions like

Company (Hierarchy name Vendor)

Company 1 (Hierarchy name Employee)

Company 2 (Hierarchy name Location)

What I did before, I renamed them "Company Vendor", "Comapny Employee" and "Company Location". That created issues of losing layout in Excel Pivot table report.

This is what I tested and worked to fix Excel Pivot report losing layout issue.

- After migration create a dimension "Company" or user first one "Company" dimension, in that dimension create three Hierarchy "Vendor", "Employee" and "Location". If you use existing "Company" dimension then add two new "Employee" and "Location"

- Remove all Company dimensions from cubes

- Add new Company dimension which has more the one Hierarchy

- Process cube

- Open Old Excel Pivot table report which runs fine with AS 2000 cube

- Run macro to change connection to AS 2005

- Refresh report

You should see "Filed list" changed now "Company Vendor" DIM will be only "Vendor" but the big deal is you don't lose reports layout.

It worked for me I still have open issue that I have 5 cubes and one Cube with 5 Linked Cubes (like Virtual Cube in 2000). I don't know why I am not able to add my new "Company" dimension to my Virtual Cube.

|||

Hello Ashok. The cube migration wizard(AS2000 to SSAS2005) will only copy the structures in AS2000 and do its best to make it fit in SSAS2005. You are already aware of this as I can see.

Another problem that you might see is that some MDX will no longer work as expected.

If you have several customer dimensions in AS2000 you will still have several customer dimensions in SSAS2005. But one design fundamental is that you can have one customer dimension in SSAS2005 with several user hierarchies in that dimension.

Attribute relations is another fundamental thing in SSAS2005 that I do not think that the migration wizard will get right.

In SSAS2005 you no longer have virtual cubes but a logical cube with one or several measure groups(fact tables)

I have done some migration projects and I have always recommended my customers to avoid the migation wizard and redesign the AS2000 cubes according to what is best in SSAS2005. I think that this have been the best approach.

This is the best chance you will have to improve you AS2000 cubes.

Regards

Thomas Ivarsson

Excel Import

Trying to import an excel file into SQL Server. For some reason, this
column is being considered Double(15) and when it imports, it's losing text
data. I'd like it to just be treated as any form of text and import
exactly. Right now all of the numeric ones go through but the hybrid ones
are blank. Is this possible?
KM
154+a
84+b
154
84
15
15
84
Are you using a script to import or just the import wizard from SQL? If you
use the import wizard, are you predefining the fields before you import but
after you select the flat file source. (first screen but you need to scroll
down to the Advanced - Data source: - format each column)
If you have run through the import and failed, the table is already created
with these predefined fields and you will need to modify them to fit the data.
The above assume you use the wizard (right click - database - choose Import)
Regards,
Jamie
"James" wrote:

> Trying to import an excel file into SQL Server. For some reason, this
> column is being considered Double(15) and when it imports, it's losing text
> data. I'd like it to just be treated as any form of text and import
> exactly. Right now all of the numeric ones go through but the hybrid ones
> are blank. Is this possible?
> KM
> --
> 154+a
> 84+b
> 154
> 84
> 15
> 15
> 84
>
>
|||These little scripts may be of relevance:
http://sqlblog.com/blogs/linchi_shea/archive/2007/03/06/a-little-scripting-saves-the-day.aspx
Linchi
"James" wrote:

> Trying to import an excel file into SQL Server. For some reason, this
> column is being considered Double(15) and when it imports, it's losing text
> data. I'd like it to just be treated as any form of text and import
> exactly. Right now all of the numeric ones go through but the hybrid ones
> are blank. Is this possible?
> KM
> --
> 154+a
> 84+b
> 154
> 84
> 15
> 15
> 84
>
>
|||Hi Jamie,
I am facing the same problem while I import excel to sql server 2000. I
couldnt find the option (first screen but you need to scroll down to the
Advanced - Data source: - format each column) what you have mentioned here.
For workaround before I import I prefix some character for instance "#" or
any other character for these kind of columns which make DTS aware of varchar
datatype and once its imported I fire update query to remove this char. so
far I havent been able to figure out any out of the box solution from SQL
server.
"thejamie" wrote:
[vbcol=seagreen]
> Are you using a script to import or just the import wizard from SQL? If you
> use the import wizard, are you predefining the fields before you import but
> after you select the flat file source. (first screen but you need to scroll
> down to the Advanced - Data source: - format each column)
> If you have run through the import and failed, the table is already created
> with these predefined fields and you will need to modify them to fit the data.
> The above assume you use the wizard (right click - database - choose Import)
> --
> Regards,
> Jamie
>
> "James" wrote:
|||Bhavesh,
Worst case scenario for me... when I can't get it to import - I save the
Excel as a CSV - check the file over visually (sometimes users have comma's
embedded in text which will throw off the import) and then change the
extension from CSV to TXT and import it as a comma-delimited Text file. It
isn't often I need to do so but it comes in handy when other methods fail.
Regards,
Jamie
"Bhavesh" wrote:
[vbcol=seagreen]
> Hi Jamie,
> I am facing the same problem while I import excel to sql server 2000. I
> couldnt find the option (first screen but you need to scroll down to the
> Advanced - Data source: - format each column) what you have mentioned here.
> For workaround before I import I prefix some character for instance "#" or
> any other character for these kind of columns which make DTS aware of varchar
> datatype and once its imported I fire update query to remove this char. so
> far I havent been able to figure out any out of the box solution from SQL
> server.
>
> "thejamie" wrote:
|||Linchi,
Nice little article.
We have someone in our company that does the imports by creating the insert
statement in the Excel sheet and into a column that precedes the columns he
imports... he gets a great deal of work done that way.
Regards,
Jamie
"Linchi Shea" wrote:
[vbcol=seagreen]
> These little scripts may be of relevance:
> http://sqlblog.com/blogs/linchi_shea/archive/2007/03/06/a-little-scripting-saves-the-day.aspx
> Linchi
> "James" wrote:

Excel Import

Trying to import an excel file into SQL Server. For some reason, this
column is being considered Double(15) and when it imports, it's losing text
data. I'd like it to just be treated as any form of text and import
exactly. Right now all of the numeric ones go through but the hybrid ones
are blank. Is this possible?
KM
--
154+a
84+b
154
84
15
15
84Are you using a script to import or just the import wizard from SQL? If you
use the import wizard, are you predefining the fields before you import but
after you select the flat file source. (first screen but you need to scroll
down to the Advanced - Data source: - format each column)
If you have run through the import and failed, the table is already created
with these predefined fields and you will need to modify them to fit the dat
a.
The above assume you use the wizard (right click - database - choose Import)
--
Regards,
Jamie
"James" wrote:

> Trying to import an excel file into SQL Server. For some reason, this
> column is being considered Double(15) and when it imports, it's losing tex
t
> data. I'd like it to just be treated as any form of text and import
> exactly. Right now all of the numeric ones go through but the hybrid ones
> are blank. Is this possible?
> KM
> --
> 154+a
> 84+b
> 154
> 84
> 15
> 15
> 84
>
>|||These little scripts may be of relevance:
http://sqlblog.com/blogs/linchi_she.../>
he-day.aspx
Linchi
"James" wrote:

> Trying to import an excel file into SQL Server. For some reason, this
> column is being considered Double(15) and when it imports, it's losing tex
t
> data. I'd like it to just be treated as any form of text and import
> exactly. Right now all of the numeric ones go through but the hybrid ones
> are blank. Is this possible?
> KM
> --
> 154+a
> 84+b
> 154
> 84
> 15
> 15
> 84
>
>

Excel Import

Trying to import an excel file into SQL Server. For some reason, this
column is being considered Double(15) and when it imports, it's losing text
data. I'd like it to just be treated as any form of text and import
exactly. Right now all of the numeric ones go through but the hybrid ones
are blank. Is this possible?
KM
--
154+a
84+b
154
84
15
15
84Are you using a script to import or just the import wizard from SQL? If you
use the import wizard, are you predefining the fields before you import but
after you select the flat file source. (first screen but you need to scroll
down to the Advanced - Data source: - format each column)
If you have run through the import and failed, the table is already created
with these predefined fields and you will need to modify them to fit the data.
The above assume you use the wizard (right click - database - choose Import)
--
Regards,
Jamie
"James" wrote:
> Trying to import an excel file into SQL Server. For some reason, this
> column is being considered Double(15) and when it imports, it's losing text
> data. I'd like it to just be treated as any form of text and import
> exactly. Right now all of the numeric ones go through but the hybrid ones
> are blank. Is this possible?
> KM
> --
> 154+a
> 84+b
> 154
> 84
> 15
> 15
> 84
>
>|||These little scripts may be of relevance:
http://sqlblog.com/blogs/linchi_shea/archive/2007/03/06/a-little-scripting-saves-the-day.aspx
Linchi
"James" wrote:
> Trying to import an excel file into SQL Server. For some reason, this
> column is being considered Double(15) and when it imports, it's losing text
> data. I'd like it to just be treated as any form of text and import
> exactly. Right now all of the numeric ones go through but the hybrid ones
> are blank. Is this possible?
> KM
> --
> 154+a
> 84+b
> 154
> 84
> 15
> 15
> 84
>
>|||Hi Jamie,
I am facing the same problem while I import excel to sql server 2000. I
couldnt find the option (first screen but you need to scroll down to the
Advanced - Data source: - format each column) what you have mentioned here.
For workaround before I import I prefix some character for instance "#" or
any other character for these kind of columns which make DTS aware of varchar
datatype and once its imported I fire update query to remove this char. so
far I havent been able to figure out any out of the box solution from SQL
server.
"thejamie" wrote:
> Are you using a script to import or just the import wizard from SQL? If you
> use the import wizard, are you predefining the fields before you import but
> after you select the flat file source. (first screen but you need to scroll
> down to the Advanced - Data source: - format each column)
> If you have run through the import and failed, the table is already created
> with these predefined fields and you will need to modify them to fit the data.
> The above assume you use the wizard (right click - database - choose Import)
> --
> Regards,
> Jamie
>
> "James" wrote:
> > Trying to import an excel file into SQL Server. For some reason, this
> > column is being considered Double(15) and when it imports, it's losing text
> > data. I'd like it to just be treated as any form of text and import
> > exactly. Right now all of the numeric ones go through but the hybrid ones
> > are blank. Is this possible?
> >
> > KM
> > --
> > 154+a
> > 84+b
> > 154
> > 84
> > 15
> > 15
> > 84
> >
> >
> >|||Bhavesh,
Worst case scenario for me... when I can't get it to import - I save the
Excel as a CSV - check the file over visually (sometimes users have comma's
embedded in text which will throw off the import) and then change the
extension from CSV to TXT and import it as a comma-delimited Text file. It
isn't often I need to do so but it comes in handy when other methods fail.
--
Regards,
Jamie
"Bhavesh" wrote:
> Hi Jamie,
> I am facing the same problem while I import excel to sql server 2000. I
> couldnt find the option (first screen but you need to scroll down to the
> Advanced - Data source: - format each column) what you have mentioned here.
> For workaround before I import I prefix some character for instance "#" or
> any other character for these kind of columns which make DTS aware of varchar
> datatype and once its imported I fire update query to remove this char. so
> far I havent been able to figure out any out of the box solution from SQL
> server.
>
> "thejamie" wrote:
> > Are you using a script to import or just the import wizard from SQL? If you
> > use the import wizard, are you predefining the fields before you import but
> > after you select the flat file source. (first screen but you need to scroll
> > down to the Advanced - Data source: - format each column)
> >
> > If you have run through the import and failed, the table is already created
> > with these predefined fields and you will need to modify them to fit the data.
> >
> > The above assume you use the wizard (right click - database - choose Import)
> > --
> > Regards,
> > Jamie
> >
> >
> > "James" wrote:
> >
> > > Trying to import an excel file into SQL Server. For some reason, this
> > > column is being considered Double(15) and when it imports, it's losing text
> > > data. I'd like it to just be treated as any form of text and import
> > > exactly. Right now all of the numeric ones go through but the hybrid ones
> > > are blank. Is this possible?
> > >
> > > KM
> > > --
> > > 154+a
> > > 84+b
> > > 154
> > > 84
> > > 15
> > > 15
> > > 84
> > >
> > >
> > >|||Linchi,
Nice little article.
We have someone in our company that does the imports by creating the insert
statement in the Excel sheet and into a column that precedes the columns he
imports... he gets a great deal of work done that way.
--
Regards,
Jamie
"Linchi Shea" wrote:
> These little scripts may be of relevance:
> http://sqlblog.com/blogs/linchi_shea/archive/2007/03/06/a-little-scripting-saves-the-day.aspx
> Linchi
> "James" wrote:
> > Trying to import an excel file into SQL Server. For some reason, this
> > column is being considered Double(15) and when it imports, it's losing text
> > data. I'd like it to just be treated as any form of text and import
> > exactly. Right now all of the numeric ones go through but the hybrid ones
> > are blank. Is this possible?
> >
> > KM
> > --
> > 154+a
> > 84+b
> > 154
> > 84
> > 15
> > 15
> > 84
> >
> >
> >