I have SQL query, and I am trying to write MDX with similar logic. Logic has where clause:
Where (((CD_TYP_CAT_PARENT = 'LOAD' OR CD_TYP_CAT_PARENT = 'SPEND') AND (CD_TYP_CAT NOT IN('REVERSAL','REFUND','FEE')))
OR (CD_TYP_CAT_PARENT = 'OTHER' AND CD_TYP_CAT = 'SPEND'))
how do I write this in MDX?
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.
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,
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 ?
It is possible to create Analysis Services project (*.dwproj) in Visual Studio 2012 Data Tools?
View 5 Replies View RelatedI 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.
I would like to know the best practice for running analysis service in terms of port usage. Is it better to run on a specific port or have dynamic ports ? We have clustered servers that run default on 2383 but not sure with non clustered what's the best way to get performance.
View 2 Replies View RelatedI want to find out if it is possible to create a role in a cube without an AD account, e.g. using a GMail email address.
View 3 Replies View RelatedHi,
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.
I'm looking to see if I have a issue with excessive IO activity caused by less-than-ideal access patterns.
So I've run this query to look at logical and physical reads for logical writes:
selectsql_handle, plan_handle,
total_physical_reads, last_physical_reads,
min_physical_reads, max_physical_reads,
total_logical_writes, last_logical_writes,
min_logical_writes, max_logical_writes,
total_logical_reads, last_logical_reads,
min_logical_reads, max_logical_reads
fromsys.dm_exec_query_stats
The question is, when can it be considered excessive?
Or can you just order the results by descending order and see if you can make improvements on the worst ones?
The same goes for this query for pageiolatch wait counters and times for indexes:
select database_id, object_id, index_id, partition_number, page_io_latch_wait_count, page_io_latch_wait_in_ms
from sys.dm_db_index_operational_stats (null,null,null,null)
I just ran a testbed of 4 types of SQL queries:1. inline SQL with a StringBuilder2. managed sql3. SQL text processing (@SQL as varchar(5000); SET @SQL = 'SELECT ' + @var...)4. a regular sproc that has the columns and table name hard coded1,2, and 4 always end up at about the same time given the averages.3 is always at last 1.5 times slower, and usually closed to 2 times.1 and 2 both use StringBuilders, the code is a direct copy, and 3 is a copy as well.My managed SQL is: [Microsoft.SqlServer.Server.SqlProcedure] public static void usp_Items_Select_Managed(SqlString table, SqlString name, SqlString value) { // sql StringBuilder stringBuilder = new StringBuilder(); stringBuilder.AppendFormat( "SELECT {0}.* FROM {0} WHERE {0}.{1} = {2}", table, col, value ); SqlConnection sqlConnection = new SqlConnection("context connection=true"); SqlCommand sqlCommand = new SqlCommand(stringBuilder.ToString(), sqlConnection); sqlConnection.Open(); SqlContext.Pipe.Send(sqlCommand.ExecuteReader()); sqlConnection.Close(); }Is there anything wrong with my Managed SQL, or is this just the way that it is?Thanks
View 1 Replies View RelatedHello ,
I have a SQL 2000 with Analysis server installed on it . Its in a different domain . When i am trying to register the server remotely i.e from another domain ,from another server it gives me an error that :
'Cannot connect to the repository .
Analysis Server : <Server_name>
Error : Cannot open database requested in Login '<Database_name > Login fails . Do you wish to register this server ? '
Both the domains are trusted . Except that the OS where the Analysis server is running is Windows 2000 . I am trying to connect through SQL 2000 Server with Windows NT 4.0 Server .
We have another Analysis server which is running but on Windows NT 4.0 OS where i can easily register that server .
I tried providing access with all the rights to concerned domains , but in vain .
I am exhausted trying to figure out the problem .
Any assistance in this regard would be of great help to me .
Thanks
Ravi .
Hello,
I am tuning some queries and am using the Display Execution Plan option from Query Analyzer. What I am looking to for is somewhere which will explain the differences between the costs displayed when you put the mouse pointer over the object in the Execution plan output. BOL gives some general directions, but I need more specifics.
Any help on this would be appreciated
Thanks,
Bill
Hi all,
Is there any method by which I can use Stored procedures in Analysis services. I have some procedures which uses Temprorary tables. I wanna use those procedures's columns. Is it possible?
Thanks in advance!
Rahul
Hi,
Im pretty much new to AS and would like to ask some silly questions why I cant browse the data in my cube.
Version - SQL2K Developers Edition on W2K OS
Problem:
Im doing the tutorial which comes with Analysis Services. I have successfully followed their instructions and created and processed the 'Sales' cube.
However when i browse the cube, i get the error Unable to browse the cube 'Sales'. Unspecified error
Would anyone know why?
thanks in advance.
will anyone help me as iam new to AS and i created the cube using the sample tutorial.but when i try to browse the cube it is showing an error as "unable to browse the cube,unspecified error'.pls help me thanx in adv
View 3 Replies View Relatedhello everybody
I am new to bi project.my client want user activities to be captured into chart and cube.Into this context i just started reading analysis services.i read some document and implemented one simple analysis service project using adventureworksdw database.
My question is this like i have to work on analysis services using my own db.so directly can i use clint db(ie user table) or i need to write some script to change this db into a db which can produce cube and dimention.
Thanks in advance
I installed MS Analysis Services in my Windows XP (with SP1) desktop. When I try to expand the "+" sign beside the server, I got the following error message: "Unable to connect to the registry on the server, or you are not a member of the OLAP Administrators group on this server."
It is very weird that occasionally I can connect to the server without the above problem. But most of time, I cannot connect to the server and will get the above the error message.
Anyone knows how to fix this problem?
Thanks a lot!
A lot of job postings these days for SQL DBA's list the requirement of Analysis Services. Not having any experience with it I'm wondering what exactly is meant? Of course its going to vary from company to company, but I really have no clue what they mean in general? Is it cube design? MDX query writing? Simply backing up? To be clear, Im asking in regards specificically for a DBA, not a Developer.
TIA, ChrisR
I have been looking at Microsoft Business Intelligence tools. SQL Server 2005. I see great potential with Analysis server, and creating data cubes for reporting. This will offload much processing from my transactional servers.
I just haven't been able to identify the process for automatically updating my cubes over night. The documentation leaves me baffled.
Any assistance is greatly appreciated.
Hi,
I am running XP in the office and already have SQL's Enterprise Manager and Analysis services installed - which I can use to access the SQL databases on our office server.
Question:
I have installed the desktop version of SQL on my machine and am trying to register the server in the Analysis Service Manager but no luck.
Kindly help.
THanks
Hi
I was doing steps on page 15 / 16 of attached sheet
I was doing the microsoft example about cube to setup DSN SOURCE CONNECTION to ACCESS database (food mart) when i do a test it works
but after i do design storage and go to Process the cube
it gives me error
'test connection failed because of an error in initializing provide
(microsoft) ODBC DRIVER manager data source name not found and no default driver specified
one more error found
Microsoft ODBC DRIVER MANAGER driver sql connect attr failed: IMOO6
Thanks
Hi.
I have a problem with the caculation of the dimensions in the Analysis
Services and I need help.
In a certain dimension I make some calculations with the "personalized member formulas". Some cells are modified after this calculations. After this, in another dimension, other calculations are made using again the "personalized member formulas". With this operations some cells that were modified in the previous dimension should be modified again, but they aren't. Is there a way to fix the sequence of the calculation of the dimensions? Do they get calculated at the same time or some get done before others? Once one of this cells are modified using those formulas is it possible to modify them again? In the Hyperion's Essbase software ther is an option to calculate some dimensions before others. Does Analysis Services has that option or something similar?.
I hope you can help me. Thank you very much.
Hi,
I've got a stored procedure and a view that do the same thing and i would like to figure out which one does what faster. I use SQL Server... what are some ways for me to figure out which is better in terms of performance?
I've been having SQL 2000 startup issues for a while now and upon checking the logs, noticed a bunch of messages about analysis of the database at % completion and recovery complete. Then I see messages about it shutting down and starting back up again. Is this what is causing the problem? What is going on?
View 2 Replies View RelatedHi, all experts,
I am looking for some high level resource for comparing SQL server 2005 Analysis Services to SAS.
Some thing of a white paper or independent study nature will be grateful.
I hope some experts here have the expereices working with both and can speak of their expereices.
Thanks!
Does anyone know if the analysis services CD is seperate from the SQL 2k5 enterprise CD's? We are trying to install it on a server and when we go to add / remove programs to check if analysis services is installed it is not listed. To add it it tries to go to the setup process but asks for the analysis services cd..which I cannot find anywhere! Is this a seperate CD install ?
Thanks,
Jon
Weblog -- http://weblogs.sqlteam.com/jhermiz
Suppose I have a dimensional data warehouse in a relational SQL Server2K5 database. What's the primary benefit of moving to SSAS? What does it buy me? My understanding is that when creating a SSAS cube, analysis services will slice and dice the data on all dimensions that fact is tied to, and calculate all the possible aggregates and intersections. Is that true? If so, is SSAS really only useful to support heavy ad-hoc reporting needs?
Thanks,
-a
MOM contains its own Block Analysis script. It is a script written onVB, that looks for the monitored servers, creates for each oneMomCreateObject("SQLDMO.SQLServer"), afterwards connects to eachdatabase on each server and executes SELECT GETDATE() query. If MOMdoesn't receive answer for 6 minutes, it sends alert. Several timessince I started to monitor my servers I received the next alert:The program "SQLDMO_789" has been blocked for 6 minutes on databaseBurstingDataWarehouse in the SQL instance MSSQLSERVER. The definedacceptable blocking threshold is 1 minute(s). "SQLDMO_789" is runningon SPID 134 as login NT AUTHORITYSYSTEM and is blocked by SPID 133.The resource id is KEY: 10:2:1 (a2007950f190)SQLDMO_789 - is the MOM itself (number varies from time to time). 10 -is BurstingDataWarehouse database. As far as I can judge, MOM connectsto the server succesfully (otherwise, how can it know SPID?) The serveritself worked fine at that time - nothing unusual, all the jobsfinished succesfully including the heavy ones. What can block SELECTGETDATE() query for 6 minutes???
View 1 Replies View Relatedhello!i'm looking for resources on database analysis. my main questions are:how does one determine the primary key of a table, and, how does onedetermine the relationships between the tables?if you know of any tips, books or websites, i'd love to know. mycurrent skills for such quests are mainly looking at field names anddata and trying to figure out visio. if there are better approaches,i'd love to learn.thanks,-j
View 1 Replies View RelatedFolks,I need help with this task. I have a set of data that needs to be plotted ontimeline chart.Example:Unit ProcStart ProcEnd MachineU1 5/5/03 6:01 5/5/03 6:04 M1U2 5/5/03 6:03 5/5/03 6:05 M1U3 5/5/03 6:03 5/5/03 6:04 M2:etc. There are about 40K units and 30 serving machines. The data is inMSAccess / SQLServer. My overall task is to plot concurrent processing ateach machine at each 1-minute interval.Example for M1:count3|2| * *1| * * *|______________________________6:01 6:02 6:03 6:04 6:05 6:06My thought is that if I can break down each row of data into smallertimechunk, then I can do grouping and counts based on the new data -- whichis something like:Unit ProcTime MachineU1 6:01 M1U1 6:02 M1U1 6:03 M1U1 6:04 M1U2 6:03 M1U2 6:04 M1U2 6:05 M1U3 6:03 M2U3 6:04 M2Then I was thinking to do query with: Machine, ProcTime, Count(Unit). Then Ican export the data into Excel and plot it.My questions:1) How to break the original data into smaller chunk timeframe (i.e. query,stored procedure, etc.)?2) Is this a sound approach? Any suggestions for better idea to achieve thetask?Cheers,-Jon
View 2 Replies View RelatedI was at a Microsoft sales presentation last week for the new versionof SQL Server, Yukon. They had an extensive presentation on SQLServer and Reporting Services but didn't mention Analysis Services.When I asked about the future of the product the MS Sales rep couldn'tgive me any info. When I looked at the big product chart I got at thepresntation, Analysis Services wasn't on it. There was just boxrefering to Reporting Services and XML. Does anyone know what or ifthere is a future for Analysis Services?
View 1 Replies View RelatedI'm trying to return the EOY (12/31/14) value for measure Average Balance when I pass in a Date current member. This current member may also be at a Month, Quarter or Year level of the Post Date dimension. I've tried:
[Measures].[AverageBalance], ([Post Date].[Calendar].CurrentMember).Lag(1))
([Measures].[AverageBalance], ClosingPeriod([Post Date].[Calendar].[Month], Ancestor([Post Date].[Calendar].CurrentMember, 1).Lag(1)))
IIF([Post Date].[Calendar].CurrentMember.Level.Name = "Date", ([Measures].[AverageBalance], Ancestor([Post Date].[Calendar].CurrentMember, 3).Lag(1))
And a lot of other commands but nothing is returning the correct value.