Analysis :: Excel Waiting For Query To Be Executed
Jun 2, 2015My 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
ADVERTISEMENT
Excel Destination Appends The Excel File Everytime A Package Is Executed
Dec 18, 2006i have an SSIS package that exports to an excel file. This works fine. the problem is that it appends the data instead of overwriting the file. Is there any way to overwrite the file like you can with a flat file? I have to email the file everyweek and don't want to have to clear it out manually. Any help would be appreciated
View 2 Replies View RelatedDifferentiate Between Whether Stored Procedure A Is Executed Inside Query Analyzer Or Executed Inside System Application Itself.
May 26, 2008Just wonder whether is there any indicator or system parameters that can indicate whether stored procedure A is executed inside query analyzer or executed inside application itself so that if execution is done inside query analyzer then i can block it from being executed/retrieve sensitive data from it?
What i'm want to do is to block someone executing stored procedure using query analyzer and retrieve its sensitive results.
Stored procedure A has been granted execution for public user but inside application, it will prompt access denied message if particular user has no rights to use system although knew public user name and password. Because there is second layer of user validation inside system application.
However inside query analyzer, there is no way control execution of stored procedure A it as user knew the public user name and password.
Looking forward for replies from expert here. Thanks in advance.
Note: Hope my explaination here clearly describe my current problems.
Analysis :: Excel 2013 Pivot-table / Grouping On Date That Comes From Analysis Service 2008?
Nov 24, 2015I 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.
How To Get The Last Query Executed?
Feb 9, 2004Hello, i have a trigger and i want to know the query that raised it, or want to retrieve the last executed query by the server. I think it's a hard question but i know that someone can help me... Thanks
View 3 Replies View RelatedSame DTS Fails Executed As Job ,but Run Fine When Executed From DTS Designer
Mar 13, 2002I created DTS a while ago and placed in job to run once a day (it worked fine for 3 months)
2 days ago I changed sa password and now job fails with error (Login failed for user 'sa'.), but it run fine from DTS !!!
1. My DTS created with domain Account DomainSVCSQL2000( sa rights and local admin)
2. SVCSQL service use DomainSVCSQL2000 to run
3. SVCSQL agent use DomainSVCSQL2000 to run
4. DTS use 'osql -E
Where should look for reference to sa ?
Executed as user: MONTREALsvcsql2000. DTSRun: Loading... Error: -2147217843 (80040E4D); Provider Error: 18456 (4818) Error string: Login failed for user 'sa'. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0. Process Exit Code 1. The step failed.
Log The Query Executed In Stored Procedure
Jun 19, 2008hi,
i want log the query executed in stored procedure ..
any way of doing it in sql server 2000
regards.
SQL Server/ADO/XP Problem Query Cannot Be Executed
Jul 20, 2005This is a request for help fixing a SQL Server 2000/ADO problem on WindowsXP. I would appreciate any useful assistance.PROBLEM: SQL Server access on my machine fails as follows:1. All of my VB6 apps reference the following ADO typelib:Microsoft ActiveX Data Objects 2.7 LibraryLocated at:c:Program FilesCommon FilesSystemADOmsado27.tlbWhen I open one of these projects in VB6, the reference to msado27.tlb isshown as MISSING. If I remove the reference then open the references dialogagain, msado27.tlb is listed normally. When I check it and try to close thereferences dialog, I take an "Error in loading DLL".I can remove the reference to msado27.tlb and add a reference to msado15.dll(Microsoft ActiveX Data Objects 2.8 Library).When I run the app, I take a trappable error on the first ADO line my appexecutes:Dim lo_Connection As ADODB.ConnectionSet lo_Connection = New ADODB.ConnectionThe error is:Automation errorThe specified procedure could not be found.2. In Enterprise Manager, when I do "Open Table/Return All Rows" on anytable, I take the following error:"The query cannot be executed because some files are either missing or notregistered. Run setup again to make sure the required files areregistered."CURRENT CONFIGURATIONI develop software in VB6 that accesses SQL Server via a dsn-lessconnections and disconnected recordsets. I am running SQL Server 2000Standard locally, version installed from the MSDN install CD and updated byservice pack download.SQL Server version: version 8.00.760 (SP3)VB version: VB6 SP5Component Checker 2.0 shows no problems, and reports:The following product releases were matched: MDAC 2.8 RTM.My OS is Windows XP Home. As far as I can tell, it is completely updatedwith all hotfixes, including the following:KB810218KB820291KB821253KB822603KB823182KB824105KB824141KB826939KB828035KB829558Q322011Q327979Q814995Q815485ATTEMPTS TO FIXA. I have uninstalled and reinstalled SQL Server and client tools *numerous*times with no change.B. Queries in Query Analyzer execute just fine.C. The last four lines out of sqlstp.log (located in c:Windows) are asfollows:12:40:04 StatsGenerate returned: 212:40:04 StatsGenerate (0x0,0x1,0xf000000,0x100,1033,303,0x0,0x1,0,0,012:40:04 StatsGenerate -1,George Copeland)12:40:04 Installation SucceededD. The only services running during setup are those specified by MicrosoftKB article 192710. There are no relevant errors in the event log.E. I tried reinstalling MDAC 2.7 SP1 Refresh and MDAC 2.8. No change.F. Microsoft KB Article 315868(http://support.microsoft.com/defaul...Ben-us%3B315868) saysthe following:If the reinstallation of the SQL Server 2000 Client Tools does not correctthe problem, then re-register the following DLLs by using Regsvr32.exe, andthen restart the computer:C:Program filesCommon FilesSystemADOMsado15.dllC:Program filesCommon FilesSystemOle DbOledb32.dllC:Program filesCommon FilesSystemOle DbSqloledb.dllC:WINNTSystem32Atl.dllAttempting to register the first three dlls fails with"LoadLibrary("C:Program filesCommon FilesSystemADOMsado15.dll")failed - The specified procedure could not be found.". In any case, thisdoes nothing to fix the problem. And since these link libraries are notregisterable, this KB article is just a little, shall I say, unhelpful.Please note that the MS newsgroups are FULL of requests for help on thisEXACT problem. All attempts to help have failed--NOBODY has figured out thefix. Most people trying to help are treating it as an installation issue,because that is what the error message says. However, I suspect insteadthat a Windows XP hotfix has CLOBBERED ADO.BTW, this is a BIG issue for me. My development machine is hosed, and untilI get this fixed, I AM COMPLETELY DOWN.
View 5 Replies View RelatedSQL Query Are Being Executed By SQL Server Engine?
Aug 13, 2007How SQL Query/ Stored procedure are being executed by SQL Server Engine ?
View 1 Replies View RelatedPerformance Difference Between Query Executed Through ASP.NET And SSMS
Sep 18, 2007I have also posted this in microsoft.public.sqlserver.programming.
I have a query which, depending on where I run it from, will either take 10 milliseconds or 10 seconds.
The query works perfectly when run in SQL Server Management Studio... in my database of around 70,000 items it returns the results in around 10ms. It uses all my indexes and indexed views correctly.
However when I run the identical query from my ASP.NET application, it takes around 10 seconds... 1000 times longer.
Looking at it in Sql Server Profiler I can't see any difference in the query, except from ASP.NET it needs 62531 reads and from SSMS it needs only 318 reads. If I copy the slow running ASP.NET query from the profiler into SSMS, then it runs quick again. The results returned are the same.
I have provided more details of the query below, but I guess my real question is: What is the best way to debug this? I'm not an expert with SQL Server, so any pointers on where I should start looking to find the difference in how the query is being executed would be a great help.
The query is of the form:
WITH RowPost AS
(
SELECT
ROW_NUMBER() OVER(ORDER BY DateCreated DESC) AS Row,
ItemId,
Title,
....
FROM
Items_View WITH(NOEXPAND)
WHERE ItemX >= @minX AND ItemX <= @maxX AND ItemY >= @minY AND ItemY <= @maxY
)
SELECT
*,
(SELECT Count(*) FROM RowPost) AS [Count]
FROM RowPost
WHERE Row >= @minRow AND Row < @maxRow
Where Items_View is an indexed view, and WITH(NOEXPAND) is being used to force it to use the indexed view (this is optimal). The line beginning "SELECT Count(*)" is to get the total number of results (without having to run the inner query a second time).
This is running against SQL Server Developer Edition.
Is It Possible Extract File Name From Script Executed In Query Analyser ?
Apr 6, 2006Hi ,
is it possible extract file name from script executed in query
analyser ?
Example steps
1. open QA
2. open c:mySuperScript.sql
code in c:mySuperScript.sql
use master
select @filename as [file executed]
select count(*) as xfrom syslogins
3. run
-- result
file executed
------------
c:mySuperScript.sql
x
--
112
Why we need it.
Each database update created using sql compare tool from red-gate
Script saved in Vss
When script executed in production using QA we save output as proof of deployment
currently we add to each script
select
'script= put you file name'+ char(13)+
'server = ' + cast(@@servername as char(20))+ char(13)+
'dbname= ' + cast(DB_NAME()as char(20)) + char(13) +
'execution date= '+ cast(getdate()as char(24))+ char(13)+
'executed by = '+ system_user
We want wrap everything in procedure that will
extract executed file name and other values
this procedure should be first line of code in each script
Thanks
Alex
Script Your Data In A T-SQL Format (which Can Be Executed In Query Analyzer)
Jul 20, 2005Hi all,i'm working with SQL Server for about 4 years, and i have eversearching for a tool which can script my DATA in a T-SQL format tocopy them very easy from one server to another.For example scripts like that:IF NOT EXISTS (...)INSERT INTO (...)ELSEUPDATE (...)I have never found a tool like that, so i write a it by myself.This software is a free version in the moment, you can download it onmy website:http://www.sqlscripter.com..NET Framework on your client PC is required.Have fun with it ...Thomas
View 2 Replies View RelatedScheduling A Query To Be Executed At Given Time In SQL Server 2005
Apr 4, 2007Hi friend!
I want to schedule a query or procedure running at given time regularly eg: at 12AM daily. Please tell me how to do that in sql server 2005.
We use SQL server 2005 developer edition.
Thanks in advance.
New To Excel Add-in For Analysis Services
Jun 27, 2005I´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?
Thanks in advance.
Analysis :: Excel Not Connecting To SSAS
Sep 21, 2015I 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?
Analysis :: Excel Is Rounding BigInts
May 28, 2015Lets 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 RelatedAcees To Analysis Services Metadata From Excel
Sep 19, 2003I 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
Help Loading Data From Excel To Analysis Services
Jul 20, 2005I 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 RelatedUnable To Connect To Analysis Server From EXCEL
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
Drillthrough An Excel Pivot Table Into Analysis Services ?
Oct 5, 2005Wonder 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
Analysis :: Browsing Cube In Excel With Report Filters
Apr 29, 2015I 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?
Analysis :: SSAS Tabular Stand Alone Table In Excel
Jul 16, 2015I 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...
Analysis :: Excel 2010 Not Refreshing Data From Cube?
Aug 21, 2015So 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.
Analysis :: Cannot Import Excel Work Book To SSAS
Jul 24, 2015Im 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?
Analysis :: SSAS Tabular - Grouping In Excel Grayed Out?
Nov 10, 2015I 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.
Analysis :: Creating Cubes In SSAS Using Various Tables In Excel
Jun 29, 2015I'm exploring creating local cube files, .cub from an excel sheet with tables.Would SSAS be able to create one cube from taking data from a 1 way table (A, B, C), a 2 way table (AxB, BxC, AxC) and a 3 way table (AxBxC)?
View 4 Replies View RelatedAnalysis :: How To Show All Level Of SSAS Dimensions In Excel
Oct 28, 2010The all-level of dimensions doesn't show up in the PivotTable Field List? I have reports where I want to show one member of a dimensions compared to the total of the dimension (and not the total of the members shown). But I can't select the ALL-level. Is there any way to do this?
View 5 Replies View RelatedProblem With Excel 2002/2003 Add-in For SQL Server Analysis Services
Sep 6, 2005Hi, 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..
Some one have any idea?
Tks!!!
Pablo
Analysis :: Timeline Is Not Working With Multidimensional Cube In Excel 2013
Jul 11, 2014I have created one Multidimensional CUBE, while browsing(Analyze in Excel) it in Excel, I am unable to create TimeLine Slicer.It is giving me following error as: "We can't create Timeline for this report because it doesn't have field formatted as Date".
I have Dim_Date having Date as a column of Day level granularity. In PowerPivot, we do mark Dim_Date Table as a 'Date Table'.In the same way, do we have to set anything here so that Excel will come to the Date format for TimeLine Slicer?
How To Connect To Analysis Services With Excel 2007 Data Mining Add In?
Jan 18, 2007I 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 RelatedErrors In Table Analysis Tools For Excel DataMining AddIn
Mar 2, 2007It's an error "The 'MINIMUM_DEPENDENCY_PROBABILITY' data mining parameter is not valid..." always I try to run Analyze Key Influencers Tool. How it's possible to fix it?
View 5 Replies View RelatedAnalysis :: Excel Freezing When Interacting With Microsoft Dynamics AX Cubes
May 7, 2015I am running into a problem where Excel 2010 is freezing when I try to work with data coming from a Microsoft Dynamics AX 2012 R3 pre-built cube.
What is happening is that when I go to add a second dimension to my pivot table, excel locks up and becomes completely unresponsive for at least 2 hours. If I add the dimension to the table before the measure though, everything is fine.
For Example, I am trying to build a sales report with the product name and customer name as dimensions, and Sales as the measure. If I add the product name to my pivot table as a column and then sales as a measure, Excel will freeze when I try to add the customer dimension. However, if I add both dimensions to the pivot table first and then the measure, Excel seems to work fine.
Analysis :: Opening Multidimensional Model In Excel PowerView Report
Oct 11, 2015How to correctly open Multidimensional Model in SSAS to Excel Power View Report? I am continuously getting this error message when open with Power View Report.Â
"Sorry, something went wrong while loading the model for the item or data source 'PC01-4300095 Sales Cube'. Verify that the connection information is correct and that you have permissions to access the data source."