I have a 40 MB database in excel format.
I need to use it in Analysis Services, I imported the data by DTS (Data
Transformation Services), everything is working I can see the database,
but I can't load it in the analysis services.
I have to construct a cube but I can't see the database in any way I
tried.
Thank you
I hope my message is quite clear
Hope to find somebody that can answer me in Italian, but English is good
as well.
We have an install of SQL MDS 2016 CTP3 which I can access via the web browser and create models etc. Â I have uninstalled the Excel add-in for 2012 and installed the MDS 2016 CTP3 add-in on Excel 2010. Â I am able to create a MDS connection and the "Test" reports success in Excel.
However, when I try to connect to a model via the Master Data Explorer to load an entity, Excel hangs with the message "Loading objects for xxxx_Model". Â My only option is to close Excel via the Task Manager. Â
Where do I look for more detailed logs? Â Is there a switch I can use to provide a better debugging experience?
I downloaded a trial version of office 2007 and the data mining addin. I do not have an automatic connection to Analysis Services, so most functionality does not work. Is it possible to configure a connection with a trial version? If so, does anyone know what the server name is and how to configure it?
I have used for-each loop container for loading excel sheet contains multiple sheets with same structure. It is loading data into SQL table even there is no data in sheets.
I´ve been trying the Excel 2003 Add-in for Analysis Services because after reading the white paper from Microsoft it seemed to be a good client of Analysis Services. I´m getting trouble to make it work right, maybe some of you can help :confused: :
-Can I insert a formula as a result of applying "cubecellvalue" to a "cubemember" function?
-How do I calculate a variance, meaning the percentage of sales for example? Is it possible to divide a "cubecellvalue" into another one?
-How do I format the Excel sheet so that the format changes when drilling-up and down?
I have an xls spreadsheet with multiple worksheets. From each worksheet I need to load the data into sql server. The data I need to retrieve from each worksheet is not row by row, but cell by cell. For example, I need to load data from cells C44, G65, I23, A78, etc. in all the worksheets. Is this possible using SSIS?
I would like my clients had access to the cube metadata. The clients use Excel to access the cube. How can they see the metadata? Have I to create a dimnesion with the metadata?
I need to be able to load data from an Excel spreadsheet into SQL Server via a web application. I was able to load the spreadsheet data into the application and store it into a DataSet. How do I get the data in the dataset into a table in SQL Server?
I insert/update thousands of line items daily to my MS SQL 2k db each day from multiple excel sheets that are uploaded. In Microsoft's infinite wisdom Excel and MS SQL is not "Fully" compatible and some characters throw off the uploads, cause errors in loading, etc. Each Excel sheet has from a few rows up to 50,000 rows or more. We load around 100 of these Excel sheets each day depending on what our users upload.
Our main problem appears to be with "Special Characters", anything that is not a number or letter seems to be an issue in loads. We have written our scripts to ignore a certain set of characters such as #,!, -, ', ", [, ], {, }, +, =, *, %, ~, `, <,>, etc. But we still get errors. This has become a frustrating nightmare. Any help in the right direction would be greatly appreciated.
I have tried ASP scripts, VB created exe's, converting the Excel sheet to a text file, then uploading, and other various means to get this process error free. Some files never have issues loading, some excel files will error out and not at the same point each time. We can run the same file 5 times in a row and it will stop/error at a different point each time without any rhyme or reason.
Now we are not just doing an "Insert", there are several variables that are at work when loading the data, like combining exact items into one row, associating data with ID's in another table, etc. It is not just a simple, take this data and place it here scenerio which makes this a serious headache to figure out how to make this error free and troubleshoot.
Is there some information or a direction I should look to consider a solid solution to importing data from Excel sheets to a MS SQL 2k db? These files are loaded into a specific folder and on upload they are also recorded in a table marked ready for update in the db. Our scheduler runs the exe associated with that users ID and loads their data, overwriting their previous data load, then marks the file as done.
Is there a proven method, some external program that can be used to make this a solid process, or any direction you can provide for me to research?
I have set up a cube running under MSSQL Server 2000 Analysis services and have created reports in Excel 2002 using pivot tables and linking into the cube as an external data source.
The pivottable works fine and I can slice / dice as usual, the only thing that doesnt work is the drill through into AS. I receive and error message saying "Cannot show detail data for that selection...".
Now I know that the drill through works, as when I create a report using the Analsyis Services Excel Add Inthe drill through works fine, its just how do I get it working using the pivot tables ?
fyi - the reason I dont want to use the Analsyis Services Excel Add In for all the reports is because I have to deploy this to XX number of users, who wont have admin rights on their machines etc etc....
Is there any VB that I could use to perform this drill through and return the results.....or easier ??
Hi, i´m using Excel 2002/2003 Add-in for SQL Server Analysis Services and i have the following problem.
I´ve created a report with the addin and i saved it in a share directory. I have users in differents workstations with windows 2000 sp 4, excel 2003 and mdac 2.8 the problem is that sometimes when users open the report and want to do a drill down of a dimension it isn´t any data..
can anyone help me to solve this problem i have created a ssis package to load the data from excel file to the table, but we are getting the data in different language ie in french,english and in china after loading the data when we view the data it is showing as junk characters for chinese data but we are able to see other language data ie french and english. so please tell me how to solve that reply to my mail id(sandeep_shetty@mindtree.com)
I'm using Script Component to load data into Oracle DB due to the poor performance issue. Now, I found it will missing some data during the transmission. Please see the screenshot below:Â
I am getting ErrorCode 8 while loading the data from stage to model. I have checked my error view it states that "Member Code is Inactive".
Initially I have loaded same set of data in Model from MDS Stage table but then deleted with ImportType = 5 which removed all the data from the MDM model.
Now i want to load it back but its giving the Error Code 8 .. Before loading the same data i have changed the stage table Importtype to 2 and Importstatusid to 0.
So I use Excel 2010 connect to a cube I have built. Â Then I change some values in the cube via my ETL and re-process the cube. Â Then I verify that record is NOT there in the fact table - check!
However, when I refresh the worksheet where the pivot table is pulling data from the cube, but that old record wont go away!
Just realized my cube data source on the dev server, was in fact still configured with my local workstation name. Once I updated that, processed the cube all was well.
.If record from Detail (Spectrum table) is null then do insert the record into Spectrum table set status_flag to 'A' for active else do update the record (replace all old values with new values) set status_flag to 'A' for active end-if
· If record from Master (Staging table) is null then do soft delete set status_flag to 'D' for delete end-if
I have to load data into destination table, it has foreign key relation to two different tables called person table and organization table . sample data to be loaded is like
person_id organization_id 1                  Null 2                    NULL Null               1 null              null
person table and organization table doesn't have null values in them, when I try to load this data none of them are laoded, I know either person_id or organization id having null value is failing foreign key constraint. But I want to transfer all the rows except the ones having both nulls. how this can be achieved ?
I am connecting to SSAS cube from Excel and I have date dimension with 4 fields (I have others but I don't use it for this case). I created 4 fields in order to test all possible scenarios that I could think of:
DateKey:   - Type: System.Integer   - Value: yyyyMMdd Date:   - Type: System.DateTime DateStr0:   - Type: System.String   - Value: dd/MM/yyyy (note: I am not using US culture)   - Example: 01/11/2015  DateStr1:   - Type: System.String   - Value: %d/%M/yyyy (note: I am not using US culture)   - Example: 1/11/2015 Â
Filtering on date is working fine:
Initially, in excel, filtering on date was not working. But after changing dimensional type to time, and setting  DataType to Date, as mentioned in [URL] filter is working fine as you can see in the picture.Grouping on date is not working:
I have hierarchy in my Date dimension and I can group based on hierarchy, no problem. But user is used to pre-build grouping function of excel, and he wants to use that. Pre-build functions of Excel, Group and ungroup seems to be available as you can see in following picture:
But when user clicks 'Group', excel groups it as if it is a string, and that is the problem. User wants to group using pre-build grouping function available in Pivot table. I also find out that Power Pivot Table does not support this excel grouping functionality. And if I understood well, this pre-build grouping functionality of excel, needs to do calculation at run time, and that is not viable solution if you have millions of rows. So Power pivot table does not support pre-build grouping functionality of excel and hence we need to use dimension hierarchy to do the grouping. But I am not using Power Pivot table, I am using simple Pivot Table. So I expect grouping functionality to be working fine. Then I tried to do simple test. I created a simple data source in excel itself. And use it as source of my Pivot table. Then grouping is working fine. The only difference that I can see is (When double click the Measure value in Excel),For date values of my simple test, excel consider them as 'Date'.
For date values of my data coming from cube, excel consider them as 'General'
  2.1. But value here is same as it was in simple test.
  2.2. 'Date Filter' works just fine.
  2.3. If I just select this cell and unselect it, then excel change type to 'Date' though for that cell.Â
  2.4. I have created 4 different types of fields in my date dimension thinking that values of attribute of my dimension might be the problem, but excel consider 'General' for all of them.
  2.5 This value (that can be seen when double clicking on measure) comes from 'Name Column' of the attribute. And the DataType defined is WChar. And I thought that might be the reason of issue. And I changed it to 'Date'. But SSAS does not allow it to change to 'Date' giving error : The 'Date' data type is not allowed for the 'NameColumn' property; 'WChar' should be used.
So, I don't know, what is the puzzle piece that I am missing.
1. Date filter works, group does not work
2. Excel consider it as 'General' string.
3. SSAS does not allow to change 'NameColumn' to Date.
I have some questions about SQL Servers 2000 and 2005 compatibility. In my configuration I have to use both servers. The cubes are stocked in 2005 server. May I transfer from 2005 to 2000 Analysis Services the cubes?
If yes, what is the procedure? The result of migration is the same in the two different versions?
We already integrated different client data to MDS with MS Excel plugin, now we want to push back updated or new added record to source database. is it possible do using MDS? Do we have any background sync process to which automatically sync data to and from subscriber and MDS?
I need to use cubes in VS 2005 web project. I have no clue how to create cubes or use them in my web page. I am familiar with Reporting Services but not cubes. thanks in advance!
I want to perform a Analysis Services Processing Task using a connection manager created from a Analysis Services data source. I create the data source using the Oledb provider for Analysis Services 9.0, and it works fine. Then I create a connection manager using the "New connection from data source" option, and it seems to create it ok.
The problem is that I can't then asign this connection to the Analysis Services processing task. I get the error:
"Connection Manager MyConectionManagerFromDataSource is not a Analysis Services Connection Manager"
However, if I create directly a connection manager using "New Analysis Services Connection" it works fine.
Is it at all possible to create a Analysis services connection from a Data Source to perform a Analysis Services Processing Task?
Trying to go through the Analysis Services tuturial. Logged in as Administrator on a 64 bit W2K server. SQL Server 2000 AS and SP4.As I come to the Design Storage step I get a message sayingData source provider error: ; Time:2006-04-10 19:51:47There are no further details given and the Event Logger has nothing.Grateful for any help.Screenshot: http://i23.photobucket.com/albums/b366/biund/sql/DesignStorageproblem.pngAnd the version Screenshot:http://i23.photobucket.com/albums/b366/biund/sql/DesignStorageproblem2.png
Hi, I'm trying to learn about analysis, integration and reporting services. I have install SQL server 2005 management Studio Express. but I cant find these in the Start menu as mentioned in the tutorial Click Start > SQL Server 2005 > Business Intelligence Development Studio.(for reporting services). what do I need to do? Please help me.
