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 RepliesI 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 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 RelatedI'm trying to make a SSAS default instance available over the internet. I've opened the 2383 port on the server, and I can access it through SSMS using the local ip. I've also attempted to open and map the port on my router, and if I go to [URL] .... it says it's open. If I turn off the server, it says the port is closed, so I think it's routing correctly to the server.
However, when I plug my internet IP address into SSMS, it says
"A connection cannot be made. Ensure that the server is running. No connection could be made because the target machine actively refused it IPADDRESS:2383"
It says the same thing if I give it a random, closed port.
Hello,
In our project, we would like to use the same data source for our analysis service database cubes and for our reporting service reports.
I created the analysis service project first, deployed successfully. When trying to setting up the data source in the report model project, I selected the "create a data source based on another object", and then selected the "create a data source based on an analysis service project". However, there is no analysis service project to select, and no browse button to see where the reporting service is looking for analysis service project either.
I have tried creating a new analysis service project with data source views, cubes, dimensions and all the stuff, but still cannot see the analysis service project in the drop down box to be selected for my reporting model project data source.
As I am fairly new to the reporting service, I'm sure I'm missing something, but couldn't find much information in the help or on the web. Any suggestion would be much appreciated.
Thank you very much,
Annie
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 RelatedHi 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,
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
How to update a cube using SSIS?
View 1 Replies View RelatedHi,
I want to install the sql server2000 analysis service.Can you please tell me the website from which I can install that.
I give up, how do you start the Analysis Manager? SSAS is installed and running, but I just can't seem to find the button/shortcut/whatever to start the manager.
View 6 Replies View RelatedHi,
I was facing problems applying SP3 for Analysis Services so I removed it and then re-installed.
I then tried to apply SP3 but I am still getting v8.00.382 for AS (which represents SP1). I have tried a couple of things but it seems that SP3 is just not getting applied (for Analysis Services).
Anyone any clues? Thanks.
Hi,
Shortly after I run the setup file of AS SP3, I get a Dialog box with the following error message:
"An error occurred during the move data process: -145"
Can someone please guide what this is and how this can be solved.
Many many thanks in advance.
how can I process and update cubes automaticly every night ?
Thanks
Hi Gurus,
I have installed MS analysis service(OLAP) on my lap top
my user is in the Administrative group , other than that I tried creating a user with 'OLAP ADMINISTRATOR' privilate and tried to start the ms analysis service
I am getting the error , unable to start the service on server(), you are not a member of OLAP Administrators group
I tried to install the service pack 3 from Microsoft
Could you pleae guide me to start this serivice
Thanks,
i have AAAA project create by BI, now i want to change my computer, so i get XMLA from AAAA project in BI and run it in analysis service XMLA query but it return follow error:
Executing the query ...
Either the user, PCHOMEmax, does not have access to the Analysis Services Project1 database, or the database does not exist.
Execution complete
i try another way: i click right mouse on my database from analysis service, so i choice "Script database as "--> CREATE to... and i get a new XMLA file so igo to another computer, and run it, SQL return follow error:
Executing the query ...
Either the user, PCHOMEmax, does not have access to the AAAA database, or the database does not exist.
Execution complete
can i solve this problem.
thanks
Hi
I am new to SQL Server and not quite understand the difference between OLAP server or SQL Analysis Service. Are they referring to the same thing?? An user asked me to confirm that the OLAP server is active on a server running SQL Server 2005, what do I need to check. The Analysis service is up and running, does it mean it is OK??
Please advise.
Thanks.
Hi,
I created a SQL Server 2005 Analysis Service project. When I tried to deploy a standard Decision Tree model, it gave me errors (see below). Clearly, I can't use ntext data type with DISTINCT, but how can I change the SQL command since it was automatically created? What was the final impact of removing the DISTINCT word from the SQL command?
SQL queries 1
SELECT DISTINCT [dbo_Training_x0020_Data].[Hellos] AS [dbo_Training_x0020_DataHellos0_0]
FROM [dbo].[Training Data] AS [dbo_Training_x0020_Data]
Error Messages 1
OLE DB error: OLE DB or ODBC error: The ntext data type cannot be selected as DISTINCT because it is not comparable.; 42000.
Please assist!
Mary
I have installed SQL Server 2005 Standard Edition for our Team Foundation Server install. I have verified the installation and all seems well, except that the Analysis Services service does not remain started. I get a message like "Some services stop if there is nothing to do." The Analysis service needs to be running in order for the TFS install to succeed. How can I get the Analysis Services service to remain started?
Thanks in Advance
My first question is can I run Analysis Services 2005 on a different server to where the databases are running
If yes, any pointers to some articles.
I am new to Analysis Services. Do I have to schedule backups for Analysis Services databases just like I do for SQL Server databases? or do I just need to backup the C:Program FilesMicrosoft analysis ServicesBinmsmdrep.mdb?? Please help. Thanks.
View 1 Replies View RelatedI am new to Analysis Services, do I have schedule Analysis Service database backup job just like I do for SQL Server databases? Or I just need to backup the C:Program FilesMicrosoft Analysis ServciesBinmsmdrep.mdb file. Thanks in advance.
View 6 Replies View RelatedHi,
One of my users is supposed to have full access to all other dimesions except for one and thus I have defined a customized role on one of 4 dimensions that I have.
When I test the role (from the front-end), I am getting a strange message in my front-end and could it be that I also have to define a role for this user on all other dimensions? Should I define the role on the server or the client??? As you can guess, I am a bit confused and will appreciate your help.
Thanks.
Hi,
I have a cube with the following
DimStartDate
DimEndDate
DimArea
FactDataCount
I need to output the following report
Area 5days 10days 30days
Miami 4 2 1
Memphis 3 3 2
1)Where do I do the dateDiff calculation, at report or cube level?
2)How do I work out which dates belong to the above groups? I'm assuming i have to check if the dateDiff lies between those numbers?
thanks
I have a big table with several types of transsactions: PO (Puchase Orders, SO,( Sales orders), INV (Invoices) ....
I want to create cubes with only one type of transactions (1 cube for PO,...)
Where and how can I filter the rows I want to use in my cube ?
Thanks
Hi,
One of my users is supposed to have full access to all other dimesions except for one and thus I have defined a customized role on one of 4 dimensions that I have.
When I test the role (from the front-end), I am getting a strange message in my front-end :confused: and could it be that I also have to define a role for this user on all other dimensions? Should I define the role on the server or the client :confused: I am a bit confused and will appreciate your help.
Thanks.
I want the change the name of the Data source (see attached gif picture)
Thanks
I have to install cubes on another machine (not on the same site/NT domain).
I have archived the OLAP db and generated a .cab file.
I have retored on the destination server.
It works fine in Analysis Manager console (I can process and lokk at the data).
But when I want to access the cube from Excel using OLAP cube, I have an error msg.
Is there something I need to do on the distination server ?
Langage, SP, role, version of SQLServer/Analysis services ...
Thanks
Hi
For strange reasons, I am now unable to backup (archive) any of the databases in Analysis Service. :eek:
When I try to archive, I get a dialog box with the following error:
"[MicroSoft][ODBC Driver Manager] Data source name not found and no default driver specified"
I am able to process my cubes and dimensions and am totally lost as to why I'm getting this problem :S
Will appreciate your help.
Thanks.