MDX : WHERE Greater than year 2004

SQL Server Analysis Services

Index ‹ SQL Server

- Previous
  - 1
    - Incremental process based on Action Code JayH wrote: I think reading the table three times is negligible compared to executing individual statements. If you wanted to only read it once, yet serialize the statements, you could use a script to execute the statements instead of splitting them to different OLE DB commands, but I wouldn't do that. I don't know if it will work, but the most efficient way is to join between your target table and the transform table for the INSERT, UPDATE, and DELETE statements and not bring the statements through SSIS at all. How about a compromise Load the file to a staging table while splitting off the inserts to an OLE DB Destination using fast load. Then back in the control flow, execute two "Execute SQL" tasks. The first to update the destination table by joining to the staging table. The second deletes from the destination, also by joining to the staging table. Tag: MDX : WHERE Greater than year 2004
  - 2
    - SQL Server and WinXP Great, I know all of the particulars, was just thinking WinXP was going to stand in the way. Thanks! Will post back if this does/doesn't work Pat Tag: MDX : WHERE Greater than year 2004
  - 3
    - Access is denied when trying to restore Anaylsis Services 2005 Databases I'm getting a "access is denied" error when I try to create a new analysis services 2005 database by restoring one from a backup created from a different server. I can restore a backup created from the same machine w/o any problems. The login I'm using is a domain admin account. Both machine is running SQL 2005 SP2. Any help would be appreciated. Thanks, Henry Tag: MDX : WHERE Greater than year 2004
  - 4
    - Non-additive measure - Actual and Average SKU is the lowest level of data...the individual product being manufactured and sold. The LeadTime does not need to be calculated it is already determined for each product. Here is a table that I hope helps: LeadTime Product Actual Average 1001 3 4 1002 4 4 1003 5 4 Sub-total Sum 12 Sub-total Avg 4 When I set the measure LeadTime to Sum the calculation that uses LeadTime is correct at the product level, but wrong at the aggregate level. If I set the measure to Average then the calc is wrong at the product and right at the aggregate level. I need to show the cube data with LeadTime as actual at the product level and average at the aggregate level. Dave Tag: MDX : WHERE Greater than year 2004
  - 5
    - Masking of data its based on the role i had to mask the details shown in a column. i mean had to display "restricted". i was able to solve it. i created a view and in the query i used case statements to display restricted where needed Tag: MDX : WHERE Greater than year 2004
  - 6
    - Creating user to own schemas? Thank you Raul, I agree this does make sense. I just tried to create a user (ampfm) and it failed due to their not being a login named ampfm. However, when I looked at logins, there does not seem to be one for sys or INFORMATION_SCHEMA. How were those users created without also requiring login accounts Tag: MDX : WHERE Greater than year 2004
  - 7
    - Execute a stored procedure of another server As Konstantin indicated, the syntax is: ServerName.DatabaseName.SchemaName.ProcedureName SchemaName is 'usually' [dbo] in SQL 2000. Whoever executes the remote procedure will have to have LOGIN permission to the remote server, be given permissions to access the remote database, and EXECUTE permission to the stored procedure. Tag: MDX : WHERE Greater than year 2004
  - 8
    - Creating VirtualDeviceSet for Remote SQL Server 2005 failed. Hi, all! I am writing the VDI application to allow backup/restore MS SQL Server 2005. I would want to backup/restore remote servers as well as local. I have local instance of MS SQL 2005 and remote. Local instance has MSSQL2005_ZORG instance name, remote uses default (so it supposed to be MSSQLSERVER). While connection to those server via SQL Server Management studio i see local server as "ZORG\MSSQL2005_ZORG" and remote as "VM2000SRVZ2". When i try to create VIrtualDeviceSet via CreateEx i pass "MSSQL2005_ZORG" as lpInstanceName parameter and all works fine. But I could't create same device set for remote 'MSSQLSERVER' instance. I passed any combination for that, such as "VM2000SRVZ2\MSSQLSERVER", "MSSQLSERVER", "VM2000SRVZ2", " \\VM2000SRVZ2\MSSQLSERVER " and so on. No luck always get VD_E_INSTANCE_NAME (0x80770007). Any idea --Thanks Tag: MDX : WHERE Greater than year 2004
  - 9
    - Text Extract in SQL 2000 I am using SQL 2000, is there a way around Varchar(Max) Tag: MDX : WHERE Greater than year 2004
  - 10
    - (RDA)Accessing server via a port.. ErikEJ wrote: Can you connect via Internet Explorer to the above URL on port 81 from your client 8desktop or device) If not, maybe a firewall or similar is prevent communication. I can browse sqlcesa30.dll via browser on the device successfully. but when I access via my program,it fails..altough the connection address is same as the address that I wrote to the browser on the device, it doesn't connect...so that means there's not firewall or something that prevents communication. am i wrong Tag: MDX : WHERE Greater than year 2004
  - 11
    - Can we drill through to details for calculated members? Hi, Hannes, Thanks a lot for your help. Sorry for the mistyping, yes, I was asking for 'drill through' capability for calculated members. Thanks again. With kindest regards, Yours sincerely,. Tag: MDX : WHERE Greater than year 2004
  - 12
    - Design Tool and Security roles Yes, I do reconnect to my cube but I always get the error message, I can't test security, even though the roles I defined do allow the users to access the cube I still get this message. Thank you, Tag: MDX : WHERE Greater than year 2004
  - 13
    - Lock in FTS Hello, There are some king of lock that stop my search process over a FTS table indOfertaCliente. The Query: SELECT TOP 1 * FROM indOfertacliente Works fine...but SELECT * FROM CONTAINSTABLE ( dbo . indOfertaCliente , (*), 'polar' , LANGUAGE 3082 , 1 ) returns timeout. Also i receive timeout when i try to disable de FTS index over the table. Which resource can be locked Where can i see it Sql 2005. SP1 Tahnks in advance Tag: MDX : WHERE Greater than year 2004
  - 14
    - No certificates available in the Protocols for MSSQKSERVER Properties window That blog is one of many I have been working with. Here is what I can ascertain so far- 1) It is in the local computer personal share, the current user personal share and the service (MSSQLSERVER) personal share. 2) Timestamps/system time are synched up. 3) Enhanced Key Usage contains these values- Server Authentication (1.3.6.1.5.5.7.3.1) Client Authentication (1.3.6.1.5.5.7.3.2) 4) Not sure how/where to check this. 5) Does not apply. I have also tried to manually set the certificate in the registry by entering the thumbprint of the certificate into the Certificate key. SQL Server will not start, however, when I try that. What is the correct format for doing that One other factor I forgot to mention earlier is that this is being done on a VM in our test bed. Would that have any effect on how this stuff works Thanks! Tag: MDX : WHERE Greater than year 2004
  - 15
    - Cannot add domain accounts to SQLServer Permissions I have 4 new SQL Server 2005 installations on Windows 2003 that I configured at our main office and shipped to a hosting center. All four servers are members of our domain. I set up test datbases with replication on one of the servers and facilitated this with a domain account. Now that I've moved the servers to the hosting center (which has a DC) and I'm not having any luck adding domain accounts to the permissions section on any of the the SQL Server boxes. When I try to add a domain account in the SQL Server's permissions window I get "Name Not Found". By every indication the server is connected to the domain. I can log on using my domain account; I can create shares specifying domain accounts but I can't seem to add domain accounts to the SQL server permissions. When I look in the permission's tab I still see the original domain account, I had added back in the main office, stranded by itself in the list of users. We're using mixed authentication by the way. Why doesn't SQL Server recognize the domain Where does it get it's list of users Does the account I'm logging in with just not have the permission to add domain accounts These diaglogs are slightly different from the normal 'add a user' dialog boxes. I feel like this must be a simple oversight. Any help would be appreciated. I'd prefer to move away from local accounts to keep things simple. Tag: MDX : WHERE Greater than year 2004
  - 16
    - Changing a linkedserver datasource Hi Kuntal, Could you demonstrate how I would do this in code (any language) I have tried various ways but either manage to build the linkedserver object with the properties of the old server and then get an error when i try to create it, or i can create it but then not set the properties. Many thanks, Robin. Tag: MDX : WHERE Greater than year 2004
