I have a problem. I would like to make Market basket analysis on data.
Here is the example of data structure:
Customers
Customer_ID
Gender
Age
City
CustomerProducts
Customer_ID
Product_ID
I've created mining association model where Customers is a case table and CustomerProducts is nested table.
(I guess that association algorithm is the best for this kind of analysis).
My questions are:
1. How to write DMX query for next question:
Give me the list of customers that are using Products R1, R2, ... Rn and have potential to buy Product Ri
(List of customers with probabilities to buy Ri)
2. DMX query for next:
Same as 1. but customers filtered by some criteria, for example Gender = 'M' and Age between 25 and 36
3. DMX query for next
For chosen product Ri give me the list of groups of products with probabilities that customers that are using products from that group will buy product Ri.
(here is the example: for product Ri list will have to look like this:
R2, R5, R6 0.85
R5, R7 0.76
R1, R4, R5, R8 0.75
...
)
I don't know does this report have sense since customers that own products from one group have different other attribute values (Gender: M, F; Age...) but I have request like that...
Thanks in advance
Can someone please help me with this? I am trying to do a Market Basket Analysis with this kind of data, which can be downloaded here-- filename: foodmart.mdb (https://acs.senecac.on.ca/ftp/ms/). Using the sales_fact_1998 table, I'd like to:
1) Run a "regular" SQL query 2) Run a SQL theory using the "A-Priori" method.
If anyone of you can help me, I would greatly appreciate it. Thanks.
I'm looking for suggestions on the right design approach in relation to a problem that resembles Basket analysis. The data to be analyzed is a dimension Attribute_DIM and contains an ID, Attribute and Attribute_Value. Some examples of the data are :
ID Attribute Attribute_Value
1 Color Black
1 Movie Men in Black
1 Book Of Human Bondage
2 Color White
2 Movie Men in Black
2 Book Grapes of Wrath
We need to be able to analyze multiple selections of the dimension. For example,
Men In Black
Grapes Of Wrath Of Human Bondage
Men In Black Black 1 1
White 1 0
I have had some success using the Association Algorithm Mining Model. I think It is an overkill since I only need descriptive and no predictive analysis.
I'm looking for some ideas on the right approach to this problem. Ideally, we need to present the data in a cube and have the possibility to perform member analysis of the dimension.
I have looked at several articles (including http://msdn2.microsoft.com/en-us/library/aa902637(sql.80).aspx and http://www.aspnetpro.net/newsletterarticle/2004/10/asp200410ri_l/asp200410ri_l.asp). I'm not convinced those are the solutions and would appreciate any insight into this problem.
I have to build a Product basket mining mode such that when a customer selects a product the model should be able to recommend him/her some more products.
I have a customer, product transaction table.
I have come across many typical examples where the output is as follows
Customer Recommended Products
Cust1 a, b, c.
Cust2 e, b, f.
But I want the output of the model to be as follows
Product RecommendedProducts
A B, C, D.
B E, D, A.
C D, A, F
How should my mining model structure and the prediction query look like?
70-229 certification exam measures your ability to design and implement database solutions by using Microsoft SQL Server 2000 Enterprise Edition. Candidates for this exam work in a medium to enterprise computing environment that uses SQL Server 2000 Enterprise Edition. Candidates have at least one year of experience implementing relational databases. I don't have experience on database. Haven't experience on SQL server administration. Suggest me good one resources in low price. What are best option available in market?
Hi, As a hobby, I am creating custom SQL Server Integration Services components, which are installed as an add-on to out of the box components. They are mainly used to reduce the development time and minimize the maintance overhead. I would like to know, how I should market them? What are the mediums to use? Since this is the first time, I am creating a software for mass markets, I would really appreciate your help while marketing this baby.
Just FYI, the first commercially available package that I develop is for creating text based files. My package connects to any OLE DB datasource. Executes the query and stores the output in a text file. It is very useful while developing database driven custom feeds. Instead of working with multiple dataflow tasks, data flow sources, you just need to configure one single component and the rest is taken care for you. Since, the package implements the standard Microsoft Task Editor, it is very easy to learn.
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 ?
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.
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?
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 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'))
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
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 .
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.
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?
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
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
hello 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.
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.
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.
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.
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.
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
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.
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?
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.
