New To Excel Add-in For Analysis Services
Jun 27, 2005
Ive 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. Im 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?
Thanks in advance.
View 1 Replies
ADVERTISEMENT
Sep 19, 2003
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?
Paulo
View 7 Replies
View Related
Jul 20, 2005
I have a 40 MB database in excel format.I need to use it in Analysis Services, I imported the data by DTS (DataTransformation 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 Itried.Thank youI hope my message is quite clearHope to find somebody that can answer me in Italian, but English is goodas well.
View 3 Replies
View Related
Oct 5, 2005
Wonder if you could help me ?
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 ??
Thanks very much
Mark
View 3 Replies
View Related
Sep 6, 2005
Hi, im using Excel 2002/2003 Add-in for SQL Server Analysis Services and i have the following problem.
Ive 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 isnt any data..
Some one have any idea?
Tks!!!
Pablo
View 3 Replies
View Related
Jan 18, 2007
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?
View 14 Replies
View Related
Nov 24, 2015
I am using
SSAS: version 2008
Excel: version 2013
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.
View 2 Replies
View Related
Oct 17, 2007
Hi,
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?
If not, how can I solve this problem?
Thanks in advance.
View 3 Replies
View Related
Mar 5, 2015
I have, a SSAS 2012 tabular instance with SP2, there is a database on the instance with a read role with everyone assigned permissions. When configuring the Power BI analysis services connector, at the point where you enter Friendly Name, Description and Friendly error message, when you click next I receive the error "The remote server returned an error (403)." I've tested connecting to the database from Excel on a desktop and connect fine.I don't use a "onmicrosoft" account so don't have that problem to deal with.
We use Power BI Pro with our Office 365. As far as I can tell that part is working ok as I pass that stage of the configuration with a message saying connected to Power BI.The connector is installed on the same server as tabular services, its a Win2012 Standard server. The tabular instance is running a domain account that is the admin account for the instance (this is a dev environment) that account is what I've used in the connector configuration. It's also a local admin account. There is no gateway installed on the server.
View 10 Replies
View Related
May 13, 2014
I have a cube that we are processing nightly via an Analysis Service Processing Task in SSIS. In order to increase the performance of the processing time, we elected to use a lot of rigid dimension attributes, and do a full process of everything in the SSIS task. The issue that I am having is that after that task completes, I need to go into Visual Studio to deploy the cube becuase we are unable to browse or use the cube. This issue seemed to start once we changed the SSIS Analysis Service Processing Task to do a full process on the dimensions, rather than an incremental.
I would expect that once development is done, and it is processed and deployed, that is it. My thinking is that the SSIS task should just update the already deployed cube,
View 2 Replies
View Related
Jun 12, 2015
How to right choose key column in"Mining Structure" for Microsoft Analysis Services?
I have table:
"Incoming goods"
Create table Income (
ID int not null identity(1, 1)
[Date] datetime not null,
GoodID int not null,
PriceDeliver decimal(18, 2) not null,
PriceSalse decimal(18, 2) not null,
CONSTRAINT PK_ Income PRIMARY KEY CLUSTERED (ID),
CONSTRAINT FK_IncomeGood foreign key (GoodID) references dbo.Goods ( ID )
)
I'm trying to build a relationship(regression) between “Price Sale” from Good and “Price Deliver”.But I do not know what column better choose as “key column”: ID or GoodID ?
View 2 Replies
View Related
Feb 18, 2013
It is possible to create Analysis Services project (*.dwproj) in Visual Studio 2012 Data Tools?
View 5 Replies
View Related
Oct 11, 2007
Hello, I have a problem when trying to fully process an SSAS database using Integration Services "Analysis Services Processing Task" task. I have 2 of these tasks which are responsible for processing the Dimensions then the Cubes. When I run the package either via the BIDS environment or on the local server from the Integration Services engine, I will get an error after about 20 minutes stating:
"Error: Memory Error: Allocation failure. Not enough storage is available to process this command""Error: Errors in the metadata manager. An error occurred when loading the <cube name> cube from the file \?D:Program FilesMicrosoft SQL ServerMSSQL.2OLAPDataMyWarehouse<cube file>.xml"
The cube name is not specific, it will fail and any of my cubes could be in the error log
If I fully process the AS database using the AS engine (logon to local AS server, right-click AS database and click Process), I get no errors at all, it processes and completes fine. The processing options are identical when I run in AS or via the SSIS "Analysis Services Processing Task" task.
I've searched quite a lot online but no joy, the information I have gleaned from various sites does not directly link SSIS with SSAS processing problems.
When either the AS processing starts via SSAS or SSIS the memory usage of MSMDSRV.exe increases to around 1.4 / 1.5 GB but never goes to 2GB ever, even when the error appears.
I've done the following with no effect.
" Have run via AS and works fine
" No specific cube it fails on
" Have created a Dimension only package, same problem
" Changed the maxmemorylimit
" Changed the connections to localhost
" Memory DOES NOT max out on server
Server Specs:
Windows Server 2003 Standard + Service Pack 2
4GM ram, 2GB paging file
SQL Server 2005 + Service Pack 2
Can anyone help?
Andy
View 2 Replies
View Related
Dec 3, 2006
I think I've seen a similar post on a blog or on the forums - but it seems like this should be possible -
I have an MDX query - that works fine in SQL Enterprise Manager, and has my dimension members on columns, and my measures on the rows. When I try the same query in Reporting Services, I get the error:
"The query cannot be prepared: The query must have at least one axis. The first axis of the query should not have multiple hierarchies, nor should it reference any dimension other than the Measures dimension..
Parameter name: mdx (MDXQueryGenerator)"
Although it works when you pivot the view, I really need my data presented with the members on the columns and the measures on the rows. Another forum post mentioned using the SQL 9.0 driver, but I can't see this listed anywhere (the only one I see is the .NET framework Data Provider for Microsoft Analysis Services).
Here's what my query looks like -
SELECT
{ [Time].[Month].&[2006-09-01T00:00:00] ,
[Time].[Month].&[2006-10-01T00:00:00],
[Time].[Month].&[2006-11-01T00:00:00],
[Time].[Month].&[2006-12-01T00:00:00]
} on COLUMNS,
{
[Measures].[Unique Users],
[Measures].[UU Pct 1],
[Measures].[UU Pct 2],
} ON ROWS
FROM [Cube]
Any ideas?
Thanks,
Arjun
View 8 Replies
View Related
Sep 16, 2007
Hi All,
I'm trying to create reports in RS2005 using AS2000 as my data source. I understand that if I use RS2005 on AS2000, I wont be able to enjoy the OLAP based parameters as in using AS2005. Does anyone know an easy way to easily use Parameters in RS2005 while still using AS2000?
Regards,
Joseph
View 1 Replies
View Related
Apr 24, 2015
I am trying to configure the reporting for TFS using SQL Server. But I get following error when viewing any report:
So I try to manually process the cube to check if it works. I am following this article: [URL] ....
When I click on GetProcessingStatus and invoke it (with last field set as TRUE) I get following error:
How to resolve this issue and be able to see the reports.
View 5 Replies
View Related
Jul 30, 2007
Hello,
i have a small problem with the data sources in the reporting services, maybe you can clarify the situation... I have a MS CRM 3 solution with the reporting services 2005 installed. From an other vendor we have a arcplan solution on the 2000 analysis services. I would like to get access from the 2005 reporting services to the 2000 cube. Is this possible? Ive read that the SSMS cant connect to the 2000 cubes. Can i connect from the reporting services??
thank you
regards
Andreas
View 3 Replies
View Related
May 15, 2007
Hi all,
Till now all the reports for our application was created in Reporting Services 2005 with SQL Server Database(OLTP) stored procedures.
Most of the reports take 10 to 15 report Parameters and all are multi valued.
Now because of the slow response time,we have decided to use OLAP cubes data source. We have created the OLAP cube in Microsoft Analysis Services.
Can someone tell me how to use the report parameters to query the Cube.
Any links,books for reference would be of great help..
Thanks,
Looking forward to your replies
View 3 Replies
View Related
Jul 25, 2015
Trying to upload excel in server where excel is not installed. BIDs was there in the server, when i am trying to craete Excel source I am not able.what the workround for this.. How to upload excel without excel installed on the server.
View 4 Replies
View Related
Sep 13, 2015
We have 10 sheets in Excel File and 10 sheet contains errror data. How to load 9 sheets data in to 1 destination and error data in to other destination?
View 4 Replies
View Related
Sep 14, 2015
I have ssis package where I have excel connection manager with expression pointing to a variable which has path for location and name of excel spreadsheet to be create each with date on the name.ExcelFilePath points to variable for shared location where excel file will be saved.I have File system task for copying template excel file to destination location with date in file name.I drag and drop excel destination. Pointed to excel connection manager. Under data access mode, I have select table and view. When I try to select name of excel sheet, it says, no tables or views could be loaded. I should be able to see sheetname there so that I can map column. I only have option to create new spreadsheet. I want to use template to load data in excel file. I dont want to create new sheet. It was working before. But I opened the ssis package and its broken. I was able to see spreadsheet name before but I dont see it now even though I have not made any change to package. XCEL 12.0 XML;HDR=NO";
View 5 Replies
View Related
Sep 21, 2015
I have created SSAS package successfully.
When I try to connect from excel , to SSAS Getting error message like
A Connection attempt failed because the connected party did not properly respond after a period of time or established connection failed because connected host has failed to respond.
If I connect to SSIS , I'm able to connect correctly.
Why I'm getting this error and how to overcome this?
View 4 Replies
View Related
May 28, 2015
Lets say I have a fact table with one measure (Datatype = UnsignedBigInt). Now when have big numbers in the facttable Excel is rounding the numbers. AS u can see in the bottom result which is taken in SSMS, the results are correctly stored in the cube. The top result is in Excel.
View 3 Replies
View Related
Jul 6, 2015
While importing data from Excel source , some column is getting null value even though excel column has value.To Resolve the issue we tried with
HKEY_LOCAL_MACHINESOFTWAREWow6432NodeMicrosoftOffice14.0Access Connectivity EngineEnginesExcel
1.Change the Value of the Row TypeGuessRows from 8 (Default value) to 0 and ImportMixedType = text
• xls
HKEY_LOCAL_MACHINESOFTWAREMicrosoftJet4.0EnginesExcel
1.Change the Value of the Row TypeGuessRows from 8 (Default value) to 0 and ImportMixedType = text
the connection string of the excel
UPPER(REVERSE(SUBSTRING( REVERSE(@[User::VarInputExcelFile]), 1, 5) ) ) == ".XLSX" ? "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + @[User::VarInputExcelFile] + ";Extended Properties="Excel 12.0;HDR=Yes;IMEX=1";":"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" + @[User::VarInputExcelFile] + ";Extended Properties="EXCEL 8.0;HDR=Yes;IMEX=1";"
by doing the above setting also , the column is coming as null from excel source even though there is data in excel.
View 2 Replies
View Related
Jan 27, 2004
Hi
What is the diff between the above 2?
Thanks.
View 1 Replies
View Related
Jan 24, 2007
Setup: Reporting Services and Analysis Services are on the same server. Active Directory User. Default configuration of Reporting Services.
Problem: Reporting Server does not apply Analysis Services Role after login.
Details:
I have created a report that retreives data from the Analysis Service. I created a role and added my user company/myUserName to the role.
On the Reporting Server I set the data source to :
* Credentials supplied by the user running the report
and
Use as Windows credentials when connecting to the data source
Internally when I connect devsql2005/Reports/ReportName I get the user and password fields. When I fill them in I get the report but I can see all the data while my role was restricted to see only few results.
Can anyone suggest what may be wrong? Any articles or tutorials on how to setup and deploy solutions where Report Server is talking to Analysis Services and NOT Sql Server.
Thank You,
Pavel Hlobil
View 3 Replies
View Related
Jun 2, 2015
My Excel Pivot Table connects to and SSAS 2008 cube. What exactly happens in the back-end during 'Waiting for the query to be executed...'
View 3 Replies
View Related
May 30, 2008
Hi,
I have Installed Analysis Services on Windows 2000 Server.
There is no Active Directory and no Domain configured.
All client PCs and the server are part of Workgroup.
I have also installed MS SQL Server on this Server and
an instance of MS SQL Express edition on this server.
There is no Firewall, no Office and no IIS installed on this server.
When I use Analysis Manager on the server, I am able to connect to the Sample Database
and browse/edit the cubes. Similarly I am also able to use the Sample MDX application.
However, when I try to connect to the same database from my Laptop (Vista home premium/Office 2007)
- either using Analysis Manager or From EXCEL, I am not able to connec to it.
The Excel gives me following error:
"The following system error occurred: No Connection could be made because the target machine actively refused it"
And Analysis Manager does not register the server. It gives me a different error:
"Errors occurred while connecting to INTRANETNT. Cannot open connection to Analysis server 'INTRANETNT'.
Security error [The user could not be authenticated]'' Do you still want to register this server?"
I am able to connect the SQL Server database from EXCEL from my laptop
I checked following:
I am able to do a telnet on port 1433 on my server from my PC - and it connects.
Please advise what should I do to sort out the problem. This is my first exposure to Analysis Server
Regards
Madhup
View 5 Replies
View Related
Apr 29, 2015
I am built the cube and processed. The fact table has 2 keys from the same dimension table.
My customers asked me that they want to browse the cube with report filters. The scenario is very strange.
They want to filter on same dimension with different conditions.
For example, they want to use the condition as ProductCode IN (...) and ProductName NOT IN (...)
How can I do this when browsing the cube in excel or any where?
View 2 Replies
View Related
Jul 16, 2015
I am working with SSAS Tabular. I have a stand alone table with 60 columns and contains 120K records. Table size is 250MB. And trying to build a tabular report out of it and it is taking longer and throwing exception, screenshot attached.
It might be cross-join issues, as workaround created a dummy measure and using in report. But it working for 10-20 k records and beyond throwing same exception. I have 8 GB RAM and 100 GB free disk space.
Customer wanted it in SSAS Tabular...
View 3 Replies
View Related
Aug 21, 2015
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.
View 2 Replies
View Related
Jul 24, 2015
Im trying to pull a workbook which has a power pivot into SSAS using a remote tabular instance through visual studio 2013. However Im getting an error saying, " We cannot import the workbook XXXXX.xlsx. Try placing the work book on a server that the service account of DBNAME/Tabular has permission to read and that can be reached with a UNC path (//<server>/<shared>/<file>)". Here DBNAME is the server name and Tabular is the instance name.
I tried changing the logon name in SQL server analysis services(Tabular) in Services.msc Microsoft console.Also tried changing the logon to local system. Still the error persists.
how to overcome this error?
View 5 Replies
View Related
Nov 10, 2015
I have a Tabular data model and I'm returning a measure that counts employees (each row is an employee) and then a calculated column in the model that gets SeniorityInMonths.
So if an employee was hired exactly 1 year ago, they would have 12 in this column.
I want to group these into bins, but the Group option is grayed out.
View 6 Replies
View Related