- Next
  - 1
    - Can I ask how to use 'weight' variable in descision tree and how to use cross validation in the data mining procedure? What do you mean by 'Weight variable' Also, regarding cross validation -- it is not built in in SQL Server 2005. If you write your own cross validation mechanism (either a stored procedure or some client side code), then you should be able to use it with any algorithm. Tag: MDX : WHERE Greater than year 2004
  - 2
    - Very basic trigger question I would alter Kent's suggestion and use a DEFAULT of getdate() on INSERT (less system impact than a TRIGGER), leaving the RecordLastUpdated field NULL on insert -easier to tell original insertion. (I've actually had occasion to find all records that have never been updated.) I also usually have a field to capture the user. Then, in the UPDATE TRIGGER I would add a check for rows updated -and quit if nothing to do. As a side note, I recommend a naming convention of table_typeofTrigger_WhatIsUpdated. So doing makes it very easy to find all of the code elements that 'touch' a particular table -they just alphabetize together (the name structure gives a clue about the object). It works wonderfully for large projects and source control. Also, you may wish to decide what to do if the UPDATE were to fail. I usually have some error handling code. Here is a 'revised' UPDATE TRIGGER: Code Snippet CREATE TRIGGER dbo.tr_Customer_U_RecordLastUpdated ON dbo.Customer FOR UPDATE AS BEGIN IF @@ROWCOUNT = 0 RETURN UPDATE dbo.Customer SET RecordLastUpdated = getdate() FROM dbo.Customer c INNER JOIN inserted i ON c.CustomerID = i.CustomerID --Error Checking, what to do if the UPDATE fails END GO Tag: MDX : WHERE Greater than year 2004
  - 3
    - What is this error Post your sp code (Sample) Tag: MDX : WHERE Greater than year 2004
  - 4
    - Distinct Sum for my column hi, thanks for the reply. but function call in my footer is called first, where i display the sum,so the sum always come zero. so the code doesnt works -thanks and rgeards Hemant Tag: MDX : WHERE Greater than year 2004
  - 5
    - Add/Remove DataViewers makes VS to crash In my case it happens when a dataviewer is assiocated with a deleted item. When you delete an control flow item on the control flow the dataviewers are not deleted. When you try to delete one VS will crash Workaround: Create a new control flow item with the same name as is used for the dataviewer. Delete the dataviewers in the breakpoint window Delete the control flow item. At least it did the trick for me (after a few dozens of crashes before I discovered this workaround) Sander Tag: MDX : WHERE Greater than year 2004
  - 6
    - sp_spaceused equivalent in CE? Thanks for the info. I am also considering using something like Sum(Len(MyNvarcharField)) to get a closer approximation of the variable length data (of which there are several). I don't really have a good percentage to estimate actual data vs. column max length. Expensive, but needs to run only a couple times a day to monitor size. Tag: MDX : WHERE Greater than year 2004
  - 7
    - index question??? Dave: I will tell you my database structure. This is simple. I have only three tables. Student: Uniqueid int (PK), lname varchar(35), fname varchar(35) courses: Courseno(PK), coursetime, courseday. Table1(this is dump table ): uniqueid, scandate, courseno. This dump table is not related to student or courses table bc without a record in students table then student can attend a class. If student attends a class for 45 days in a semester for a particular course then dump table will have 45 records. say if he swipes his id thrice (thinking something wrong ) then there will be three records in the dump table. This dump table is also the final table. If i want to see the attendance of students in a particular course i query this table. do you think I need a table out of this dump table or just adding the indexes if fine for this table. as far i understand from ur reply, you suggest to create non-clustered index on uniqueid, scandate, coursecrn Tag: MDX : WHERE Greater than year 2004
  - 8
    - Solution for simple select statement. Great info. Thanks for the fast reply. That was really wounderful! Tag: MDX : WHERE Greater than year 2004
  - 9
    - Disaster Recovery Plan Our disaster recover plan centers around a warm standby server. We would like to do daily backups of the Report Server databases on the primary server and restore them on the secondary. I have read that you need to backup and restore the Encryption Key. After doing so I get this error when trying to access the report server: The feature: "Scale-out deployment" is not supported in this edition of Reporting Services. (rsOperationNotSupported) (rsRPCError) This is presumably because this configuration is interpreted as a Reporting Service Farm which is not available in SQL Standard version that we are using. I simply want a warm standby that will not be used unless we fail-over. Is there anyway to do this backing up the primary databases and restoring to the secondary. If not what is the preferred disaster recovery plan for SQL Reporting Services Tag: MDX : WHERE Greater than year 2004
  - 10
    - query to split a database column ? Hi, TO know the count for the products I want to do it in separate columns. can you please let me know how to do it. Thanks Rajesh Tag: MDX : WHERE Greater than year 2004
  - 11
    - MSDE upgrade to SQL Server 2005 Express Edition SP2 Sahil Girotra wrote: I guess we have been able to figure out the cause of the problem (atleast part of the problem) and have been able to successfully perform the upgrade of MSDE 2000 to MS SQL Server 2005 Express Edition. - I guess the feature to upgrade MSDE 2000 to SQL Server 2005 Express Edition SP2 is not available (atleast thats what I figured out with the error message) - Upgrade using Windows Authentication required two steps: - Granting logon permissions to 'BUILTIN\Administrators' group on our database using the command "sp_grantlogin 'BUILTIN\Administrators' ". This resolves the error message " Server Setup could not connect to the database service for server configuration. The error was: [Microsoft][SQL Native Client][SQL Server]Login failed for user <mydomain>\<AdminID>" - Disabling PASSFILT.DLL by renaming the file C:\WINNT\System32\PASSFILT.DLL (and rebooting the server to unload the DLL from the memory. Once the DLL is unloaded from the memory, the install runs successfully and that resolves the error "[Microsoft][SQL Native Client][SQL Server]Password validation failed. The password does not meet the requirements of the password filter DLL.. To continue, correct the problem, and then run SQL Server Setup again". After the install ran successfully, we rename the PASSFILT.DLL and reboot the servers again so the DLL gets loaded again. I hope this helps. Regards, Sahil Girotra Tag: MDX : WHERE Greater than year 2004
  - 12
    - sqlcmd -Lc can you run OSQL -L and SQLCMD -L , then compare the list... Madhu Tag: MDX : WHERE Greater than year 2004
  - 13
    - SMO - And SQL Express You should at least look at the EULA and verify you meet the laid out requirements. Also, this link will give you information for registering for a redistributable license. http://www.microsoft.com/sql/editions/express/redistregister.mspx. Hope this helps. Tag: MDX : WHERE Greater than year 2004
  - 14
    - Rebuilding a huge clustered index Defragmenting the current index won't help, since you want a new index on different column(s). If you are running SQL Server 2005 Enterprise Edition, you might try creating a new, non-clustered index with the ONLINE option and with MAXDOP set to something like 25% of your number of available CPU's (so it does not peg all the CPU's while building the index). This would be a lot less disruptive than dropping the clustered index and building a new one. This is assuming you need to stay online while you are doing this. Without knowing the table schema, its hard to estimate the storage requirements for this. Tag: MDX : WHERE Greater than year 2004
  - 15
    - Excel Connection Manager and expressions Yea, you were right. It's just a matter of setting the expression in the control flow instead of the data flow. I really appreciate it. Phil Tag: MDX : WHERE Greater than year 2004
  - 16
    - Problem sorting using custom code as the SortExpression I have a report that contains a Chart called "SummaryChart" and a table called "DetailsTable" the data for the summary chart comes from a data set called dataSourceSummary and the data for the details table comes from a data set called dataSourceDetails. The summary chart presents counts of items broken down by a category and the details table shows the details of the items that make up the counts in summary chart. I have a situation where I need to sort the data in the table in a non alpha-numeric way. So I have added a user sort item to the table column header and set the SortExpression to something like the following SortProvider.GetUserSortOrder("TableName",5,Fields!myColumn.Value) where SortProvider is a C# class and GetUserSortOrder is a static method the method essentially finds a row in a database, get the display order for that row and returns it. I have tested this in another report and it works as expected. Now finally to the problem: What happens is when I click on the table heading, values from fields in the dataset for the chart are being passed into my method, followed by values from fields in the dataset for the table and then an exception is thrown. The only difference between this report and ones that I have tested this on and it works is that this report has two dataregions (Chart and Table) and two datasets. I have tried setting the ExpressionScope to DetailsTable and the SortTarget to dataSourceDetails but that results in the sort not working at all. I would have thought that it should only be passed values from the data that it is sorting, especially considering that the data that is being passed in from the chart is a completely different field. Has anyone else experienced this Has anyone got any ideas Tag: MDX : WHERE Greater than year 2004

Richard Abrahams

I have an MDX dataset auto-created by SQL 2005, I want to restrict it to anything greater than year 2004. I could get the single year 2004 by replacing ALLMEMEBERS with &[2004] : (i.e. [Incident Date].[Year].&[2004]) but don't know how to do GREATER THAN in MDX.

WITH MEMBER [Measures].[ParameterCaption] AS '[Incident Date].[Year].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Incident Date].[Year].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Incident Date].[Year].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Incident Date].[Year].ALLMEMBERS ON ROWS FROM [Incidents]

Top

Re: MDX : WHERE Greater than year 2004

Christopher Webb

Try the following:

WITH MEMBER [Measures].[ParameterCaption] AS '[Incident Date].[Year].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Incident Date].[Year].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Incident Date].[Year].CURRENTMEMBER.LEVEL.ORDINAL'
SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS ,
{[Incident Date].[Year].&[2004]:null}
ON ROWS FROM [Incidents]

This should return every member from 2004 onwards on [Incident Date].[Year].

HTH,

Chris

Top