Then in any Normal Scenario [according to Microsoft], this will solve it unless you have the [Large Dimension Problem] then it will not affect anything.
the Large Dimension Problem starts when you have at least one dimension with 170,000 records and more. In my case I have 220,000 records So I guess there are no solution.
I read a lot about migrating from SSAS 2005 to SSAS 2008, and also from 32bit to 64bit. but the reviews still as it. it should enhance the memory usage, but the same processing.
another thing that might be interesting, when I browse from Excel 2007 @ the server things are mmm not that bad, but doing the same from a different computer, then the disaster happens when I get to the large dimension.
what Microsoft expect to have small dimensions for making them work fine !!!!!
Image via WikipediaFaced many problems when loading or exporting to excel files! got many confused when loading data from excel file and it seems that the data you have is less than the data in the file. Needed to do many calculations and formatting!
the problem is we don't know how to deal with excel, and how excel deals with data connectors like ODBC.
I faced these issues and searched a lot to get a result and got these guidelines.
1- for loading an excel file make sure you format the excel file to the desired format you want. example: if you only will get the data in number format, so format the column with the suitable numeric format. so that all the data that you will have is the numbers, if you types a word in this column, excel will not transfer this value to the ODBC driver.
If you want it generic so you will format it as text. so whatever gets into the cell excel will transfer it.
here is a snippet of how loading from excel. [the code is in C#]
public DataTable LoadSheet(string sheetName) { DataTable dtSheet = new DataTable();
new System.Data.OleDb.OleDbDataAdapter(command, excelConString).Fill(dtSheet); return dtSheet; }
this how to load a sheet of excel into DataTable which you can deal with in the rest of your code.
*Trick: when reading, excel considers the first formatted row as the table header [formatted means bordered] if you did not do that the headers will be F1, F2...etc
2- Writing to Excel. the problem begins.
first you have to export to a named sheet
the sheet should be in a table format, if you typed in this sheet before exporting the writing process will begin writing after your writing. example: you write in cell A2, when exporting the excel will begin writing from A3, B3,C3...etc.
any formats should be applied on the previous cell that you want to write in. example: you to begin writing in cell A4, and you want it to be formated as numeric (#.00). So, you have to apply this format on cell A3 only and not the column.
for (int i = 0; i < count; i++) { fieldValue = (item is DataRow) ? fields[i].GetDataRowValue(item) : fields[i].GetValue(item); if (i == 0) { builder.AppendFormat("insert into [{0}$] values(?", sheetName); } else builder.Append(", ?");
parameter = new OleDbParameter(string.Format("param{0}", i), GetOleDBType(fields[i].FieldType)); parameter.Value = fieldValue;
command.Parameters.Add(parameter);
if (i == count - 1) builder.Append(")"); }
command.CommandText = builder.ToString(); return command; } and tada it did insert the rows.
*Tricks: make sure you cast the fields values to its proper type.
Now the nightmare part. the calculations and advanced formatting. this is an excel behavior. if you need to do this you have to create a separate sheet for viewing and other sheets to get the data and make a reference from the view sheet to the other sheets.
the calculations will be on the view sheet. your export function will export to those sheets. And you have to open the file to apply the formatting otherwise it will not affected!!!!
